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

How to find Empty partition in a table ?

29) How to find Empty partition in a table ?

Answer > Patition are generally important when table size is too huge and if we have query being access by particular set of range value, In this scenario creating a partition will help to access data fastest , However partition help the optimizer to understand which partition to search for given range of value in such case when there are huge list of partition without any data and the particular partition is empty its allways recommended to drop the partition as it creates overhead for optimizer to generate a better plan for performance. the below query will help to find the empty partition in a table.

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,

SV.MaxValue AS MAX_PARTITION_DT,

CASE WHEN FirstRange=’0’ THEN FirstRange

WHEN SUBSTRING( FirstRange FROM 1 FOR 4) = ‘DATE’ THEN SUBSTRING ( FirstRange from 7 for 10)

END as EMPTY_PART_START_VAL,

SV.MinValue AS EMPTY_PART_END_VAL,

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 NOT null

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

Add a Comment

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