Striving for Optimal Performance
  • Blog
    • Archive
    • Categories
    • Search
  • Troubleshooting Oracle Performance
    • Description
    • Structure
    • Table of Contents
    • Forewords
    • Reviews
    • Downloadable Files
    • Addenda and Errata
    • First Edition
  • Public Appearances
    • Past Public Appearances
  • Contact
  • About

Invisible Indexes and Hints

11 November 2008 6 Comments Written by Christian Antognini

In this post I would like to remove some misinformation about the utilization of hints with invisible indexes.

Let’s start by providing you what two sources say about that topic:

  • Oracle Database 11g: New Features for DBAs and Developers (by Sam R. Alapati and Charles Kim, page 132)
    “If you want the optimizer to take the invisible index into account, you must use the index hint, as shown in the following example:“
  • Oracle Database 11g: The Top New Features for DBAs and Developers – Schema Management (by Arup Nanda)
    “To make the optimizer use the index again, you have to explicitly name the index in a hint:”

Both sources point out that a hint can be used to compel the query optimizer to use an invisible index. Both also provide an example (not shown here). But, what is more important, both are wrong! In other words, a hint cannot be used to compel the query optimizer to use an invisible index.

What’s going on?!?

The problem is that this specific information used to be correct for the beta release of Oracle Database 11g. But that behavior was a bug, not a feature! Therefore, once the bug was fixed, this piece of information was no longer valid.

This is what happens when authors use beta releases for doing their tests…

11gR1, Bug, Indexes, Query Optimizer
Introduce TVD$XTAT
Hotsos Symposium 2009

6 Comments

  1. Radoslav Golian Radoslav Golian
    13 November 2008    

    Good remark, thank you. I didn’t know that this was a bug.

    Reply
  2. Arup Nanda Arup Nanda
    1 September 2009    

    You are right, Chris. And I apologize for not responding to it earlier.
    I was referred to this literally hours ago. Sometimes things change albeit
    in small amounts between beta and production. Those articles were reviewed
    by Oracle PM and they were supposed to have caught that. Well…. Anyway,
    that’s not an excuse; but just a background.

    In 11gR2, the series will be written with production code alone.

    Reply
  3. Christian Antognini Christian Antognini
    1 September 2009    

    Hi Arup

    I sympathize with that… I know that editors want to have books (in your case, articles) as soon as the production release is available and, therefore, authors are pushed to work with beta releases. Hence, there are good chances that something like that happens.

    I originally wrote this post because I heard that specific “myth” too many times! I though it was good to stop it. Or, at least, to try…

    It’s good to know that for 11gR2 it will be different.

    Cheers,
    Chris

    Reply
  4. Mark Lancaster Mark Lancaster
    22 February 2010    

    Hi Chris

    An update on invisible indexes and hints.
    Oracle introduced the USE_INVISIBLE_INDEXES hint in 11.1.0.6, so provided your patched up it works correctly.

    e.g. select /*+ USE_INVISIBLE_INDEXES */ * from your_table;

    In fact they added quite a few new hints, see the V$SQL_HINT view for details.

    Regards

    Mark

    Reply
  5. Christian Antognini Christian Antognini
    28 February 2010    

    Hi Mark

    Thank you very much for your comment. It points out an important thing…

    To make things clear, in my post I was speaking about hints like INDEX and not the new hint USE_INVISIBLE_INDEXES. Let me give an example to make the point cristal clear to everyone ;-)

    SQL> EXPLAIN PLAN FOR SELECT * FROM t1 WHERE n = 1;
    
    SQL> SELECT * FROM table(dbms_xplan.display(format=>'basic'));
    
    PLAN_TABLE_OUTPUT
    ----------------------------------
    Plan hash value: 3617692013
    
    ----------------------------------
    | Id  | Operation         | Name |
    ----------------------------------
    |   0 | SELECT STATEMENT  |      |
    |   1 |  TABLE ACCESS FULL| T1   |
    ----------------------------------
    
    SQL> EXPLAIN PLAN FOR SELECT /*+ index(t1) */ * FROM t1 WHERE n = 1;
    
    SQL> SELECT * FROM table(dbms_xplan.display(format=>'basic'));
    
    PLAN_TABLE_OUTPUT
    ----------------------------------
    Plan hash value: 3617692013
    
    ----------------------------------
    | Id  | Operation         | Name |
    ----------------------------------
    |   0 | SELECT STATEMENT  |      |
    |   1 |  TABLE ACCESS FULL| T1   |
    ----------------------------------
    
    SQL> EXPLAIN PLAN FOR SELECT /*+ use_invisible_indexes */ * FROM t1 WHERE n = 1;
    
    SQL> SELECT * FROM table(dbms_xplan.display(format=>'basic'));
    
    PLAN_TABLE_OUTPUT
    --------------------------------------------
    Plan hash value: 100931685
    
    --------------------------------------------
    | Id  | Operation                   | Name |
    --------------------------------------------
    |   0 | SELECT STATEMENT            |      |
    |   1 |  TABLE ACCESS BY INDEX ROWID| T1   |
    |   2 |   INDEX RANGE SCAN          | I_N  |
    --------------------------------------------

    Cheers,
    Chris

    Reply
  6. Raja Govindan Raja Govindan
    9 September 2016    

    Hello Chris,

    Thanks for good information. I created invisible index and forced query to use invisible index by using hint /*+ INDEX (schema.table schema.index) */ and tried with
    another hint /*+ INDEX(table column) */ ; but all were using full table scan. After reading your article , used /*+ use_invisible_indexes */ and query started using that invisible index. Thanks again for your post.

    Regards
    -/Raja Govindan

    Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

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