Don't understand the optimize choice

Nguyen Duc Tuan

Don't understand the optimize choice
Dear all ,

DB2 V7

I have a strange situation where the optimizer doesn't choose limited
partition scanning (Page_Range=Yes) , and i cannot understand it's choice.

TableA, 3 partitions , is partitioned with index1(COL1,COL2)
COL1 is CHAR(1)
The Limit key is ('A' , 'B' , 'C' )

Distribution over the partitions is (Systabstats)

CARD NPAGES
----+---------+-----
0 0
5575 2205
46575 14290


The query is :

SELECT * from TableA where COL1 = ?
and COL5 = ? and COL6 = ?

The optimizer chose a tablespace scan without Page_RAnge=Y . It would be
better if it scans only the partition giving by COL1 = ?

Do i miss something ?

Thank you for your help

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Nguyen Duc Tuan

Re: Don't understand the optimize choice
(in response to Nguyen Duc Tuan)
Found the answer in Admin Guide : "Limited partitions scan is not supported
when host variables are used on the first key of the primary index..."

Regards

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Patrick Bossman

Re: Don't understand the optimize choice
(in response to Nguyen Duc Tuan)
Correct.

I would like to add that DB2 for z/OS V8 allows limited partition scan when
host variables, parameter markers, and special registers are used.

Regards,
Pat

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm