Re: Creating extended stats on a ASCENDING/DESCENDING index

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 11.2.0.2 (-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 ?

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com/all_postings

Author: Oracle Core (Apress 2011)
http://www.apress.com/9781430239543

"Mladen Gogala" <gogala.mladen_at_gmail.com> wrote in message 
news:jqlf61$e01$1_at_solani.org...


|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?

|

|

| -- 

| http://mgogala.byethost5.com