PROVE IT !!If you know it then PROVE IT !! Skill Proficiency Test

How to find the missing stats on partition column ?

28) How to find the missing stats on partition column ?

Answer > As a general practice we collect stats on Partition which provide useful demographic to Optimizer to understand howmany partition are there in a particular table, however collecting stats on partition column is too important which will help optimizer to understand , How many partition are there and how many unique values are there in each partition and this will help optimizer to generate better plan. The below SQL will help to fetch the partition column having Missing stats.

SEL

SV. DatabaseName,

SV. TableName,

SV.CollectDate,

CASE WHEN SUBSTRING( PartitioningCol FROM 1 FOR 1) = ‘”‘ THEN PartitioningCol

ELSE ‘”‘||trim(PartitioningCol)||'”‘ –SUBSTRING ( FirstRange from 7 for 10)

END as PartitioningColumn,

A.ConstraintText

from EDW1_UTIL_BASEVIEW.StatsView SV

left outer join

(Sel databasename,tablename,ConstraintType,PartitioningCol,FirstRange

,ConstraintText,CreateTimeStamp

from (

sel constrainttext, INDEX(constrainttext,’RANGE_N’) S, INDEX(constrainttext,’BETWEEN’) L, S+8 S1, L-S1 Len

–,(TRIM( SUBSTRING(constrainttext FROM ((INDEX(constrainttext,’RANGE_N’))+8) FOR ((INDEX(constrainttext,’BETWEEN’))- ((INDEX(constrainttext,’RANGE_N’))+8)))))

,INDEX(constrainttext,’AND’) A

,L+8 S2

,A-S2 Len2

,databasename,tablename,ConstraintType,CreateTimeStamp

, (TRIM( SUBSTRING(constrainttext FROM S1 FOR Len))) PartitioningCol

,(TRIM( SUBSTRING(constrainttext FROM S2 FOR Len2))) FirstRange

–,(TRIM( SUBSTRING(constrainttext FROM ((INDEX(constrainttext,’BETWEEN’))+8) FOR ((INDEX(constrainttext,’AND’))- ((INDEX(constrainttext,’BETWEEN’))+8)))))

from dbc.IndexConstraints

where databasename like ‘EDW1_%’

and constrainttext like ‘%RANGE_N%’

) A ) A

on

trim(SV.databasename)=trim(A.databasename)

and

trim(SV. TableName) = trim(A. TableName)

–and SV. ColumnName = A. PartitioningCol

where

sv.TableType=’Tbl’

and sv.PartitionLevels=1

and sv.statsType <> ‘MCol’ –‘Col’

and sv.ColumnCount=1

–and MinValue is not null

and (SV.databasename,SV.tablename) in

(sel IC.databasename,IC.tablename

from dbc.IndexConstraints IC

inner join dbc.Tables T

ON T.DatabaseName=IC.databasename

and T.TableName=IC.TableName

where IC.ConstraintType=’Q’ and T.databasename like ‘EDW1_%’ and T.TableKind=’T’

group by 1,2

)

AND MinValue is null

and trim(UPPER(ColumnName)) = trim(UPPER(PartitioningColumn))

Add a Comment

Your email address will not be published. Required fields are marked *