Oracle Scratchpad

February 6, 2012

Index naming

Filed under: CBO,Indexing,Oracle — Jonathan Lewis @ 6:28 pm GMT Feb 6,2012

Here’s a model of a little problem I came across recently. It’s something I wrote about many years ago, and I thought I’d seen a note on Metalink [Ed: probably Note 73167.1 – see comments 2 and 4 below] explaining that the issue had been addressed; but the problem is still there, even in

We start with a little data set (and it’s my standard setup of 8KB blocks, LMTs, 1MB uniform extents, and no ASSM):

rem     Script:         index_pk_ignored.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Jan 2012

create table t1
with generator as (
        select  --+ materialize
                rownum id 
        from dual 
        connect by 
                level <= 10000
        trunc((rownum-1)/2)     product_id,
        mod(rownum-1,2)         flag1,
        mod(rownum-1,3)         flag2,
        rownum                  n1,
        lpad(rownum,30)         v1
        generator       v1,
        generator       v2
        rownum <= 1000000

-- collect stats, compute, no histograms

alter table t1 add constraint t1_pk primary key (product_id,flag1, flag2) 
using index (
        create unique index t1_pk on t1(product_id, flag1, flag2)

create index t1_i1 on t1(product_id, flag2, flag1, n1,v1);

As so often happens with the anomalies I uncover on client sites, there’s something a bit odd about this model. The second index starts with all the columns of the primary key (although the column order is slightly different). There is a rationale for this: the second index makes a big difference to a particular overnight batch job because it eliminates a large number of random accesses to a very big table. The intention is to drop the primary index eventually and use this larger index to cover the primary key.

Here’s an example of a very simple SQL statement which is run very frequently during the day – but don’t ask why this statement happens, there is a good rationale but it would take a lengthy explanation.

        product_id = 2500

Which index do you think Oracle will use to execute this query ? If it’s not obvious from the index definitions I’ll give you an extra clue by reporting a few of the index statistics as well.

-------------------- -------- ----------- ----------------- 
T1_PK                       2       2,807             7,088 
T1_I1                       2       7,996             7,154 

The answer is that Oracle uses the larger index. Here are the execution plans I get from hinting the query to use each index in turn:

Hinted to PK

| Id  | Operation        | Name  | Rows  | Bytes | Cost  |
|   0 | SELECT STATEMENT |       |     2 |    34 |     3 |
|*  1 |  INDEX RANGE SCAN| T1_PK |     2 |    34 |     3 |

Hinted to larger index

| Id  | Operation        | Name  | Rows  | Bytes | Cost  |
|   0 | SELECT STATEMENT |       |     2 |    34 |     3 |
|*  1 |  INDEX RANGE SCAN| T1_I1 |     2 |    34 |     3 |

The two indexes produce the same cost (and you can check this in more detail in the 10053 trace) – so why does Oracle use the “wrong” index ? Because when you sort the index names alphabetically, T1_I1 comes before T1_PK. If you doubt me at this point, by the way, just run the test and then repeat it using the name T1_XX instead of T1_I1.

You might think the problem has something to do with bad statistics, of course – but that’s why I specified compute in the stats collection. To hammer the point home I’ve also run tests hacking the statistics on the T1_I1 index, doubling virtually every statistic (leaf blocks, clustering_factor, etc.) except the number of distinct keys – which I halved. There is a statistical solution – but I’d have to push the statistics on this index a very long way before the arithmetic for this query made the larger index more expensive than the smaller one.

I thought I’d seen a note on MOS (Metalink) saying that this tie-break situation had been addressed with an extra test based on the number of distinct keys, (I’m not sure it’s been implemented, but clearly these two indexes have the same number of distinct key anyway). Perhaps a check on the absolute number of leaf blocks in the index might also be in order if the key count is a tie. In the meantime, of course, I can add a SQL Baseline to the query.

The choice of index doesn’t make any difference to the performance of any individual execution of the query, of course, but it does mean that I don’t have both the indexes taking up space in the cache during the day when the larger index shouldn’t really be used, so statistically the wrong choice has an impact on every execution of this query and every other query on the system.


  1. Jonathan,

    it is too late so unless I’m mistaken it seems simple increasing of BLEVEL statistic by one for T1_I1 worked on my site ( DB). The cost for T1_I1 index has been increased from 3 to 4, which makes sense to my mind. As you suggested, we would have to increase leaf_blocks statistic dramatically to make T1_I1 expensive enough. However, fine blevel hacking could be smart solution (especially in 10g where SQL baselines can’t be used)
    This tie-break situation is quite rare, but I have faced it several times, too. Oracle should fix the code, the alphabetical sort rule should be really used as the last resort (which obviously is not in this situation)

    Pavol Babel

    Comment by Pavol Babel — February 7, 2012 @ 2:02 am GMT Feb 7,2012 | Reply

  2. Hi Jonathan,

    do you mean Doc ID 73167.1?


    Comment by Mauro — February 7, 2012 @ 4:12 am GMT Feb 7,2012 | Reply

  3. The two indexes produce the same cost
    Does this not mean the costing itself has an issue here ? Should the cost of accessing index with larger width (due to more number of columns) be more than accessing index with smaller width, especially when both are being probed with range scans ?

    Comment by Narendra — February 7, 2012 @ 9:24 am GMT Feb 7,2012 | Reply

    • The basic formula for index range scan is OK. It goes like BLEVEL + ceiling(selectivity * leaf_blocks) . However, in Jonathan’s example the selectivity for product_id is approximately 1/500000. So CBO predicts only 3 LIO for both access paths (to change it, we would have to set leaf_blocks statistic for T1_I1 to at least 500 001 ).
      It is not very common to have two indexes with same prefixed columns (especially such a selective one), but sometimes there could be reason for that.

      Pavol Babel

      Comment by Pavol Babel — February 7, 2012 @ 10:42 pm GMT Feb 7,2012 | Reply

  4. This is the MOS note
    Handling of equally ranked (RBO) or costed (CBO) indexes (Doc ID 73167.1)

    Comment by AMC — February 7, 2012 @ 2:51 pm GMT Feb 7,2012 | Reply

  5. “Because when you sort the index names alphabetically”

    Uhm… does the NL parameters play a role here or not? (i.e.: different language settings = different sorting)
    Or Oracle internally always use an “English sort”?

    Comment by lascoltodelvenerdi — February 7, 2012 @ 10:25 pm GMT Feb 7,2012 | Reply

    • US7ASCII…

      Comment by Pavol Babel — February 7, 2012 @ 11:17 pm GMT Feb 7,2012 | Reply

      • > US7ASCII…

        Are you sure? This works :-)

        create table t(id int,i1 int);
        create index "Ä" on t(i1);
        create index "ä" on t(id);
        create index "a" on t(i1,id);
        create index "A" on t(id,i1);
        select index_name from user_indexes where table_name='T' order by index_name;

        Comment by Flado — March 2, 2012 @ 4:48 pm GMT Mar 2,2012 | Reply

        • Flado,

          I also thought that object names were restricted to ASCII (7-bit) – but all I could find in the manuals was advice to stick to ASCII because that gives maximum transportability. There were also the comments about names being stored in uppercase only in the data dictionary unless quoted – so the introduces a few more restrictions on how easily you can use them. Perhaps the naming has to be restricting to the single byte characters of the database character set.

          Your example is quite entertaining as a demo, because the order of output changes if you do:

          alter session set nls_comp=linguistic;
          alter session set nls_sort = binary_ci;

          This sort of thing could make a real mess of peoples’ favourite admin scripts.

          Comment by Jonathan Lewis — March 12, 2012 @ 12:24 pm GMT Mar 12,2012

  6. […] time to time I’ve mentioned the fact that the optimizer will sort indexes alphabetically by name as the last tie-breaker when two plans have the same cost. Thanks to an email that arrived a couple […]

    Pingback by Index Sorting | Oracle Scratchpad — November 12, 2021 @ 6:41 pm GMT Nov 12,2021 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by

%d bloggers like this: