Oracle - Understanding the hint no_index

Question

I am trying to understand how no_index speeds up a query and cannot find documentation online to explain this.

For example, I have this query that was executing very slowly

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

And one of our database administrators was able to significantly speed it up 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 understand why? I would like to find out why this works, so I can check if I can apply it to another request (this join) to speed it up, because it takes even longer.

Thanks!

---

** Update ** This is what I know about the table in the example.

• This is a "partitioned table"
• TAB_000000000019 is a table in which there is no column
• field1 is indexed

Answer 1

The Oracle optimizer makes judgments about how best to run the query, and for this it uses a large amount of statistics collected about tables and indexes. Based on these statistics, he decides whether to use an index or just perform a table scan, for example.

Critically, these statistics are not automatically updated because they can be very expensive to collect. In cases where statistics are not updated, the optimizer may make the “wrong” decision and possibly use the index when it would actually be faster to scan the table.

If the DBA developer / developer knows this, they can give hints (which is what NO_INDEX ) for the optimizer, telling him not to use this index, because it is known to slow down, often due to a lack of files.

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

This is a little black art, to be honest, but this is the essence of it, as I understand things.

Disclaimer: I am not a database administrator, but I have dealt with this area.

Answer 2

For your update: if field1 is the only indexed field, then the original query most likely performs a quick full scan of this index (i.e., reading each record in the index and checking for filter conditions in field1), and then using these results to find rows in a table and filter on other conditions. The conditions in field 1 are such that a unique index scan or range scan (i.e., searching for specific values ​​or ranges of values ​​in the index) would be impossible.

The optimizer probably chose this path, because there are two filter predicates in field 1. The optimizer will calculate the estimated selectivity for each of them, and then multiply them to determine their combined selectivity. But in many cases, this greatly underestimates the number of lines that will match the condition.

The NO_INDEX hint excludes this option from the optimizer’s considerations, so it essentially comes with a plan that, in his opinion, is best — perhaps in this case, using the removal of partitions based on one of the other filter conditions in the query.

Answer 3

Using an index degrades query performance if it leads to an increase in the number of disk I / O compared to querying a 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 next block fills 1 million records in this table. Each 250th record is filled with the rare value symbol in column b, and all the rest 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; / 

Index is placed in a column

 create index tq84_index on tq84_ix_test (b); 

The same query, but once with an index and once without an index, is different 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? In the case without an index, all database blocks are read in sequential order. This is usually expensive and therefore considered bad. In the usual situation with the index, such a “full table scan” can be reduced to reading, say, from 2 to 5 blocks of the index database, as well as reading one database block containing the record pointed to by the pointer. In this example, this is completely different: the entire index is read, and for (almost) every record in the index, a database block is also read. Thus, not only the entire table is read, but also the index. Note that this behavior will be different if c also in the index, because in this case, Oracle can select the value of c from the index instead of going through the table.

So, to summarize the problem: if the index does not select multiple records, it might be useful not to use it.

Answer 4

Something a note about indexes is that they are pre-computed values ​​based on the order of the rows and data in the field. In this particular case, you say that field1 is indexed, and you use it in the query as follows:

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

In the query fragment above the filter, there is a variable part of the data, since the percent symbol (%) binds a string, and then another string. This means that the default optimization, Oracle, which does not use the hint of the optimizer, will first try to find the row inside the indexed field, and also find if the data is a substring of data in the field, then it will check that the data does not match another specific row. After checking the index, other columns are checked. This is a very slow process if repeated.

The NO_INDEX hint suggested by the database administrator removes the optimizer’s preference for using the index and is likely to allow the optimizer to first select earlier comparisons and not necessarily force the indexes to be compared first, and then compare other columns.

The following is slow because it compares a string and its substrings:

  field1_ like '%someGenericString%' 

While the following is faster because it is specific:

  field1_ like 'someSpecificString' 

Thus, the reason for using the NO_INDEX hint is a comparison with an index that slows down performance. If the index field is compared with more specific data, index comparison is usually faster.

I usually say because when an indexed field contains more redundant data, for example, in the @Atish example mentioned above, it will have to go through a long list of negative comparison results before a positive comparison is returned. Hints produce different results, because both the design of the database and the data in the tables affect the speed of the query. Therefore, to apply the hints, you need to know whether the individual comparisons that you hint at the optimizer will be faster in your dataset. There are no shortcuts in this process. The use of hints should occur after the correct SQL queries have been written, because the hints must be based on real data.

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

Answer 5

To add to the words of Rene and Dave, this is what I really observed in a production situation:

If the condition (s) in the indexed field returns too many matches, Oracle is better off scanning the full table.

We had a reporting program requesting a very large indexed table - the index was on a regional code, and the request specified the exact region code, so Oracle CBO uses the index.

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

While the report was run for one of the other (secondary) regional codes, it completed in less than 30 minutes, but it took many hours for the main region code.

Adding a hint of SQL to force a full table scan resolved the issue.

Hope this helps.

Answer 6

I read somewhere that using% before a query like% someGenericString% will cause Oracle to ignore INDEX in this field. Perhaps this explains why the query is slow.