MLPPI Constraint Explanation

Database
Enthusiast

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

1 REPLY
Enthusiast

Re: MLPPI Constraint Explanation

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