Oracle Scratchpad

November 11, 2013

Reverse Key

Filed under: Indexing,Oracle,Performance — Jonathan Lewis @ 3:19 pm GMT Nov 11,2013

Here’s one of those little details which I would have said just couldn’t be true – except it’s in the manuals, and the manuals happen to be right.

rem     Script:         reverse_limitation.sql
rem     Author:         J.P.Lewis
rem     Dated:          Nov 2013

create table t1 pctfree 75 pctused 25
select * from all_objects where rownum <= 10000 -- > comment to work around for WordPress formatting issue

create table t2 pctfree 75 pctused 25
select * from t1

create index t1_i1 on t1(object_id);

        dbms_stats.gather_table_stats(user,'t1',method_opt=>'for all columns size 1');
        dbms_stats.gather_table_stats(user,'t2',method_opt=>'for all columns size 1');

I’ve created a couple of table which take up few hundred blocks – and one of those tables has an index which is nearly unique. Now I’m going to run a simple query which picks one or two rows from one table and joins them to the other table; I’m going to force a particular execution plan, and I’m going to use a distributed query (taking advantage of a loopback connection qualifier so that I don’t actually have to have two separate database). Here’s the query and execution plan:

		leading(t2) use_nl(t1)
	t2.object_name, t1.object_name
	t2		t2,
	t1@&m_target	t1
	t2.object_name = 'DUAL'
and	t1.object_id = t2.object_id

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
|   0 | SELECT STATEMENT   |      |     1 |   103 |   217   (1)| 00:00:01 |        |      |
|   1 |  NESTED LOOPS      |      |     1 |   103 |   217   (1)| 00:00:01 |        |      |
|*  2 |   TABLE ACCESS FULL| T2   |     1 |    24 |   215   (1)| 00:00:01 |        |      |
|   3 |   REMOTE           | T1   |     1 |    79 |     2   (0)| 00:00:01 | ORCL@~ | R->S |

Predicate Information (identified by operation id):
   2 - filter("T2"."OBJECT_NAME"='DUAL')

Remote SQL Information (identified by operation id):
       "OBJECT_ID"=:1 (accessing 'ORCL@LOOPBACK' )

I’ve actually used a substitution variable from SQL*Plus, (&m_target) so that I could test the same code conveniently on several different versions (including The critical point to notice in the plan is that we HAVE to do a full tablescan on t2 to get the (very few) rows identified by the filter predicate, and this tablescan has a cost of 215 – quite large. It is then clear from the cost of the remote section that the optimizer is aware of the fact that there is an index at the far end of the join which it can use to find the matching rows very cheaply.

Now let’s rebuild the index on t1 as a reverse key index, and see what the plan looks like:

alter index t1_i1 rebuild reverse;

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
|   0 | SELECT STATEMENT   |      |     1 |   103 |   431   (1)| 00:00:01 |        |      |
|   1 |  NESTED LOOPS      |      |     1 |   103 |   431   (1)| 00:00:01 |        |      |
|*  2 |   TABLE ACCESS FULL| T2   |     1 |    24 |   215   (1)| 00:00:01 |        |      |
|   3 |   REMOTE           | T1   |     1 |    79 |   215   (1)| 00:00:01 | ORCL@~ | R->S |

Predicate Information (identified by operation id):
   2 - filter("T2"."OBJECT_NAME"='DUAL')

Remote SQL Information (identified by operation id):
       "OBJECT_ID"=:1 (accessing 'ORCL@LOOPBACK' )

The cost of the remote query jumps to 215 – the same as the cost of a tablescan. Of course, in this particular case the remote optimizer WILL use the index when the remote query reaches it, but the local optimizer has demonstrated that it doesn’t know about the remote index, which means it is likely to ignore execution plans which would be able to use this index very effectively, and it may even produce execution plans where the existence of this reverse key index causes problem when the remote query reaches the remote database.

I’ve tested a few scenarios to confirm this, although I haven’t yet run a 10053 trace to prove the point beyond any doubt – I really ought to do this, but I found that this behaviour is documented in the manuals, specifically the Oracle 8i Performance Tuning Guide and Reference:

Reverse indexes on remote tables are not visible to the optimizer. This can prevent nested-loop joins from being used for remote tables if there is an equijoin using a column with only a reverse index.

I haven’t yet found this statement repeated in any of the more recent manuals – but it certainly appears to be true.

I’ve made comments in the past about being cautious with reverse key indexes – here’s another good reason for checking carefully for side effects.

Update July 2017

I’ve just repeated the test on – and the problem is still there; the local instance costs the remote table in the nested loop as if it is expecting a tablescan when the index on the remote table is reversed.



  1. Can you think of any reasons why a reverse key index should be invisible?

    Comment by Dom Brooks — November 11, 2013 @ 6:45 pm GMT Nov 11,2013 | Reply

  2. I have some old applications built in Delphi 5 connecting to one of the databases I run.

    Once we decided to build a reverse index do speed up a query and guess what… the application crashed because of that index!

    Comment by Daniel Stolf — November 11, 2013 @ 6:55 pm GMT Nov 11,2013 | Reply

  3. I’m amazed that so much does actually work over Database Links, given potentially different database versions, OSs, endianess, db character sets, SCN synchronisation etc. I wonder whether Pluggable Databases will get some special “intra-container” database link functionality.

    Comment by Gary — November 17, 2013 @ 12:18 am GMT Nov 17,2013 | Reply

    • Gary,

      That’s an interesting thought. But then we run into the painful philosophical problem: “It doesn’t work with database links, but it is working with PDBs – is that deliberate or a temporary accident?”

      I would expect most things to work properly across database links – though perhaps with some time-lag before the Oracle developers get around to it – because so much of exadata is in the communication between machines to ensure common understanding, and that’s only become possible because the communication rates have become so much faster in recent years. Some of the db_link limitations (e.g. no transfer of histogram information) were surely due to protecting systems from slow network communication.

      Comment by Jonathan Lewis — November 29, 2013 @ 9:32 am GMT Nov 29,2013 | Reply

  4. I’m hoping that my data architects will read this great article, for a few mounts I’m suggesting “hash partitioning – global hash partitioned index” instead “reverse index” to solve “contention for index’s block”, … no luck.
    By the way the “reverse index” does not hep if the contention is on the root block of the index, and “range scan” on “reverse index” is restricted, so in general “reverse index” is not good solution.
    Thanks for article and knowledge sharing.

    Andjelko Miovcic

    Comment by andjelko miovcic — June 22, 2015 @ 9:57 pm BST Jun 22,2015 | Reply

  5. […] For a completely different (documented) issue with reverse key indexes, see this note on how they can be ignored in distributed queries. […]

    Pingback by Reverse Key | Oracle Scratchpad — April 7, 2018 @ 9:23 am BST Apr 7,2018 | Reply

  6. […] finishing this note, I discovered that I had written a similar note about reverse key indexes nearly five years ago. Arguably a reverse key is just a special case of a function-based index – except it’s […]

    Pingback by FBI Limitation | Oracle Scratchpad — May 4, 2018 @ 9:19 am BST May 4,2018 | 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.

Website Powered by