Why the optimizer does not use an Index Unique Scan

execution-planindex-tuningoracleoracle-10g-r2tuning

I am on Oracle 10.2.0.4.0. I have a big Index Organized Table (IOT) with 74 columns and over 345 million of rows. An external tool execute a batch where I cannot touch the source code. The critical query in the batch execute a SELECT against multiple columns of the table, filtering with three query predicates using three different equality operators. This three predicates are passed as Bind Variables, and during the batch they change their values. Sometimes, during a Bind Variable change, the CBO choose another execution plan, that is not good.
The predicates are these:

.. WHERE ((COSTEVENT.ACC_NUM=:PREVALUE0) AND 
  (COSTEVENT.EVENT_SEQ=PREVALUE1)) AND (COSTEVENT.EVENT_REF=PREVALUE2);

The table has an UNIQUE INDEX on the column EVENT_REF. There is a composite PRIMARY KEY on five columns, two of which are EVENT_SEQ and ACC_NUM (the others are EVENT_TYPE_ID and EVENT_SOURCE and EVENT_REF).
When the query performs well, the CBO chooses the following execution plan:

-------------------------------------------------------------------------------------
| Id  | Operation         | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                 |       |       |     8 (100)|          |
|   1 |  INDEX UNIQUE SCAN| COSTEVENT_PK    |     1 |   265 |     8   (0)| 00:00:01 |
|   2 |   INDEX RANGE SCAN| COSTEVENT_UK1   |     1 |       |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

   1 - access("COSTEVENT"."EVENT_REF"='00DE320000620E1')
       filter("COSTEVENT"."EVENT_SEQ"=11 AND
              "COSTEVENT"."ACC_NUM"='LA00032914')
   2 - access("COSTEVENT"."EVENT_REF"='00DE320000620E1')

But during the batch, sometimes, the CBO switch to another plan, the bad plan:

-----------------------------------------------------------------------------------
| Id  | Operation        | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                |     1 |   266 |     5   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| COSTEVENT_PK   |     1 |   266 |     5   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("COSTEVENT"."ACC_NUM"='LA00032914' AND
              "COSTEVENT"."EVENT_SEQ"=111 AND "COSTEVENT"."EVENT_REF"='00DE320000620E1')
       filter("COSTEVENT"."EVENT_REF"='00DE320000620E1')

Instead of accessing first to the EVENT_REF unique index key, and then performing the limited range scan, the CBO, for some strange reason, access first to ACC_NUM, and the applies the filter; but ACC_NUM is not UNIQUE so he needs to perform an INDEX RANGE SCAN through million and million of rows.
If I add the hint /*+ index(COSTEVENT COSTEVENT_UK1) */ to the SELECT, the CBO chooses correctly for an INDEX UNIQUE SCAN, but I can't access to the source code.

Other useful info are here:

COLUMN_NAME                       DENSITY LOW_VALUE                                HIGH_VALUE                               NUM_DISTINCT HISTOGRAM
------------------------------ ---------- ---------------------------------------- ---------------------------------------- ------------ ---------------
ACC_NUM                        .001146789 30303030343037                           4E3030303331343634                              31011 HEIGHT BALANCED
EVENT_SEQ                      1.4550E-09 C102                                     C20204                                            100 FREQUENCY
EVENT_SOURCE                   .000808407 30303031383034                           7777772E7469656D706F2E6974                     123629 HEIGHT BALANCED
EVENT_TYPE_ID                  1.4550E-09 C102                                     C119                                               11 FREQUENCY
EVENT_REF                      2.9101E-09 303030364645303030343534343544           303044434644303030303032303634              343634290 NONE

and here:

INDEX_NAME                                                                                 CLUSTERING_FACTOR   NUM_ROWS 
------------------------------------------------------------------------------------------ ----------------- ---------- 
COSTEVENT_PK                                                                                               0  345257860 
COSTEVENT_UK1                                                                                       30346000  345738415 

The question are:
1) why Oracle chooses for and INDEX RANGE SCAN instead of an INDEX UNIQUE SCAN, even if the first is extremely slower than the second?
2) There is a way to lead Oracle to the right choice, without having to apply a SQL Profile, for example? (maybe manipulating histograms, and statistics, etc..)

Thanks in advance

Best Answer

I have a big Index Organized Table (IOT) with 74 columns

Why do you think that this table is a candidate for an IOT? IMHO, IOT's are meant to be narrow tables that don't change often. I have done testing with narrow tables and have found IOT's to do inserts at a slower rate than a traditional table with indexes. You may want to start by recreating this as a traditional table and then think about what indexes you want on the table. Also there is a note in this article on IOT's. Performance Tuning Guide