Oracle Scratchpad

June 21, 2013

Invisible ?

Filed under: Bugs,Hints,Indexing,Oracle — Jonathan Lewis @ 7:14 am BST Jun 21,2013

I’ll probably have to file this one under “Optimizer ignoring hints” – except that it should also go under “bugs”, and that’s one of the get-out clauses I use in my “hints are not hints” argument.

Sometimes an invisible index isn’t completely invisible.

Here’s a demonstration from showing something which, to my mind, is a very annoying problem. The objects are in a tablespace that has been created with uniform extents of 1MB on an 8KB block size, using freelist management. I’ve rigged the Hakan factor to ensure that I get exactly 40 rows per block, and I’ve set the system statistics to ensure that a relatively small swing in cost results in a change in execution plan.

SQL> desc t1
Name                          Null?    Type
----------------------------- -------- --------------------
COLX                                   NUMBER
COLY                                   NUMBER
PADDING                                VARCHAR2(150)

truncate table t1;

insert /*+ append */ into t1
with generator as (
	select	--+ materialize
		rownum id
	from dual
	connect by
		level <= 1e4
	trunc((rownum - 1) / 1000)	colX,
	mod((rownum - 1) , 40)		colY,
	rpad('x',150)			padding
	generator	v1,
	generator	v2
	rownum <= 1e6;


		ownname		 => user,
		tabname		 =>'T1',
		method_opt	 => 'for all columns size 1'

create index t1_one_col on t1(colX) nologging;
create index t1_two_col on t1(colX, colY) nologging;

select	*
from	t1
where	colX = 500

You won’t be surprised to learn that if I run the query I’ve shown above, Oracle uses the index on (colX) to access the table; the 1,000 rows are all in a single cluster of 12 consecutive blocks in the table so even though it looks like quite a large number of rows to access by index, the indexed access path is still an efficient one. However, I’d like to drop this index because it has a huge functional overlap with the index (colX, colY), and I’d hope that the optimizer would simply use the larger index when I dropped the smaller. Just to play safe, though, I’ll make t1_one_col invisible and check the execution plan – and this is what I got (remember, this depends to some degree on my system stats):

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
|   0 | SELECT STATEMENT  |      |  1000 |   154K|   859   (9)| 00:00:05 |
|*  1 |  TABLE ACCESS FULL| T1   |  1000 |   154K|   859   (9)| 00:00:05 |

Predicate Information (identified by operation id):
   1 - filter("COLX"=500)

The optimizer has picked a full tablescan because the pattern of the data (combined with the definition of the index) has produced a much larger clustering_factor on the t1_two_col index than on the t1_one_col index; but that’s not a big problem, for testing purposes I can always put a hint into the SQL, and since the version is newer than 9i I can use the “index description” syntax so that I can tell the optimizer to use t1_one_col if it’s available, but the best index that starts with the same columns in the same order if t1_one_col isn’t available:

	/*+ index(t1(colX)) */
from	t1
where	colX = 500

This query should use the most cost-effective index on the table that starts with column colX – and since I’ve made t1_one_col invisible the optimizer should use index t1_two_col. Unfortunately the optimizer ignored my hint !

Since I was working with a small, private, data set the obvious thing to do next was to drop t1_one_col to show that the optimizer could be made to use index t1_two_col; and this is the resulting plan for exactly the same (hinted) query:

| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
|   0 | SELECT STATEMENT            |            |  1000 |   154K|  1007   (1)| 00:00:06 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1         |  1000 |   154K|  1007   (1)| 00:00:06 |
|*  2 |   INDEX RANGE SCAN          | T1_TWO_COL |  1000 |       |     5   (0)| 00:00:01 |

Predicate Information (identified by operation id):
   2 - access("COLX"=500)

Normally if there is no exact match for an “index structure” hint the optimizer will associate the hint with any index that starts with the correct set of columns in the right order; if there is an exact match the hint is associated only with that index.

However it looks as if the selection of candidates that match the hint is made before the optimizer checks for index visibility. As a result, if you’ve used the new-style hints in your code and hope to have a period of running on production with invisible indexes as a way of testing a change in your indexing strategy  (e.g. adding a column to an index to reduce visits to a table, dropping an index that is a prefix to another index) you may find that after a successful test period you still see plans change when you finally drop the indexes that you had made invisible.

Bonus blog note:

There are other cases when an invisible index isn’t quite as invisible as you might hope. Here’s a blog that I noticed a little while ago with example involving v$object_usage:

Update Jan 2014

This is bug 17727676, reported fixed in 12.2 – with the option for a one-off patch to earlier versions.


  1. Jonathan,

    very interesting – perhaps “vague index” would have be a better name since they seem to be not that invisible (being used to support uniqueness, prevent fk locking issues, supply statistics etc.).

    If someone else trys to play with the example: I had to tweak my system statistics to reproduce the results (with the mentioned hakan factor and ~ 25000 blocks; MBRC: 128; SREADTIM: 5; MREADTIM: 25 – to get the FTS cost < 1000).



    Comment by Martin Preiss — June 21, 2013 @ 8:22 am BST Jun 21,2013 | Reply

    • Martin,

      I think “invisible” is only intended to mean invisible to the optimizer – which makes all your other examples legal – I’m struggling now to come up with a more evocative (and satirical) name.

      Your system stats tweaks were remarkably similar to mine – perhaps I should have included those in the setup, so thanks for including them.

      Comment by Jonathan Lewis — June 21, 2013 @ 5:58 pm BST Jun 21,2013 | Reply

  2. Hi Jonathan,
    i also noticed that behavior some time ago. I used your example and just “faked” the block count statistic for the table to get the FTS without the hint. However we can see that your assumption “it looks as if the selection of candidates that match the hint is made before the optimizer checks for index visibility” is absolutely true, if we look closely at the CBO trace.

    Here is the CBO trace snippet from my database.

    Table Stats::
      Table: T1  Alias: T1
        #Rows: 1000000  #Blks:  20  AvgRowLen:  158.00  ChainCnt:  0.00
    Index Stats::
      Index: T1_ONE_COL  Col#: 1
        LVLS: 2  #LB: 2077  #DK: 1000  LB/K: 2.00  DB/K: 22.00  CLUF: 22730.00
        User hint to use this index
      Index: T1_TWO_COL  Col#: 1 2
        LVLS: 2  #LB: 2493  #DK: 40000  LB/K: 1.00  DB/K: 22.00  CLUF: 911725.00
    Access path analysis for T1
      Single Table Cardinality Estimation for T1[T1] 
      Column (#1): COLX(
        AvgLen: 4 NDV: 1000 Nulls: 0 Density: 0.001000 Min: 0 Max: 999
      Table: T1  Alias: T1
        Card: Original: 1000000.000000  Rounded: 1000  Computed: 1000.00  Non Adjusted: 1000.00
      Access Path: TableScan
        Cost:  13.64  Resp: 13.64  Degree: 0
          Cost_io: 7.00  Cost_cpu: 200182429
          Resp_io: 7.00  Resp_cpu: 200182429
      Access Path: index (RangeScan)
        Index: T1_TWO_COL
        resc_io: 917.00  resc_cpu: 6920360
        ix_sel: 0.001000  ix_sel_with_filters: 0.001000 
        Cost: 917.23  Resp: 917.23  Degree: 1
      Best:: AccessPath: TableScan
             Cost: 13.64  Degree: 1  Resp: 13.64  Card: 1000.00  Bytes: 0
    Dumping Hints
      atom_hint=(@=0x7f817dc1dda8 err=0 resol=1 used=1 token=83 org=1 lvl=3 txt=INDEX ("T1" "T1_ONE_COL") )

    Check the comment "User hint to use this index / INVISIBLE" and the missing (corresponding) part in the access path analysis.


    Comment by Stefan Koehler — June 21, 2013 @ 11:33 am BST Jun 21,2013 | Reply

    • Stefan,

      Thanks for that – I’m always happy to see additional information coming in about anomalies like this.
      (Too bad for me that I’d already got a post scheduled for Sunday evening saying the same thing – but I’ll be posting it anyway ;)

      Comment by Jonathan Lewis — June 21, 2013 @ 6:01 pm BST Jun 21,2013 | Reply

  3. That’s an interesting one Jonathan. I never really liked the “index description” syntax. Just seems to me that if you know you need to hint for an index you know the one you want. It’s both more clear what your intentions are and more clear what you want the optimizer to do if you use the name. Of course then if you later change the name of the index it could cause you grief, but changing names rarely (if ever) happens. And falling back to another index may or may not be what you want. At any rate, this one was interesting and has ramifications when using SPM if you mess with invisible indexes, as you know. Thanks for posting this.

    Comment by Kerry Osborne — June 21, 2013 @ 2:16 pm BST Jun 21,2013 | Reply

    • Kerry,

      Thanks for the comment – I hope this behaviour gets recognised as a bug and fixed at some point. In the meantime, it’s interesting to note what ramifications start to crawl out of the woodwork when you starting thinking about anomalies like this.

      I can’t really agree with your view on the naming versus describing – I won’t go into details now, but it might be quite fun (and educational) to set up a debate at some conference in the future where we argue the pros and cons of the two methods.

      Comment by Jonathan Lewis — June 21, 2013 @ 6:09 pm BST Jun 21,2013 | Reply

  4. […] In my last post I made a comment about how the optimizer will use the new format of the index hint to identify an index that is an exact match if it can, and any index that starts with the same columns (in the right order) if it can’t find an exact match. It’s fairly easy to demonstrate the behaviour in 11g by examining the 10053 (CBO) trace file generated by a simple, single table, query – in fact, this is probably a case that Doug Burns might want to cite as an example of how, sometimes, the 10053 is easy to interpret (in little patches): […]

    Pingback by Index Hints | Oracle Scratchpad — June 23, 2013 @ 6:04 pm BST Jun 23,2013 | Reply

  5. […] bug/blog in question was this one, and the problem is fixed in […]

    Pingback by Expert | Oracle Scratchpad — January 27, 2014 @ 9:18 am GMT Jan 27,2014 | Reply

  6. […] Here’s the plan I get when I make the descending index invisible: […]

    Pingback by Descending Indexes | Oracle Scratchpad — July 17, 2015 @ 9:08 pm BST Jul 17,2015 | 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 )

Google photo

You are commenting using your Google 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.

Powered by