1

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

Improve this question

1 Answer 1

Reset to default
1

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

Improve this answer
7
  • I am not the developer. The table is just that. I am interested to know why Oracle doesn't use an INDEX UNIQUE SCAN.
    – Shaunyl
    Commented Feb 12, 2015 at 16:09
  • Then you should probably talk to the developer on why this table needs to be an IOT.
    – Gandolf989
    Commented Feb 12, 2015 at 16:11
  • They access the data always via the primary key, so with a IOT this action is quicker.
    – Shaunyl
    Commented Feb 12, 2015 at 16:13
  • You can find out for certain if the table returns rows faster than an IOT by creating a copy of the table as a regular table and then creating the same indexes. then query both tables and see which returns the rows faster. You will probably get similar or better results with a regular table. Don't take my word for it though, test it on your own.
    – Gandolf989
    Commented Feb 12, 2015 at 16:30
  • In this table they add multiple rows every time the batch is executed (about once a week). If it was an heap, these data would be added to the end in different blocks. The IOT, instead, force a physical clustering of data (less blocks to read). Also, cause they access only via PK, the remaining fields are present at that location. The IOT avoids the IO operation of following a rowid back to table data. Anyway, my question is not about a comparison between IOT and Heap, but why the optimizer does not use an Index Unique Scan in that situation. So please, don't go off on a tangent. Thanks.
    – Shaunyl
    Commented Feb 12, 2015 at 16:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.