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))
