Oracle - Understanding the no_index hint

I'm trying to understand how no_index actually speeds up a query and haven't been able to find documentation online to explain it.

For example I have this query that ran extremely slow

select  * 
    from    <tablename>
    where   field1_ like '%someGenericString%' and 
            field1_ <> 'someSpecificString' and
            Action_='_someAction_' and 
      开发者_JAVA技巧      Timestamp_ >= trunc(sysdate - 2)

And one of our DBAs was able to speed it up significantly by doing this

select  /*+ NO_INDEX(TAB_000000000019) */ * 
    from    <tablename>
    where   field1_ like '%someGenericString%' and 
            field1_ <> 'someSpecificString' and
            Action_='_someAction_' and 
            Timestamp_ >= trunc(sysdate - 2) 

And I can't figure out why? I would like to figure out why this works so I can see if I can apply it to another query (this one a join) to speed it up because it's taking even longer to run.

Thanks!

** Update ** Here's what I know about the table in the example.

• It's a 'partitioned table'
• TAB_000000000019 is the table not a column in it
• field1 is indexed

Oracle's optimizer makes judgements on how best to run a query, and to do this it uses a large number of statistics gathered about the tables and indexes. Based on these stats, it decides whether or not to use an index, or to just do a table scan, for example.

Critically, these stats are not automatically up-to-date, because they can be very expensive to gather. In cases where the stats are not up to date, the optimizer can make the "wrong" decision, and perhaps use an index when it would actually be faster to do a table scan.

If this is known by the DBA/developer, they can give hints (which is what NO_INDEX is) to the optimizer, telling it not to use a given index because it's known to slow things down, often due to out-of-date stats.

In your example, TAB_000000000019 will refer to an index or a table (I'm guessing an index, since it looks like an auto-generated name).

It's a bit of a black art, to be honest, but that's the gist of it, as I understand things.

Disclaimer: I'm not a DBA, but I've dabbled in that area.

Per your update: If field1 is the only indexed field, then the original query was likely doing a fast full scan on that index (i.e. reading through every entry in the index and checking against the filter conditions on field1), then using those results to find the rows in the table and filter on the other conditions. The conditions on field1 are such that an index unique scan or range scan (i.e. looking up specific values or ranges of values in the index) would not be possible.

Likely the optimizer chose this path because there are two filter predicates on field1. The optimizer would calculate estimated selectivity for each of these and then multiply them to determine their combined selectivity. But in many cases this will significantly underestimate the number of rows that will match the condition.

The NO_INDEX hint eliminates this option from the optimizer's consideration, so it essentially goes with the plan it thinks is next best -- possibly in this case using partition elimination based on one of the other filter conditions in the query.

Using an index degrades query performance if it results in more disk IO compared to querying the table with an index.

This can be demonstrated with a simple table:

create table tq84_ix_test (
  a number(15) primary key,
  b varchar2(20),
  c number(1)
);

The following block fills 1 Million records into this table. Every 250th record is filled with a rare value in column b while all the others are filled with frequent value:

declare
  rows_inserted number := 0;
begin

  while rows_inserted < 1000000  loop

        if mod(rows_inserted, 250) = 0 then

           insert into tq84_ix_test values (
               -1 * rows_inserted, 
               'rare value',
                1);

            rows_inserted := rows_inserted + 1;

        else

           begin
              insert into tq84_ix_test values (
                 trunc(dbms_random.value(1, 1e15)),
                'frequent value',
                 trunc(dbms_random.value(0,2))
               );
               rows_inserted := rows_inserted + 1;

           exception when dup_val_on_index then 
               null;
           end;

        end if;

  end   loop;

end;
/

An index is put on the column

create index tq84_index on tq84_ix_test (b);

The same query, but once with index and once without index, differ in performance. Check it out for yourself:

set timing on


select /*+ no_index(tq84_ix_test) */
    sum(c)
  from 
    tq84_ix_test
  where
    b = 'frequent value';


select /*+ index(tq84_ix_test tq84_index) */
    sum(c)    
  from 
    tq84_ix_test
  where
    b = 'frequent value';

Why is it? In the case without the index, all database blocks are read, in sequential order. Usually, this is costly and therefore considered bad. In normal situation, with an index, such a "full table scan" can be reduced to reading say 2 to 5 index database blocks plus reading the one database block that contains the record that the index points to. With the example here, it is different altogether: the entire index is read and for (almost) each entry in the index, a database block is read, too. So, not only is the entire table read, but also the index. Note, that this behaviour would differ if c were also in the index because in that case Oracle could choose to get the value of c from the index instead of going the detour to the table.

So, to generalize the issue: if the index does not pick few records then it might be beneficial to not use it.

Something to note about indexes is that they are precomputed values based on the row order and the data in the field. In this specific case you say that field1 is indexed and you are using it in the query as follows:

    where   field1_ like '%someGenericString%' and 
            field1_ <> 'someSpecificString'

In the query snippet above the filter is on both a variable piece of data since the percent (%) character cradles the string and then on another specific string. This means that the default Oracle optimization that doesn't use an optimizer hint will try to find the string inside the indexed field first and also find if the data it is a sub-string of the data in the field, then it will check that the data doesn't match another specific string. After the index is checked the other columns are then checked. This is a very slow process if repeated.

The NO_INDEX hint proposed by the DBA removes the optimizer's preference to use an index and will likely allow the optimizer to choose the faster comparisons first and not necessarily force index comparison first and then compare other columns.

The following is slow because it compares the string and its sub-strings:

            field1_ like '%someGenericString%'

While the following is faster because it is specific:

            field1_ like 'someSpecificString'

So the reason to use the NO_INDEX hint is if you have comparisons on the index that slow things down. If the index field is compared against more specific data then the index comparison is usually faster.

I say usually because when the indexed field contains more redundant data like in the example @Atish mentions above, it will have to go through a long list of comparison negatives before a positive comparison is returned. Hints produce varying results because both the database design and the data in the tables affect how fast a query performs. So in order to apply hints you need to know if the individual comparisons you hint to the optimizer will be faster on your data set. There are no shortcuts in this process. Applying hints should happen after proper SQL queries have been written because hints should be based on the real data.

Check out this hints reference: http://docs.oracle.com/cd/B19306_01/server.102/b14211/hintsref.htm

To add to what Rene' and Dave have said, this is what I have actually observed in a production situation:

If the condition(s) on the indexed field returns too many matches, Oracle is better off doing a Full Table Scan.

We had a report program querying a very large indexed table - the index was on a region code and the query specified the exact region code, so Oracle CBO uses the index.

Unfortunately, one specific region code accounted for 90% of the tables entries.

As long as the report was run for one of the other (minor) region codes, it completed in less than 30 minutes, but for the major region code it took many hours.

Adding a hint to the SQL to force a full table scan solved the problem.

Hope this helps.

I had read somewhere that using a % in front of query like '%someGenericString%' will lead to Oracle ignoring the INDEX on that field. Maybe that explains why the query is running slow.