I suspect that the real issue for the optimizer is the fact that the index is a non-prefixed local index. I can only presume that the estimated rows you see are a "pre partition" based number, it's still wrong should be in the near 4000 not less than 100. (given the total number for rows returned is 6 million) Since it's not prefixed the optimizer has no idea which partitions it will go once it retrieves a row from the index. What at the global level stats for the per_ind column? Especially the number for distinct values. -----Original Message----- From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Mohamed Houri Sent: Tuesday, October 01, 2013 10:07 AM To: ORACLE-L Subject: Statistics Problem on partitioned table Dear list I have a performance problem that I have narrowed to a statistics problem where the CBO is not doing good estimations on a partitioned table The culprit select looks like select a ,b ,c ,per_ind from XXX_PER_YYY where per_ind = 0; XXX_PER_YYY is a table range partitioned by a date. There are 1493 partitions. AND there is an index XXX_PER_IND on (per_ind) local (note that it is local non prefixed) The execution plan with Estimations and Actuals looks like ------------------------------------------------------------------------ ---------- | Id | Operation | Name | Starts | E-Rows | A-Rows | ------------------------------------------------------------------------ ---------- | 0 | SELECT STATEMENT | | 1 | |550K | | 1 | PARTITION RANGE ALL | | 1 | 69 |550K | | 2 | TABLE ACCESS BY LOCAL INDEX ROWID| XXX_PER_YYY | 111 | 69 |550K | |* 3 | INDEX RANGE SCAN | XXX_PER_IND | 111 | 69 |550K | ------------------------------------------------------------------------ ---------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("PER_IND"=0) And the execution plan showing the number of partition looks like ------------------------------------------------------------------------ ------------------- | Id | Operation | Name | Rows | Bytes | Pstart| Pstop | ------------------------------------------------------------------------ -------------------- | 0 | SELECT STATEMENT | | 69 | 1173 | | | | 1 | PARTITION RANGE ALL | | 69 | 1173 | 1 | 1493 | | 2 | TABLE ACCESS BY LOCAL INDEX ROWID| XXX_PER_YYY | 69 | 1173 | 1 | 1493 | |* 3 | INDEX RANGE SCAN | XXX_PER_IND | 69 | | 1 | 1493 | ------------------------------------------------------------------------ -------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("PER_IND"=0) There is only one distinct value of per_ind (per_ind =0) select per_ind, count(1) from XXX_PER_YYY group by per_ind; *per_ind cout(1)* 0 6,118,184 And I have the following partitions num_rows distribution 758 partitions with num_rows = 0; 60 partitions with num_rows <= 5000 295 partitions with num_rows between 5000 and 10000 315 partitions with num_rows > 10,000 and num_rows <50,000 65 partitions with num_rows > 100,000; Statistics are calculated at a global level How to make the CBO having correct estimations and hence an optimal execution plan? Thanks in advance -- Bien Respectueusement Mohamed Houri www.hourim.wordpress.com -- //www.freelists.org/webpage/oracle-l -- //www.freelists.org/webpage/oracle-l