Searches Using Indices

[HÉLIO ANDRADE]

I would like to know what command can I use in the database:?: Oracle, to force a search for a certain index, by me selected, rather than leaving the search for the Oracle Access Plan.

[dr_gori]

Use a HINT:

Select all

SELECT /*+ index (tabela índice) */ 
  CAMPO 
FROM SUA_TABELA 
WHERE...

That way, it will force the use of the index! :-O

[erthal]

Hélio,

This is a very important issue because everyone thinks that always using indexes improve performance, which is not true.

The use of indexes is required when about 20% of the table or tables used must be returned. When we will access most or a whole table, the easiest path is to make a full scan.

As Oracle stores the chosen columns, plus the rowid of the line, when Oracle sweep an index, pick up the rowid of the selected rows, it then needs to sweep the table to return the Records filtered by the WHERE clause. So if we want to bring most or the whole table, make it sweep the index and then the table, it ends up becoming more costly (costly) than sweeping the table completely.

For the use of indexes, and in search of better performances, some items are very important, such as:

1. Check that Query is well written;
2. Check that the tables are being analyzed frequently (we use the PackGE dBMS_Stat or the Analyze command);
3. If the above two steps are OK, checking as Oracle is riding the access plan. For this we can make use of PLSQL Developer, pressing the F5 key, or generating a query trace, which is much more precise than making it generate the plan.

As for HINTS You can take a basic look at this link: http://www.psoug.org/reference/hints.html
Another good query source is in http://asktom.oracle.com. this forum, maintained by Thomas Kyte, Oracle, he always mentions that when we need Hint to improve the performance of a query, two, or the model is bad, or Query is misunderstood. As it is not always possible to redo the model, we can try to rewrite Query.

I hope to have been useful.