MLPPI question

Database
N/A

MLPPI question

The use of MLPPI is of interest to me and I have a question that I cannot seem to find resources on, here is what I am having an issue with. . .
We currently partition on date_loaded and I would like to know if this is a viable way to utilize MLPPI.

PARTITION BY RANGE_N(date_loaded BETWEEN DATE '1999-01-01' AND DATE '2020-12-31' EACH INTERVAL '1' YEAR )
RANGE_N(date_loaded BETWEEN DATE '1999-01-01' AND DATE '2020-12-31' EACH INTERVAL '1' MONTH )

If I could partition by year and then by month I believe this would be useful but I cannot find resources stating if you can partition multiple times on an already partitioned field. If I understand MLPPI correctly this should work or am I way off the mark here.

Any help would be appreciated.

Thanks
7 REPLIES
Enthusiast

Re: MLPPI question

Regardless of if you can do that or not, I don't think it would be terribly useful. The way MLPPI works is that you define a top level set of partitions. In your example this is the EACH '1' YEAR definition. The second level of partitions resides WITHIN each of first level of partitions. This is your EACH '1' MONTH definition.

So in your example, you'd have 22 partitions at the first level and (22*12) partitions at the second level. This is because you'd define the total number of months between 1999 and 2020 for each year between 1999 and 2020.

When it came to actually populating the partitions, assuming you had 22 years of data, you'd only have 12 of the second level partitions populated PER first level partition. This is because there are only 12 months per year. So basically, only 1/22 of your total number of partitions would be populated with data.

If you really wanted to partition by year and then month, a better way to do it would be to use your existing first level partition but then define your second level on EXTRACT(MONTH FROM date_loaded) BETWEEN 1 AND 12 EACH 1. But you'll need to validate with explain plans that this achieves the desired level of partition elimination for your queries.

Hope this helps.

Junior Contributor

Re: MLPPI question

You can't do what that, but monthly partitions are exactly what you need, why do you think your approach would perform better?

The effort to scan "year 2010" in a table partitoned by month or year or day is exactly the same.

Partitioning in Teradata is just a different sort order:
A non-partitioned table is sorted by (RowHash) whereas a PPI table is sorted by (Partiton number, RowHash).

Dieter
Teradata Employee

Re: MLPPI question

I have questions related to MLPPI as I was studying it from Physical DB design manual from SoCal Library. 

in the module 7, its mentioned that with below table and query, will utilize MLPPI.

CREATE TABLE Claim_MLPPI2(
claim_idINTEGER NOT NULL,
cust_idINTEGERNOT NULL,
claim_dateDATE NOT NULL,
cityVARCHAR(30) NOT NULL,state_codeCHAR(2) NOT NULL,
claim_infoVARCHAR (256))
PRIMARY INDEX (claim_id)
PARTITION BY(
RANGE_N (claim_dateBETWEEN DATE '2006-01-01' and DATE '2013-12-31'EACH INTERVAL '1' MONTH, NO RANGE),
RANGE_N (state_codeBETWEEN 'A','D', 'I', 'N', 'T' AND 'ZZ', NO RANGE),
RANGE_N (city BETWEEN 'A', 'C', 'E', 'G', 'I', 'K', 'M', 'O', 'Q', 'S', 'U', 'W' AND 'ZZ', NO RANGE) )
UNIQUE INDEX (claim_id);


Queries:

SELECT * FROM Claim_MLPPI2 WHERE state_code= 'OH'; 
SELECT * FROM Claim_MLPPI2 WHERE state_code= 'GA' AND city LIKE 'a%';
SELECT * FROM Claim_MLPPI2 WHERE claim_date= DATE '2013-08-24' AND city LIKE 'a%';

The way I understand MLPPI, All partitioning columns HAVE TO BE SPECIFIED in the SQL. Then only MLPPI will be utilized. Else, it'll result in FTS. 

Can any expert please help me to on this. How the SQLs will utilize MLPPI when all partitioning columns are not specified/used in the SQL. 

Thanks for all your help.

Deepak

Enthusiast

Re: MLPPI question

You do not need to specify values for all partitioning columns to make use of an MLPPI.

It will still be used if you only specify values for  one or two  of the partitioning columns .

It is the PI ( also called the NPPI) index that you must specify all columns in order for it to be used.

--Shelley--

Teradata Employee

Re: MLPPI question

Thanks Shelley. I have just checked it by writing some SQLs and now understood the concept. Thaks for your input. 

Enthusiast

Re: MLPPI question

Which tables  can I have MLPPI on?

Can I have it on Global Temporary Tables or Join Indexes

Enthusiast

Re: MLPPI question

Hi,

You could have tested:

CREATE MULTISET GLOBAL TEMPORARY TABLE DB1.raja_test1 (

claim_id INTEGER NOT NULL,

cust_id INTEGER NOT NULL,

claim_date DATE NOT NULL,

city VARCHAR(30) NOT NULL,state_code CHAR(2) NOT NULL,

claim_info VARCHAR (256))

PRIMARY INDEX (claim_id)

PARTITION BY( 

RANGE_N (claim_date BETWEEN DATE '2006-01-01'  and   DATE '2013-12-31'EACH INTERVAL '1' MONTH, NO RANGE),

RANGE_N (state_code BETWEEN 'A','D', 'I', 'N', 'T' AND   'ZZ', NO RANGE), 

RANGE_N (city BETWEEN 'A', 'C', 'E', 'G',   'I', 'K', 'M', 'O', 'Q', 'S', 'U', 'W'  AND 'ZZ', NO RANGE) )

UNIQUE INDEX (claim_id)

ON COMMIT PRESERVE ROWS

;

CREATE JOIN INDEX ABC AS SELECT COUNT(*)(FLOAT, NAMED CountStar ) ,c2 

FROM DB1.raja_test

GROUP BY c1

 PRIMARY INDEX ( c1 )

 PARTITION BY RANGE_N(c2  BETWEEN 1  AND 100  EACH 1 ,  NO RANGE) 

INDEX ( c1 ); 

Cheers,