Re: Creating extended stats on a ASCENDING/DESCENDING index

From: Jonathan Lewis <>
Date: Tue, 5 Jun 2012 22:39:55 +0100
Message-ID: <>

Is the index really a single column index, or is this just an indication of the type of thing you're trying to do ?

If it's a single column index with the column declared as descending then it's a mistake. Any time you defined EVERY column in an index to be descending it's a mistake, the single column is just a special case - Oracle can use a (non-descending) B-tree index equally well in ascending or descending order.

If Oracle isn't using the index the way you expect, then there may be a problem with the statistics, or you may have hit one of the limitations of "descending indexes" (I prefer to describe them as indexes with descending columns) - for example the optimizer can't use function-based indexes with the CONCATENATION operator until (-ish).

Did you collect stats on the hidden column that would be supporting the index after creating the index ? If all else fails call dbms_stats with "method_opt => 'for all hidden columns'"

What's the execution plan with and without the hint ?


Jonathan Lewis

Author: Oracle Core (Apress 2011)

"Mladen Gogala" <> wrote in message 

|I have an index which is created with "DESC" option and is not being used
| unless a hint is used. I am trying to create an extended statistics but I
| cannot get the syntax right:
| select dbms_stats.create_extended_stats(user,'EMP','("SAL" DESC)') from
| dual;
| select dbms_stats.create_extended_stats(user,'EMP','("SAL" DESC)') from
| dual
| *
| ERROR at line 1:
| ORA-20001: Error when processing extension - missing right parenthesis
| Has anybody done it?
| --
Received on Tue Jun 05 2012 - 16:39:55 CDT

Original text of this message