Oracle Scratchpad

January 25, 2021

Index Hints

Filed under: CBO,dbms_xplan,Hints,Ignoring Hints,Index skip scan,Indexing,Oracle — Jonathan Lewis @ 4:59 pm GMT Jan 25,2021

I’ve lost count of the number of times I’ve reminded people that hinting (correctly) is hard. Even the humble /*+ index() */ hint and its close relatives are open to misunderstanding and accidental misuse, leading to complaints that “Oracle is ignoring my hint”.

Strange though it may seem, I’m still not 100% certain of what some of the basic index hints are supposed to do, and even the “hint report” in the most recent versions of dbms_xplan.display_xxx() hasn’t told me everything I’d like to know. So if you think you know all about hints and indexing this blog note is for you.

I’ll start with a brief, and approximate, timeline for the basic index hints – starting from 8.0

Version Hint
8.0index
8.1index_asc, index_desc, index_ffs, no_index
9.0index_ss, index_ss_asc, index_ss_desc
10.1no_index_ffs, no_index_ss
11.1index_rs_asc, index_rs_desc
Saving these for laterchange_dupkey_error_index, domain_index_filter, domain_index_no_sort, domain_index_sort, ignore_row_on_dupkey_index, index_combine, index_join, index_stats, local_indexes, num_index_keys, parallel_index, use_invisible_indexes, use_nl_with_index, xmlindex_rewrite, xmlindex_rewrite_in_select, xmlindex_sel_idx_tbl

For completeness I’ve included the more exotic index-related hints in the list (without a version), and I’ve even highlighted the rarely seen use_nl_with_index() hint to remind myself to raise a rhetorical question about it at the end of this piece.

In this list you’ll notice that the only hint originally available directed the optimizer to access a table by index, but in 8.1 that changed so that we could

  1. tell the optimizer about indexes it should not use
  2. specify whether the index access should use the index in ascending or descending order
  3. use an index fast full scan.

In 9i Oracle then introduced the index skip scan, with the option to specify whether the skip scan should be in ascending or descending order. The index_ss hint seems to be no more than a synonym for the index_ss_asc hint (or should that be the other way round); ss far as I can tell the index_ss() hint will not produce a descending skip scan.

You’ll note that there’s no hint to block an index skip scan, until the hint no_index_ss() appears in 10g along with the no_index_ffs() hint to block the index fast full scan. Since 10g Oracle has got better at introducing both the “positive” and “negative” versions of a hint whenever it introduces any hints for new optimizer mechanisms.

Finally we get to 11g and if you search MOS you may still be able to find the bug note (4323868.8) that introduced the index_rs_asc() and index_rs_desc() hints for index range scan ascending and descending.

From MOS Doc 4323868.8: “This fix adds new hints to enforce that an index is selected only if a start/stop keys (predicates) are used: INDEX_RS_ASC INDEX_RS_DESC”

This was necessary because by this time the index() hint allowed the optimizer to decide for itself how to use an index and it was quite difficult to force it to use the strategy you really wanted.

It’s still a source of puzzlement to me that an explicit index() hint will sometimes be turned into an index_rs_asc() when you check the Outline Information from a call to dbms_xplan.display_xxx() the Optimizer wants to use to reproduce the plan, while there are other times that an explicit index_rs_asc() hint will be turned into a basic index() hint (which might not reproduce the original plan)!

The Warm-up

Here’s a little surprise that could only reveal itself in the 19c hint report – unless you were willing to read your way carefully through a 10053 (CBO) trace file in earlier versions of Oracle. It comes from a little investigation of the index_ffs() hint that I’ve kept repeating over the last 20 years.

rem
rem     Script:         c_indffs.sql
rem     Dated:          March 2001
rem     Author:         Jonathan Lewis
rem

create table t1
nologging
as
select 
        rownum                  id,
        rpad(mod(rownum,50),10) small_vc,
        rpad('x',50)            padding
from
        all_objects
where
        rownum <= 3000
;

alter table t1 modify id not null;

create index t_i1 on t1(id);
create index t_i2 on t1(small_vc,id);

set autotrace traceonly explain

select 
        count(small_vc)
from    t1
where
        id > 2750
;

select 
        /*+ index(t1) */
        count(small_vc)
from    t1
where
        id > 2750
;

select 
        /*+ index_ffs(t1) */
        count(small_vc)
from    t1
where
        id > 2750
;

select 
        /*+ index_ffs(t1) no_index(t1) */
        count(small_vc)
from    t1
where
        id > 2750
;

set autotrace off

I’ve created a table with two indexes, and then enabled autotrace to get the execution plans for 4 queries that vary only in their hinting. Here’s the plan (on 19.3, with my settings for system stats) for the first query:

------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |     1 |    15 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE       |      |     1 |    15 |            |          |
|*  2 |   INDEX FAST FULL SCAN| T_I2 |   250 |  3750 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("ID">2750)

It’s an index fast full scan on the t_i2 (two-column) index. If I add an index() hint to this query, will that allow Oracle to continue using the index fast full scan, or will it force Oracle into some other path. Here’s the plan for the query hinted with index(t1):

---------------------------------------------------------------------------------------------
| Id  | Operation                            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |      |     1 |    15 |     5   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE                      |      |     1 |    15 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T1   |   250 |  3750 |     5   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                  | T_I1 |   250 |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("ID">2750)

The optimizer has chosen an index range scan on the (single-column) t1 index. Since this path costs more than the index fast full scan it would appear that the index() hint does not allow the optimizer to consider an index fast full scan. So we might decide that an index_ffs() hint is appropriate to secure the plan we want – and here’s the plan we get with that hint:

------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |     1 |    15 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE       |      |     1 |    15 |            |          |
|*  2 |   INDEX FAST FULL SCAN| T_I2 |   250 |  3750 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("ID">2750)

As expected we get the index fast full scan we wanted. But we might want to add belts and braces – let’s include a no_index() hint to make sure that the optimizer doesn’t consider any other strategy for using an index. Since we’ve seen that the index() hint isn’t associated with the index fast full scan path it seems reasonable to assume that the no_index() is also not associated with the index fast full scan path. Here’s the plan we get from the final variant of my query with index_ffs(t1) no_index(t1):

------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |     1 |    15 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE       |      |     1 |    15 |            |          |
|*  2 |   INDEX FAST FULL SCAN| T_I2 |   250 |  3750 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("ID">2750)

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2 (U - Unused (2))
---------------------------------------------------------------------------
   2 -  SEL$1 / T1@SEL$1
         U -  index_ffs(t1) / hint conflicts with another in sibling query block
         U -  no_index(t1) / hint conflicts with another in sibling query block

The query has produced the execution plan we wanted – but only by accident. The hint report (which, by default, is the version that reports only the erroneous or unused hints) tells us that both hints have been ignored because they each conflict with some other hint in a “sibling” query block. In this case they’re conflicting with each other.

So the plan we get was our original unhinted plan – which made it look as if we’d done exactly the right thing to ensure that we’d made the plan completely reproducible. Such (previously invisible) errors can easily lead to complaints about the optimizer ignoring hints.

The Main Event

The previous section was about an annoying little inconsistency in the way in which the “negative” version of a hint may not correspond exactly to the “postive” version. There’s a more worrying issue to address when you try to be more precise in your use of basic index hints.

We’ve seen that an index() hint could mean almost anything other than an index fast full scan, while a no_index() hint (probably) blocks all possible uses of an index, but would you expect an index_rs_asc() hint to produce a skip scan, or an index_ss_asc() hint to produce a range scan? Here’s another old script of mine to create some data and test some hints:

rem
rem     Script:         skip_scan_anomaly.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Jan 2009
rem

create table t1
as
with generator as (
        select  --+ materialize
                rownum  id
        from    all_objects 
        where   rownum <= 3000  -- > hint to avoid wordpress format issue
)
select
        mod(rownum,300)                                 addr_id300,
        mod(rownum,200)                                 addr_id200,
        mod(rownum,100)                                 addr_id100,
        mod(rownum,50)                                  addr_id050,
        trunc(sysdate) + trunc(mod(rownum,2501)/3)      effective_date,
        lpad(rownum,10,'0')                             small_vc,
        rpad('x',050)                                   padding
--      rpad('x',100)                                   padding
from
        generator       v1,
        generator       v2
where
        rownum <= 250000   -- > hint to avoid wordpress format issue
;

create index t1_i1 on t1(effective_date);
create index t1_i300 on t1(addr_id300, effective_date);
create index t1_i200 on t1(addr_id200, effective_date);
create index t1_i100 on t1(addr_id100, effective_date);
create index t1_i050 on t1(addr_id050, effective_date);

I’ve created a table with rather more indexes than I’ll be using. The significant indexes are t1_i1(effective_date), and t1_i050(addr_id050, effective_date). The former will be available for range scans the latter for skip scans when I test queries with predicates only on effective_date.

Choice of execution path can be affected by the system stats, so I need to point out that I’ve set mine with the following code:

begin
        dbms_stats.set_system_stats('MBRC',16);
        dbms_stats.set_system_stats('MREADTIM',10);
        dbms_stats.set_system_stats('SREADTIM',5);
        dbms_stats.set_system_stats('CPUSPEED',500);
exception
        when others then null;
end;
/

And I’ll start with a couple of “baseline” queries and execution plans:

explain plan for
select 
        small_vc
from    t1
where   effective_date >  to_date('&m_start_date','dd-mon-yyyy')
and     effective_date <= to_date('&m_end_date'  ,'dd-mon-yyyy')
;

select * from table(dbms_xplan.display(format=>'hint_report'));

alter index t1_i1 invisible;

explain plan for
select 
        /*+ index(t1) */
        small_vc
from    t1
where   effective_date >  to_date('&m_start_date','dd-mon-yyyy')
and     effective_date <= to_date('&m_end_date'  ,'dd-mon-yyyy')
;

You’ll notice at line 11 I’ve made the t1_i1 index invisible, and it will stay that way for a couple more tests. Here are the first two execution plans:

Unhinted
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  1500 | 28500 |   428   (9)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |  1500 | 28500 |   428   (9)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("EFFECTIVE_DATE"<=TO_DATE(' 2021-02-26 00:00:00',
              'syyyy-mm-dd hh24:mi:ss') AND "EFFECTIVE_DATE">TO_DATE(' 2021-02-22
              00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

Hinted with index(t1)
-----------------------------------------------------------------------------------------------
| Id  | Operation                           | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |         |  1500 | 28500 |  1558   (1)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T1      |  1500 | 28500 |  1558   (1)| 00:00:01 |
|*  2 |   INDEX SKIP SCAN                   | T1_I050 |  1500 |       |    52   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("EFFECTIVE_DATE">TO_DATE(' 2021-02-22 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss') AND "EFFECTIVE_DATE"<=TO_DATE(' 2021-02-26 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss'))
       filter("EFFECTIVE_DATE"<=TO_DATE(' 2021-02-26 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss') AND "EFFECTIVE_DATE">TO_DATE(' 2021-02-22 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss'))

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1
---------------------------------------------------------------------------
   1 -  SEL$1 / T1@SEL$1
           -  index(t1)

Unhinted I’ve managed to rig the data and system stats so that the first path is a full tablescan; then, when I add the generic index(t1) hint Oracle recognises and uses the hint in the best possible way, picking the lowest cost index skip scan.

A variation I won’t show here – if I change the hint to index_rs_asc(t1) the optimizer recognizes there is no (currently visible) index that could be used for an index range scan and does a full tablescan, reporting the hint as unused. It won’t try to substitute a skip scan for a range scan.

What happens if I now try the index_ss(t1) hint without specifying an index. Firstly with the t1_i1 index still invisible, then after making t1_i1 visible again:

explain plan for
select 
        /*+ index_ss(t1) */
        small_vc
from    t1
where   effective_date >  to_date('&m_start_date','dd-mon-yyyy')
and     effective_date <= to_date('&m_end_date'  ,'dd-mon-yyyy')
;

select * from table(dbms_xplan.display(format=>'hint_report'));

Here are the two execution plans, first when t1_i1(effective_date) is still invisible:

-----------------------------------------------------------------------------------------------
| Id  | Operation                           | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |         |  1500 | 28500 |  1558   (1)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T1      |  1500 | 28500 |  1558   (1)| 00:00:01 |
|*  2 |   INDEX SKIP SCAN                   | T1_I050 |  1500 |       |    52   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("EFFECTIVE_DATE">TO_DATE(' 2021-02-22 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss') AND "EFFECTIVE_DATE"<=TO_DATE(' 2021-02-26 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss'))
       filter("EFFECTIVE_DATE"<=TO_DATE(' 2021-02-26 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss') AND "EFFECTIVE_DATE">TO_DATE(' 2021-02-22 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss'))

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1
---------------------------------------------------------------------------
   1 -  SEL$1 / T1@SEL$1
           -  index_ss(t1)

As you might expect the optimizer has picked the t1_i050 index for a skip scan. (There are 3 other candidates for the skip scan, but since the have more distinct values for their leading column they are all turn out to have a higher cost than t1_i050).

So let’s make the t1_i1 index visible and see what the plan looks like:

----------------------------------------------------------------------------------------------
| Id  | Operation                           | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |       |  1500 | 28500 |   521   (1)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T1    |  1500 | 28500 |   521   (1)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | T1_I1 |  1500 |       |     6   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("EFFECTIVE_DATE">TO_DATE(' 2021-02-22 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss') AND "EFFECTIVE_DATE"<=TO_DATE(' 2021-02-26 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss'))

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------
   1 -  SEL$1 / T1@SEL$1
         U -  index_ss(t1)

The optimizer picks an index range scan using the t1_i1 index, and reports the hint as unused! For years I told myself that an index skip scan was derived as a small collection of range scans, so an index range was technically a “degenerate” skip scan i.e. one where the “small collection” consisted of exactly one element. Oracle 19c finally told me I was wrong – the optimizer is ignoring the hint.

The fact that it’s a sloppy hint and you could have been more precise is irrelevant – if the optimizer won’t do a skip scan when you specify a range scan (but watch out for the next “index hints” instalment – see footnote) it shouldn’t do a range scan when you specify a skip scan (but that’s just a personal opinion).

We should check, of course, that a precisely targeted skip scan hint works before complaining too loudly – would index_ss(t1 t1_i050), or index_ss_t1 t1_i300) work when there’s a competing index that could produce a lower cost range scan? The answer is yes.

explain plan for
select 
        /*+ index_ss(t1 t1_i050) */
        small_vc
from    t1
where   effective_date >  to_date('&m_start_date','dd-mon-yyyy')
and     effective_date <= to_date('&m_end_date'  ,'dd-mon-yyyy')
;

select * from table(dbms_xplan.display(format=>'hint_report'));

-----------------------------------------------------------------------------------------------
| Id  | Operation                           | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |         |  1500 | 28500 |  1558   (1)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T1      |  1500 | 28500 |  1558   (1)| 00:00:01 |
|*  2 |   INDEX SKIP SCAN                   | T1_I050 |  1500 |       |    52   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("EFFECTIVE_DATE">TO_DATE(' 2021-02-22 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss') AND "EFFECTIVE_DATE"<=TO_DATE(' 2021-02-26 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss'))
       filter("EFFECTIVE_DATE"<=TO_DATE(' 2021-02-26 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss') AND "EFFECTIVE_DATE">TO_DATE(' 2021-02-22 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss'))

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1
---------------------------------------------------------------------------
   1 -  SEL$1 / T1@SEL$1
           -  index_ss(t1 t1_i050)

If you specify a suitable index in the index_ss() hint then the optimizer will use it and won’t switch to the index range scan. You can, of course, specify the index by description rather than name, so the hint /*+ index_ss(t1 (addr_id050, effective_date)) */ or even a partial description like /*+ index_ss(t1 (addr_id050)) */ would have been equally valid and obeyed.

How much do you know?

I’ll finish off with a rhetorical question, which I’ll introduce with this description take from the 19c SQL Tuning Guide section 9.2.1.6:

The related hint USE_NL_WITH_INDEX(table index) hint instructs the optimizer to join the specified table to another row source with a nested loops join using the specified table as the inner table. The index is optional. If no index is specified, then the nested loops join uses an index with at least one join predicate as the index key.

An intuitive response to this hint would be to assume that most people expect nested loops to use index unique scans or range scans into the second table. So what would your initial expectation be about the validity of use_nl_with_index() if the only way the index could be used was with an index skip scan, or a full scan, or a fast full scan. What if there were two join predicates and there’s a path which could do a nested loop if it used two indexes to do an index join (index_join()) or an index bitmap conversion (index_combine()). Come to that, how confident are you that the hint will work if the index specified is a bitmap index?

Summary

It’s important to be as accurate and thorough as possible when using hints. Even when a hint is documented you may find that you can asked “what if” questions about the hint and find that the only way to get answers to your questions is to do several experiments.

If you’re going to put hints into production code, take at least a little time to say to yourself:

“I know what I want and expect this hint to do; are there any similar actions that it might also be allowed to trigger, and how could I check if I need to allow for them or block them?”

Footnote: This journey of rediscovery was prompted by an email from Kaley Crum who supplied me with an example of Oracle using an index skip scan when it had been hinted to do an index range scan.

6 Comments »

  1. index, imdex_asc, idex_desc, index_ffs (and some other) hints existed in Oracle 7.3.4, see https://docs.oracle.com/cd/A57673_01/DOC/dcommon/oin/index.htm
    So the most likely, all of them existed in 8.0 as well

    Comment by Anonymous — January 25, 2021 @ 5:15 pm GMT Jan 25,2021 | Reply

    • Thanks for that. I don’t have the older versions of Oracle to check so I was relying on V$SQL_HINTS.

      For those who want to read it, you can get to the Oracle 7.3.3 Server Tuning manual Chapter 7 in a couple of clicks.
      The specific URL for the table of contents (which includes a list of hints) is: https://docs.oracle.com/cd/A57673_01/DOC/server/doc/A48506/toc.htm and it does, indeed, list:
      INDEX
      INDEX_ASC
      INDEX_COMBINE
      INDEX_DESC
      INDEX_FFS

      Regards
      Jonathan Lewis

      Comment by Jonathan Lewis — January 25, 2021 @ 5:51 pm GMT Jan 25,2021 | Reply

  2. Hi Jonathan,

    [quote]So if you think you know all about hints and indexing this blog note is for you.[/quote]

    I certainly don’t think that, but did you omit BITMAP_TREE on purpose in favor of INDEX_COMBINE?
    Just adding it here since BITMAP_TREE is what we actually get in the outline data section when we use INDEX_COMBINE.

    SQL> create table t(x int, y int);
    
    Table created.
    
    SQL> create index t_x_i on t(x);
    
    Index created.
    
    SQL> create index t_y_i on t(y);
    
    Index created.
    
    SQL>
    SQL> exec dbms_stats.gather_table_stats('', 't')
    
    PL/SQL procedure successfully completed.
    
    SQL>
    SQL> explain plan for
      2  select /*+ bitmap_tree(t and((x) (y)))*/*
      3    from t
      4   where x = :1
      5     and y = :2;
    
    Explained.
    
    SQL>
    SQL> select * from dbms_xplan.display(format=>'outline hint_report');
    
    PLAN_TABLE_OUTPUT
    ----------------------------------------------------------------------------------------------------
    Plan hash value: 1777777312
    
    -----------------------------------------------------------------------------------------
    | Id  | Operation                       | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
    -----------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                |       |     1 |    26 |     3   (0)| 00:00:01 |
    |   1 |  BITMAP CONVERSION TO ROWIDS    |       |     1 |    26 |     3   (0)| 00:00:01 |
    |   2 |   BITMAP AND                    |       |       |       |            |          |
    |   3 |    BITMAP CONVERSION FROM ROWIDS|       |       |       |            |          |
    |*  4 |     INDEX RANGE SCAN            | T_X_I |       |       |     1   (0)| 00:00:01 |
    |   5 |    BITMAP CONVERSION FROM ROWIDS|       |       |       |            |          |
    |*  6 |     INDEX RANGE SCAN            | T_Y_I |       |       |     1   (0)| 00:00:01 |
    -----------------------------------------------------------------------------------------
    
    Outline Data
    -------------
    
      /*+
          BEGIN_OUTLINE_DATA
          BITMAP_TREE(@"SEL$1" "T"@"SEL$1" AND(("T"."X") ("T"."Y")))
          OUTLINE_LEAF(@"SEL$1")
          ALL_ROWS
          DB_VERSION('19.1.0')
          OPTIMIZER_FEATURES_ENABLE('19.1.0')
          IGNORE_OPTIM_EMBEDDED_HINTS
          END_OUTLINE_DATA
      */
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       4 - access("X"=TO_NUMBER(:1))
       6 - access("Y"=TO_NUMBER(:2))
    
    Hint Report (identified by operation id / Query Block Name / Object Alias):
    Total hints for statement: 1
    ---------------------------------------------------------------------------
    
       1 -  SEL$1 / T@SEL$1
               -  bitmap_tree(t and((x) (y)))
    
    40 rows selected.
    
    SQL>
    SQL> explain plan for
      2  select /*+ index_combine(t (x) (y))*/*
      3    from t
      4   where x = :1
      5     and y = :2;
    
    Explained.
    
    SQL>
    SQL> select * from dbms_xplan.display(format=>'outline hint_report');
    
    PLAN_TABLE_OUTPUT
    ----------------------------------------------------------------------------------------------------
    Plan hash value: 1777777312
    
    -----------------------------------------------------------------------------------------
    | Id  | Operation                       | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
    -----------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                |       |     1 |    26 |     3   (0)| 00:00:01 |
    |   1 |  BITMAP CONVERSION TO ROWIDS    |       |     1 |    26 |     3   (0)| 00:00:01 |
    |   2 |   BITMAP AND                    |       |       |       |            |          |
    |   3 |    BITMAP CONVERSION FROM ROWIDS|       |       |       |            |          |
    |*  4 |     INDEX RANGE SCAN            | T_X_I |       |       |     1   (0)| 00:00:01 |
    |   5 |    BITMAP CONVERSION FROM ROWIDS|       |       |       |            |          |
    |*  6 |     INDEX RANGE SCAN            | T_Y_I |       |       |     1   (0)| 00:00:01 |
    -----------------------------------------------------------------------------------------
    
    Outline Data
    -------------
    
      /*+
          BEGIN_OUTLINE_DATA
          BITMAP_TREE(@"SEL$1" "T"@"SEL$1" AND(("T"."X") ("T"."Y")))
          OUTLINE_LEAF(@"SEL$1")
          ALL_ROWS
          DB_VERSION('19.1.0')
          OPTIMIZER_FEATURES_ENABLE('19.1.0')
          IGNORE_OPTIM_EMBEDDED_HINTS
          END_OUTLINE_DATA
      */
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       4 - access("X"=TO_NUMBER(:1))
       6 - access("Y"=TO_NUMBER(:2))
    
    Hint Report (identified by operation id / Query Block Name / Object Alias):
    Total hints for statement: 2
    ---------------------------------------------------------------------------
    
       1 -  SEL$1 / T@SEL$1
               -  index_combine(t (x) (y))
               -  index_combine(t (x) (y))
    
    41 rows selected.
    
    SQL>
    SQL> explain plan for
      2  select /*+ BITMAP_TREE(@"SEL$1" "T"@"SEL$1" OR(1 1 ("T"."X") 2 ("T"."Y")))*/*
      3    from t
      4   where x = :1
      5      or y = :2;
    
    Explained.
    
    SQL>
    SQL> select * from dbms_xplan.display(format=>'outline hint_report');
    
    PLAN_TABLE_OUTPUT
    ----------------------------------------------------------------------------------------------------
    Plan hash value: 856784385
    
    ---------------------------------------------------------------------------------------------
    | Id  | Operation                           | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                    |       |     1 |    26 |     3   (0)| 00:00:01 |
    |   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T     |     1 |    26 |     3   (0)| 00:00:01 |
    |   2 |   BITMAP CONVERSION TO ROWIDS       |       |       |       |            |          |
    |   3 |    BITMAP OR                        |       |       |       |            |          |
    |   4 |     BITMAP CONVERSION FROM ROWIDS   |       |       |       |            |          |
    |*  5 |      INDEX RANGE SCAN               | T_X_I |       |       |     1   (0)| 00:00:01 |
    |   6 |     BITMAP CONVERSION FROM ROWIDS   |       |       |       |            |          |
    |*  7 |      INDEX RANGE SCAN               | T_Y_I |       |       |     1   (0)| 00:00:01 |
    ---------------------------------------------------------------------------------------------
    
    Outline Data
    -------------
    
      /*+
          BEGIN_OUTLINE_DATA
          BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "T"@"SEL$1")
          BITMAP_TREE(@"SEL$1" "T"@"SEL$1" OR(1 1 ("T"."X") 2 ("T"."Y")))
          OUTLINE_LEAF(@"SEL$1")
          ALL_ROWS
          DB_VERSION('19.1.0')
          OPTIMIZER_FEATURES_ENABLE('19.1.0')
          IGNORE_OPTIM_EMBEDDED_HINTS
          END_OUTLINE_DATA
      */
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       5 - access("X"=TO_NUMBER(:1))
       7 - access("Y"=TO_NUMBER(:2))
    
    Hint Report (identified by operation id / Query Block Name / Object Alias):
    Total hints for statement: 1 (U - Unused (1))
    ---------------------------------------------------------------------------
    
       1 -  SEL$1 / T@SEL$1
             U -  BITMAP_TREE(@"SEL$1" "T"@"SEL$1" OR(1 1 ("T"."X") 2 ("T"."Y")))
    
    42 rows selected.
    
    SQL>
    SQL> explain plan for
      2  select /*+ index_combine(t (x) (y))*/*
      3    from t
      4   where x = :1
      5      or y = :2;
    
    Explained.
    
    SQL>
    SQL> select * from dbms_xplan.display(format=>'outline hint_report');
    
    PLAN_TABLE_OUTPUT
    ----------------------------------------------------------------------------------------------------
    Plan hash value: 856784385
    
    ---------------------------------------------------------------------------------------------
    | Id  | Operation                           | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                    |       |     1 |    26 |     3   (0)| 00:00:01 |
    |   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T     |     1 |    26 |     3   (0)| 00:00:01 |
    |   2 |   BITMAP CONVERSION TO ROWIDS       |       |       |       |            |          |
    |   3 |    BITMAP OR                        |       |       |       |            |          |
    |   4 |     BITMAP CONVERSION FROM ROWIDS   |       |       |       |            |          |
    |*  5 |      INDEX RANGE SCAN               | T_X_I |       |       |     1   (0)| 00:00:01 |
    |   6 |     BITMAP CONVERSION FROM ROWIDS   |       |       |       |            |          |
    |*  7 |      INDEX RANGE SCAN               | T_Y_I |       |       |     1   (0)| 00:00:01 |
    ---------------------------------------------------------------------------------------------
    
    Outline Data
    -------------
    
      /*+
          BEGIN_OUTLINE_DATA
          BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "T"@"SEL$1")
          BITMAP_TREE(@"SEL$1" "T"@"SEL$1" OR(1 1 ("T"."X") 2 ("T"."Y")))
          OUTLINE_LEAF(@"SEL$1")
          ALL_ROWS
          DB_VERSION('19.1.0')
          OPTIMIZER_FEATURES_ENABLE('19.1.0')
          IGNORE_OPTIM_EMBEDDED_HINTS
          END_OUTLINE_DATA
      */
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       5 - access("X"=TO_NUMBER(:1))
       7 - access("Y"=TO_NUMBER(:2))
    
    Hint Report (identified by operation id / Query Block Name / Object Alias):
    Total hints for statement: 2
    ---------------------------------------------------------------------------
    
       1 -  SEL$1 / T@SEL$1
               -  index_combine(t (x) (y))
               -  index_combine(t (x) (y))
    
    43 rows selected.
    
    

    A few observations regarding the hint reports above:
    1) INDEX_COMBINE reported twice – by the number of additional arguments passed to INDEX_COMBINE which I verified using a few separate tests;
    2) BITMAP_TREE is reported as not used in the third case with OR – that is why I tried to use the system hint (i.e. supplied the same hint as it is in the outline data) which led to the same result;
    Looks like the HINT_REPORT leaves much to desired which is not unexpected given the fact that it was relatively recently introduced.

    Regards,
    Mikhail.

    Comment by Mikhail Velikikh — January 25, 2021 @ 6:11 pm GMT Jan 25,2021 | Reply

    • Mikhail,

      Thanks for the comment.
      did you omit BITMAP_TREE on purpose in favor of INDEX_COMBINE?

      Yes and no.

      If I had remembered it I would have left it in the “saved for later” section – but I had forgotten that that was what the optimizer produced after being prompted by index_combine.

      In a way it’s very like the way the optmizer treats a basic index(alias): you supply a high-level picture, Oracle works out the details, sometimes you get surprised.

      Regards
      Jonathan Lewis

      Thanks for the examples.

      Comment by Jonathan Lewis — January 25, 2021 @ 7:08 pm GMT Jan 25,2021 | Reply

    • Mikhail,

      I made a little time to read the details of your example.

      The worrying thing about seeing a hint reported as UNUSED while the plan appears to have used it is the idea that you might capture an SQL Baseline that seems to produce a plan you want, but you won’t know whether the baseline really works or whether the plan you’re getting (for a while) is one you would have got without the baseline anyway – and maybe the plan will change one day.

      I’ve not looked into it closely enough but sometimes I have wondered whether a hint is being labelled as “unused” because it was redundant – i.e. the optimizer was going to do that anyway – but I have seen cases where it’s very obvious that the hint was obeyed but still labelled “unused”. So I agree – there’s still some way to go to complete this feature.

      Regards
      Jonathan Lewis

      Comment by Jonathan Lewis — January 26, 2021 @ 12:54 pm GMT Jan 26,2021 | Reply

  3. […] the end of the previous post on index hints I mentioned that I had been prompted to complete a draft from a few years back because I’d […]

    Pingback by Index Hints | Oracle Scratchpad — January 26, 2021 @ 4:29 pm GMT Jan 26,2021 | 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:

WordPress.com Logo

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