ansaurus

Question

How to force oracle to use index range scan?

Answer 1

+4 A:

I suggest the following approach:-

Get an explain plan on the slow statement
Using an INDEX hint, get an explain plan on using the index

You'll notice that the cost of the INDEX plan is greater. This is why Oracle is not choosing the index plan. The cost is Oracle's estimate based on the statistics it has and various assumptions.

If the estimated cost of a plan is greater, but it actually runs quicker then the estimate is wrong. Your job is to figure out why the estimate is wrong and correct that. Then Oracle will choose the right plan for this statement and others on it's own.

To figure out why it's wrong, look at the number of expected rows in the plan. You will probably find one of these is an order of magnitude out. This might be due to non-uniformly distributed column values, old statistics, columns that corelate with each other etc.

To resolve this, you can get Oracle to collect better statistics and hint it with better starting assumptions. Then it will estimate accurate costs and come up with the fastest plan.

If you post more information I might be able to comment further.

WW 2010-03-18 22:09:57

Given it is a range scan, I'd wonder whether the 'slow' queries are dealing with a bigger range (eg a year instead of a week). In which case you'd expect them to be considerably slower. At some point, as the range goes large enough, it will make sense to switch from index to full scan. Whether Oracle has guessed that point correctly is a different matter.

Gary 2010-03-18 22:58:26

Furthermore, if it's doing a full index scan and your query is joining to another table, I'd guess it's not joining via a nested loop - it's probably switching to a hash or merge join, or joining the tables in a different order. If the slow queries "should" be quicker (i.e. they are not IRL querying a significant percentage of the table), you should be able to go back to range scans by hinting for nested loops (with a suitable `USE_NL` hint).

Jeffrey Kemp 2010-03-19 04:08:08

Answer 2

A:

I have seen hint is ignored by Oracle.

Recently, our DBA uses "optimizer_index_cost_adj" and it utilized the index. This is Oracle parameter but you could use it as session level.

100 is default value and we used 10 as the parameter.

exiter2000 2010-03-18 22:11:24

optimizer_index_cost_adj affects ALL calculations made by the optimizer when considering ANY indexes across the system - changing it must be done carefully, taking into account its general affect across a system.

Jeffrey Kemp 2010-03-19 04:04:36

That is my point. When it is used, it should be used by session level parameter so that it would not impact any other query.

exiter2000 2010-03-19 14:33:53

Answer 3

A:

If you want to know why the optimizer takes the decisions it does you need to use the 10053 trace.

SQL> alter session set events '10053 trace name context forever, level 1';

Then run explain plans for a sample fast query and a sample slow query. In the user dump directory you will get trace files detailing the decision trees which the CBO goes through. Somewhere in those files you will find the reasons why it chooses a full index scan over an index range scan.

I'm not saying the trace files are an easy read. The best resource for understanding them is Wolfgang Breitling's excellent whitepaper "A look under the hood of CBO" (PDF)

APC 2010-03-19 05:43:04

ansaurus

tags:

views:

answers:

How to force oracle to use index range scan?

related questions