If anyone who understand the optimizer better than me wants to wade through this and give me their opinion, I would be very grateful. Fair warning...this kind of long and involved Platform: Oracle 10.2.0.4 on HP-UX (Itanium) (I've applied the 10.2.0.5 patchset thinking this might bug 7430474. Problem remained) Optimizer Mode: FIRST_ROWS Table TEST1 has three columns A - CHAR(8) - NOT NULL B - number(4) - NOT NULL C - CHAR(8) - NOT NULL The primary key "TEST1_PK" is (A,B) there is also a unique index "TEST1_I2" on (B,A) The data in column A is a char representation of a date in 'YYYYMMDD' format. There are 10862 distinct values of A. For EACH value of A, there are 317 values of B. The data in C is irrelevant to this issue. So the data looks like. A B -------- ------- 20110201 1 20110201 2 20110201 3 .... 20110201 316 20110201 317 20110202 1 20110202 2 20110202 3 ..... 20110202 316 20110202 317 etc Given the query select a, b, c from test1 where a = '20110210' and b > 10 (this query will return 307 rows) I would expect range scan on the primary key (a,b) to be the optimal plan. And indeed, if I force the execution plan to use that index (which I can do by using a ALL_ROWS hint, a RULE hint, and INDEX hint or by removing the ORDER BY clause), it uses the PK and returns in about .2 seconds. However, as executed with no hints, it returns in about 1.06 seconds, which would not be a big deal if we weren't running it somewhere between 3 to 5 times per second (with different parameters and using bind variables). In order to try to understand why the optimizer was doing this I generated an EVENT 10053 trace file. Here are some relevant portions BASE STATISTICAL INFORMATION *********************** Table Stats:: Table: TEST1 Alias: TEST1 #Rows: 3413297 #Blks: 12585 AvgRowLen: 21.00 Index Stats:: Index: TEST1_I2 Col#: 2 1 LVLS: 2 #LB: 10822 #DK: 3413297 LB/K: 1.00 DB/K: 1.00 CLUF: 3408068.00 Index: TEST1_PK Col#: 1 2 LVLS: 2 #LB: 10834 #DK: 3413297 LB/K: 1.00 DB/K: 1.00 CLUF: 23542.00 OK, this looks about like I expected. The clustering factor is much better for the primary key, as I more or less expected based on the way the table is loaded. Access Path: TableScan Cost: 1145.44 Resp: 1145.44 Degree: 0 Cost_io: 987.00 Cost_cpu: 1523229875 Resp_io: 987.00 Resp_cpu: 1523229875 kkofmx: index filter:"TEST1"."A"='20110210' AND "TEST1"."B">10 Using density: 9.2064e-05 of col #1 as selectivity of unpopular value pred Access Path: index (RangeScan) Index: TEST1_I2 resc_io: 10805.00 resc_cpu: 739184909 ix_sel: 0.97 ix_sel_with_filters: 8.9302e-05 Cost: 10881.89 Resp: 10881.89 Degree: 1 Using density: 9.2064e-05 of col #1 as selectivity of unpopular value pred Access Path: index (RangeScan) Index: TEST1_PK resc_io: 6.00 resc_cpu: 161679 ix_sel: 8.9302e-05 ix_sel_with_filters: 8.9302e-05 Cost: 6.02 Resp: 6.02 Degree: 1 ****** trying bitmap/domain indexes ****** ****** finished trying bitmap/domain indexes ****** Best:: AccessPath: IndexRange Index: TEST1_PK Cost: 6.02 Degree: 1 Resp: 6.02 Card: 304.81 Bytes: 0 This looks good. It evaluated the Table Scan with a cost of 1145, a Range Scan of the TEST1_I2 index with a cost of 10881 and a Range Scan of the primary key with a cost of 6, and decided that the best path was a range scan of the PK. Here's the part I don't understand. Immediately after the section above, it does this. *************************************** OPTIMIZER STATISTICS AND COMPUTATIONS *************************************** GENERAL PLANS *************************************** Considering cardinality-based initial join order. Permutations for Starting Table :0 *********************** Join order[1]: TEST1[TEST1]#0 *********************** Best so far: Table#: 0 cost: 6.0168 card: 304.8148 bytes: 6405 ****** Recost for ORDER BY (using index) ************ *************************************** SINGLE TABLE ACCESS PATH ----------------------------------------- BEGIN Single Table Cardinality Estimation ----------------------------------------- Using density: 9.2064e-05 of col #1 as selectivity of unpopular value pred Table: TEST1 Alias: TEST1 Card: Original: 3413297 Rounded: 305 Computed: 304.81 Non Adjusted: 304.81 ----------------------------------------- END Single Table Cardinality Estimation ----------------------------------------- Access Path: TableScan Cost: 1145.44 Resp: 1145.44 Degree: 0 Cost_io: 987.00 Cost_cpu: 1523229875 Resp_io: 987.00 Resp_cpu: 1523229875 kkofmx: index filter:"TEST1"."A"='20110210' AND "TEST1"."B">10 Using density: 9.2064e-05 of col #1 as selectivity of unpopular value pred Access Path: index (RangeScan) Index: TEST1_I2 resc_io: 10805.00 resc_cpu: 739184909 ix_sel: 0.97 ix_sel_with_filters: 8.9302e-05 Cost: 10881.89 Resp: 10881.89 Degree: 1 Best:: AccessPath: IndexRange Index: TEST1_I2 Cost: 10881.89 Degree: 1 Resp: 10881.89 Card: 304.81 Bytes: 21 Notice that line up there that says '****** Recost for ORDER BY (using index) ************' ?? It apparently is going to go through the calculations again and adjust for the cost of the 'order by' clause. But it doesn't even consider using the Primary Key!! It only evaluates the Table Scan and Range scan of TEST_I2. And chooses the more expensive of the two!!! Are there other parts of the 10053 trace that I should be examining more closely and am just not seeing? Can anyone explain why it's doing this? -- //www.freelists.org/webpage/oracle-l