Oracle Scratchpad

November 24, 2011

Index Hints

Filed under: Hints,Indexing,Oracle — Jonathan Lewis @ 12:41 pm BST Nov 24,2011

A new form of index hint appeared in 10g – and it’s becoming more common to see it in production code; instead of naming indexes in index hints, we describe them. Consider the following hint (expressed in two ways, first as it appeared in the outline section of an execution plan, then cosmetically adjusted to look more like the way you would write it in your SQL):

INDEX(@"SEL$1" "PRD"@"SEL$1" ("PRODUCTS"."PRODUCT_GROUP" "PRODUCTS"."ID"))
index(@sel$1 prd@sel$1(product_group  id))


With this syntax, Oracle is directed to use an index on the table aliased as prd in query block with the (default) name sel$1; the index has to start with the columns (product_group, id) in that order – with preference given to an exact match, otherwise using the lowest cost index that starts the right way. In passing, although this example shows the default query block names, it’s good practice to name query blocks explicitly with the /*+ qb_name() */ hint.

With this in mind, what is the correct way to hint an index with the following definition:

create index prd_case on products(
        case product_group
                when 'CLASSICAL CD' then id
        end
)
;

The correct answer is the obvious guess – you have to go back to the old syntax which (again in two versions) would be:

INDEX(@"SEL$1" "PRD"@"SEL$1" "PRD_CASE")
index(@sel$1 prd@sel$1 prd_case)

There simply is no alternative.

So how about hinting a bitmap join index, e.g:

create bitmap index pe_home_st_idx on people(st.name)
from
        states  st,
        towns   ho,
        people  pe
where
        ho.id_state     = st.id
and     pe.id_town_home = ho.id
;

Again you could use the traditional approach of supplying the index name, but in this case you can also take the newer approach of supplying the index description – except you have to remember to qualify any column names with their table name (irrespective of the aliases you used for the table in the query) e.g:

/*+ index(pe (states.name)) */

Finally, left as an exercise to the readers, how should you hint an index on a virtual column:

alter table products
        add cd_product generated always as (
                case product_group
                        when 'CLASSICAL CD' then id
                end
        ) virtual
;

create index prd_virt on products(cd_product);

If you check user_tab_columns and user_ind_columns they both contain the column name cd_product, so it seems pretty clear that we should be able to use the 10g description method – but will the optimizer recognise the index if hinted that way, and what will the outline section of the execution plan show ?

8 Comments »

  1. From the outline data, the old methed is used: index_rs_asc(@sel$1,production_information@sel$1,prd_virt)

    I guess because the index type is function-base

    Comment by Sid — November 24, 2011 @ 3:21 pm BST Nov 24,2011 | Reply

  2. “There simply is no alternative.”

    There is no (sensible) alternative.
    i.e. could you not use the hidden column name?

    SQL> explain plan for
      2  select * 
      3  from t2 
      4  where nvl(col1,1) > 0;
    
    Explained.
    
    SQL> 
    SQL> select * from table(dbms_xplan.display);
    
    PLAN_TABLE_OUTPUT
    ----------------------------------------------------------------------------
    Plan hash value: 1513984157
    
    --------------------------------------------------------------------------
    | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |      | 10000 | 80000 |     9  (12)| 00:00:01 |
    |*  1 |  TABLE ACCESS FULL| T2   | 10000 | 80000 |     9  (12)| 00:00:01 |
    --------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - filter(NVL("COL1",1)>0)
    
    13 rows selected.
    
    SQL> 
    SQL> explain plan for
      2  select /*+
      3           index(@"SEL$1" "T2"@"SEL$1" ("T2"."SYS_NC00002$")) 
      4           */ 
      5         * 
      6  from t2 
      7  where nvl(col1,1) > 0;
    
    Explained.
    
    SQL> 
    SQL> select * from table(dbms_xplan.display);
    
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------------
    Plan hash value: 3246639252
    
    ------------------------------------------------------------------------------------
    | Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    ------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT            |      | 10000 | 80000 |    38   (3)| 00:00:01 |
    |   1 |  TABLE ACCESS BY INDEX ROWID| T2   | 10000 | 80000 |    38   (3)| 00:00:01 |
    |*  2 |   INDEX RANGE SCAN          | I_T2 | 10000 |       |    22   (5)| 00:00:01 |
    ------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - access(NVL("COL1",1)>0)
    
    14 rows selected.
    
    SQL> 
    

    Comment by Dom Brooks — November 24, 2011 @ 4:43 pm BST Nov 24,2011 | Reply

    • I just realised that whilst going off and exploring the points you were making, I wandered away from the FBI you had with a CASE and it’s possible that my FBI with NVL may be different in some way to an expression with case….

      Comment by Dom Brooks — November 25, 2011 @ 8:23 am BST Nov 25,2011 | Reply

      • As expected it was the same.
        Of course the danger of the hidden column name is that it might not be the same from environment to environment. So definitely not a sensible approach.

        Comment by Dom Brooks — November 25, 2011 @ 9:15 am BST Nov 25,2011 | Reply

    • Dom,

      Nice comment, especially the addition of (sensible).
      Thanks for that – and the follow-up.
      Interestingly the same strategy fails (in 10.2.0.3) for my bitmap join index example, although it worked for a bitmap join index which was a two-table join rather than three tables
      Your example did make me think about the side effects of things like export/import, and dropping indexes, etc.
      I hope everyone noticed your comment about danger – here’s a little threat waitng to happen if anyone takes that approach in production:

      
      SQL> select column_name from user_tab_cols where table_name = 'PEOPLE';
      
      COLUMN_NAME
      --------------------
      ID_TOWN_WORK
      ID_TOWN_HOME
      PADDING
      
      SQL> create index pe_ho1 on people(id_town_home + 1);
      
      Index created.
      
      SQL> select column_name from user_tab_cols where table_name = 'PEOPLE';
      
      COLUMN_NAME
      --------------------
      ID_TOWN_WORK
      ID_TOWN_HOME
      PADDING
      SYS_NC00004$
      
      SQL> create index pe_ho2 on people(id_town_home + 2);
      
      Index created.
      
      SQL> select column_name from user_tab_cols where table_name = 'PEOPLE';
      
      COLUMN_NAME
      --------------------
      ID_TOWN_WORK
      ID_TOWN_HOME
      PADDING
      SYS_NC00004$
      SYS_NC00005$
      
      SQL> drop index pe_ho1;
      
      Index dropped.
      
      SQL> select column_name from user_tab_cols where table_name = 'PEOPLE';
      
      COLUMN_NAME
      --------------------
      ID_TOWN_WORK
      ID_TOWN_HOME
      PADDING
      SYS_NC00004$
      
      

      Notice how the virtual column for index pe_ho2 starts life as sys_nc00005$, but changes to sys_nc00004$ when you drop the index pe_ho1 ?
      That surprised me – I thought I’d have to use an export import pass to switch virtual columes on the indexes

      Comment by Jonathan Lewis — November 25, 2011 @ 10:05 am BST Nov 25,2011 | Reply

  3. [...] a little history recently I came across a note I’d written about the new-style index hint. In that note I claimed that: … the index has to start with the columns (product_group, id) [...]

    Pingback by Quiz Night « Oracle Scratchpad — January 13, 2012 @ 6:42 pm BST Jan 13,2012 | Reply

  4. Hi Jonathan,

    Which views do we need to query to find the the current running queries which are using indexes with high costs? Kindly suggest.

    Thanks,
    Kartik

    Comment by kartik — April 5, 2012 @ 7:13 am BST Apr 5,2012 | 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,507 other followers