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