Re: Optimizer path

I see that your question hasn't been answered yet.

Ignoring other details, the answer to your question is that you've hit a corner case - and it even could be called a bug I think.

There is a clue in the information that you've provided:

• The table / index have 280K rows
• The index has only one distinct key
• The estimated number of rows for your original query is 139K (very close to the 280K rows * 0.5)

I believe you've hit a special case: Oracle 11g added the concept of "Column Groups" for the optimizer in order to address correlated column values. You can generate column groups explicitly via so called "Extended Statistics" in 11g but in the absence of such explicit column groups and histograms the optimizer will use the DISTINCT_KEYS of a matching composite index as measure for the selectivity and hence you end up with basically all rows returned by your three predicates corresponding to your index. The fourth predicate (REPROCESS_FLAG) is not covered by your composite index and very likely a Y/N field, so this results in a final selectivity of 0.5, hence 280K total rows times 0.5 gives us 140K rows, which is pretty close to the estimate.

The problem here is however, that the values you've provided seem to be "out-of-range" predicates (not really, but according to the statistics I believe), so the optimizer should have discovered that and was supposed to give you basically a "no match" selectivity / cardinality estimate. I think this is a buggy behaviour in the special case of only one distinct value in the column statistics for all three columns, which is not unlikely given the shown value distribution (depending on how statistics are gathered on that table).

When you added the UPPER() functions to your WHERE clause the optimizer lost track of the matching column group and therefore reverted to the individual column statistics and recognized that the values are "out-of-range", hence the dramatic difference in the estimate of only a single row.

Basically you arrived at a better execution plan by accident: The optimizer estimated a cardinality of a single row, hence the index access path was favoured.

The original estimate based on the composite index DISTINCT_KEYS (without the functions) was 139K rows, hence the full table scan was selected.

If I understand your query correctly, then you're basically looking for a few rows at most that are in status "Running". So you need to ask yourself why the optimizer estimated 139K rows based on the statistics when you expected only a couple of rows.

By generating a histogram you again break the index column group match and get an estimate based on the individual column statistics rather than based on the DISTINCT_KEYS of the index avoiding the buggy behaviour again.

There is a lot more to say for example about histograms, expressions like UPPER(), statistics gathering etc. but in your case I would think about (at least) three things: - Use Virtual Columns / Function-Based Indexes that capture only the rare values that you're interested in and set the remaining values to NULL. This gives you a very small index and very precise cardinality estimates. => But you would need to change the application to use the expression of the virtual column / FBI

• If that is not feasible make sure that your column and index statistics reflect the state where more than one distinct value is available in the columns and use a real column group on the three columns with a histogram on the column group => There is a problem with low sample sizes for the histograms when using AUTO_SAMPLE_SIZE in 11g which might again render the histogram useless

If there is more than one distinct value in the statistics the "out-of-range" problem/bug doesn't show up

• Evaluate index compression on the existing indexes (which doesn't address your optimizer problem)

Hope this helps,
Randolf

Blogging at: http://oracle-randolf.blogspot.com

> When the statement is run with functions in the WHERE clause,
> the optimizer decided to use the leading column of the index to fetch
> rows, even though the selectivity of that column was bad. What made the
> optimizer go that route and why it did not choose it with the first run?

--
http://www.freelists.org/webpage/oracle-l