A quick trick to ensure Oracle uses the indexes in oracle - with the Optimizer FIRST_ROWS hint


Reference to the article from Burleson Consulting


Sometimes the optimizer will just not use an index even if exists . There could be many reasons to that.


Answer:  Oracle SQL not using an index is a common complaint, and it’s often because the optimizer thinks that a full-scan is cheaper than index access.  Oracle not using an index can be due to:
·         Bad/incomplete statistics – Make sure to re-analyze the table and index with dbms_stats to ensure that the optimizer has good metadata.
·         Wrong optimizer_mode – The first_rows optimizer mode is to minimize response time, and it is more likely to use an index than the default all_rows mode.
·         Bugs – See these important notes on optimizer changes in 10g that cause Oracle not to use an index.
·         Cost adjustment – In some cases, the optimizer will still not use an index, and you must decrease optimizer_index_cost_adj.

How to make Oracle use the First_rows hint  can be looked from the following article
You can find the examples of using the First_Rows Hint at the below link



This paper focuses on how optimizer behaves differently when you have optimizer mode set to ALL_ROWS or FIRST_ROWS.
Possible values for optimizer_mode = choose/ all_rows/ first_rows/ first_rows[n]
By default, the value of optimizer_mode is CHOOSE which basically means ALL_ROWS (if statistics on underlying tables exist) else RULE (if there are no statistics on underlying tables). So it is very important to have statistics collected on your tables on regular intervals or else you are living in Stone Age.
FIRST_ROWS and ALL_ROWS are both cost based optimizer features. You may use them according to their requirement.

FIRST_ROWS/ FIRST_ROWS[n]
In simple terms it ensures best response time of first few rows (n rows).
This mode is good for interactive client-server environment where server serves first few rows and by the time user scroll down for more rows, it fetches other. So user feels that he has been served the data he requested, but in reality the request is still pending and query is still fetching the data in background.
Best example for this is toad, if you click on data tab, it instantaneously start showing you data and you feel toad is faster than sqlplus, but the fact is if you scroll down, you will see the query is still running.


Important facts about FIRST_ROWS
  1. It gives preference to Index scan Vs Full scan (even when index scan is not good).
  2. It prefers nested loop over hash joins because nested loop returns data as selected (& compared), but hash join hashes one first input in hash table which takes time.
  3. Cost of the query is not the only criteria for choosing the execution plan. It chooses plan which helps in fetching first rows fast.
  4. It may be a good option to use this in an OLTP environment where user wants to see data as early as possible.
ALL_ROWS
In simple terms, it means better throughput
While FIRST_ROWS may be good in returning first few rows, ALL_ROWS ensures the optimum resource consumption and throughput of the query. In other words, ALL_ROWS is better to retrieve the last row first.
In above example while explaining FIRST_ROWS, you have already seen how efficient ALL_ROWS is.

Important facts about ALL_ROWS
  1. ALL_ROWS considers both index scan and full scan and based on their contribution to the overall query, it uses them. If Selectivity of a column is low, optimizer may use index to fetch the data (for example ‘where employee_code=7712’), but if selectivity of column is quite high ('where deptno=10'), optimizer may consider doing Full table scan. With ALL_ROWS, optimizer has more freedom to its job at its best.
  2. Good for OLAP system, where work happens in batches/procedures. (While some of the report may still use FIRST_ROWS depending upon the anxiety level of report reviewers)
  3. Likes hash joins over nested loop for larger data sets.

Conclusion
Cost based optimizer gives you flexibility to choose response time or throughput. So use them based on your business requirement.

Comments

Popular posts from this blog

Breakdown of Heirarchial queries and building these using different techniques

External Tables in Oracle 10g/11g Database: Part 1

How to Create and Manage Views in Oracle