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,
claim_dateDATE NOT NULL,
cityVARCHAR(30) NOT NULL,state_codeCHAR(2) NOT NULL,
PRIMARY INDEX (claim_id)
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);
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.
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.
You could have tested: