All Forums Database
Santanu84 122 posts Joined 04/13
05 Jun 2014
MLPPI Constraint Explanation

Hi All

 

If a table is defined as MLPPI the constraint can be checked either from DBC.PartitioningConstraintsV or from DBC.IndexConstraintsV. 

There the ConstraintText feild holds the explanation in a certain format such as

 

/* nn bb cc */ partition_expression_i /* i  d+a */

 

Where,

nn = total level of partitions

bb = bytes of partition

cc = column partition

i = partition level

d = total partitions at the level

 

I would like to know more about the option of "a", what does it signify ?

 

Thanking You

Santanu

gbansal 18 posts Joined 02/11
17 Nov 2014

Run this Query if you want this thing
--Will give All tables having more than one Partition defined on same Columns only --
SELECT
DatabaseName
,TableName
,CASE
WHEN SINGLE_CHAR = '@' THEN 'RANGE_N'
WHEN SINGLE_CHAR = '$' THEN  'CASE_N'
END AS Partition_type
,COUNT(*) AS Level_of_MLPPI
FROM
(
    SELECT DatabaseName,TableName,SUBSTR(SENT,POS,1) SINGLE_CHAR
    FROM
    (SELECT DISTINCT DatabaseName,TableName,
    OREPLACE((oreplace(ConstraintText ,'CASE_N', '$')),'RANGE_N','@') AS sent FROM DBC.IndexConstraintsV
     WHERE  ( SUBSTRING(ConstraintText FROM 1 FOR 9) = 'CHECK (/*')
     ) A,
    (
        SELECT ROW_NUMBER() OVER (ORDER BY 1) Pos  FROM sys_calendar.CALENDAR
    ) B
    WHERE Pos <= CHAR_LENGTH(SENT) AND SINGLE_CHAR IN ( '$' , '@')
) B
GROUP BY 1 ,2,3 HAVING Level_of_MLPPI > 1
ORDER BY 1,2,3,4

 

You must sign in to leave a comment.