Page Range Screening using DPSI in DB2 V11.

Ben Hesed Amose Robinson

Page Range Screening using DPSI in DB2 V11.

Hello,

We created a New PBR Table and the only index on the table has 5 columns(one among the 5 is the partitioning key column but at the 3rd level in the index). So when we have a query with all 5 matching columns it is not doing a PAGE RANGE Screening but instead it is probing all the 1400 partitions in the table.

Do we need to have a index with partitioning key as the Leading Column?

When we do a explain of the SQL it is have PAGE_RANGE as "Y" but while on the package bind it is not getting PAGE RANGE.

what are the options to have PAGE RANGE SCREENING in the above situation?

Michael Hannan

RE: Page Range Screening using DPSI in DB2 V11.
(in response to Ben Hesed Amose Robinson)

In Reply to Ben Hesed Amose Robinson:

Hello,

We created a New PBR Table and the only index on the table has 5 columns(one among the 5 is the partitioning key column but at the 3rd level in the index). So when we have a query with all 5 matching columns it is not doing a PAGE RANGE Screening but instead it is probing all the 1400 partitions in the table.

Do we need to have a index with partitioning key as the Leading Column?

When we do a explain of the SQL it is have PAGE_RANGE as "Y" but while on the package bind it is not getting PAGE RANGE.

what are the options to have PAGE RANGE SCREENING in the above situation?

It is normal for DB2 to look in all partitions of DPSI, unless predicates can limit the partitions. Only certain types of predicates are allowed, but partitions can be determined at runtime. V11 should have all features working.

It might be helpful if you showed the SQL and the Explain output for the Package REBIND, and the index definition.
Just to see you have got a DPSI, you have got 5 Match cols, etc. BTW, is the index unique, or duplicate? 

A reason why a dynamic Explain works for limited partition but static does not, could be something like mismatching datatype, but then I would not expect Matchcols=5. Still would be nice to see what the predicate looks like.

Must admit I am struggling to think of anything obvious.

You have not mentioned if you are querying a single table, doing a join, etc. so would be interesting to see if there is anything unusual about the SQL. 

Is the critical 3rd col predicate equals a hostvar, literal, join predicate, etc. and what are the data types? Is column Nullable?

Without detail, the whole thing just becomes a little bit vague, to me.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd