Oracle - Understanding the no_index hint

Question

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 
            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

Answer 1

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.