Oracle Scratchpad

February 6, 2012

Index naming

Filed under: CBO,Indexing,Oracle — Jonathan Lewis @ 6:28 pm BST 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 (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 11.2.0.3.

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

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

-- script reference: index_pk_ignored.sql

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.

select
	rowid
from
	t1
where
	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.

INDEX_NAME             BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR 
-------------------- -------- ----------- ----------------- 
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.

9 Comments »

  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 (11.2.0.1 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)

    Regards
    Pavol Babel

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

  2. Hi Jonathan,

    do you mean Doc ID 73167.1?

    Regards,
    Mauro

    Comment by Mauro — February 7, 2012 @ 4:12 am BST 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 BST 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.

      Regards
      Pavol Babel

      Comment by Pavol Babel — February 7, 2012 @ 10:42 pm BST 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 BST 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 BST Feb 7,2012 | Reply

    • US7ASCII…

      Comment by Pavol Babel — February 7, 2012 @ 11:17 pm BST 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 BST 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 BST Mar 12,2012


RSS feed for comments on this post. TrackBack URI

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

The Rubric Theme Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 3,508 other followers