why is the optimizer doing this?

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?


--
http://www.freelists.org/webpage/oracle-l


Other related posts: