Oracle Scratchpad

November 11, 2013

Reverse Key

Filed under: Indexing,Oracle,Performance — Jonathan Lewis @ 3:19 pm BST 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.

create table t1 pctfree 75 pctused 25
as
select * from all_objects where rownum <= 10000; 

create table t2 pctfree 75 pctused 25
as
select * from t1; 

create index t1_i1 on t1(object_id); 

begin
 	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');
end;
/

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:


select
	/*+
		leading(t2) use_nl(t1)
	*/
	t2.object_name, t1.object_name
from
	t2		t2,
	t1@&m_target	t1
where
	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):
----------------------------------------------------
   3 - SELECT /*+ USE_NL ("T1") */ "OBJECT_NAME","OBJECT_ID" FROM "T1" "T1" WHERE
       "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 12.1.0.1). 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):
----------------------------------------------------
   3 - SELECT /*+ USE_NL ("T1") */ "OBJECT_NAME","OBJECT_ID" FROM "T1" "T1" WHERE
       "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.

7 Comments »

  1. Jonathan,

    interesting issue !

    two typos, I think
    “it doesn’t know about the remove index” should probably be “it doesn’t know about the remote index”
    and
    “and one of those tables has an index which is unique” should probably be “and one of those tables has an index which happens to be unique”

    Please feel free to remove this reply

    Matthias

    Comment by Matthias Rogel — November 11, 2013 @ 3:31 pm BST Nov 11,2013 | Reply

  2. Probably in “it doesn’t know about the remove index” should be “remote index”.

    Comment by Yuri — November 11, 2013 @ 3:35 pm BST Nov 11,2013 | Reply

  3. It’s nice to have such observant readers – thanks for pointing out the errors, now fixed.

    Comment by Jonathan Lewis — November 11, 2013 @ 4:58 pm BST Nov 11,2013 | Reply

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

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

  5. 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 BST Nov 11,2013 | Reply

  6. 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 BST 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 BST Nov 29,2013 | Reply


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,514 other followers