Re: Creating extended stats on a ASCENDING/DESCENDING index
From: ddf <oratune_at_msn.com>
Date: Tue, 5 Jun 2012 13:59:00 -0700 (PDT)
Message-ID: <58684b96-1113-48ed-b775-c28ce08664d4_at_z19g2000vbe.googlegroups.com>
On Jun 5, 2:36 pm, dombrooks <dombro..._at_hotmail.com> wrote:
> A descending index is a function-based index so... in terms of your original qustion you might want to try something like:
>
> select dbms_stats.create_extended_stats(user,'EMP','(SYS_OP_DESCEND(SAL))') from
> dual;
>
> I'd probably be more interested in why the index isn't being used.
>
> I'm sure you're familiar with Richard Foote's writings:http://richardfoote.wordpress.com/category/descending-indexes/
Date: Tue, 5 Jun 2012 13:59:00 -0700 (PDT)
Message-ID: <58684b96-1113-48ed-b775-c28ce08664d4_at_z19g2000vbe.googlegroups.com>
On Jun 5, 2:36 pm, dombrooks <dombro..._at_hotmail.com> wrote:
> A descending index is a function-based index so... in terms of your original qustion you might want to try something like:
>
> select dbms_stats.create_extended_stats(user,'EMP','(SYS_OP_DESCEND(SAL))') from
> dual;
>
> I'd probably be more interested in why the index isn't being used.
>
> I'm sure you're familiar with Richard Foote's writings:http://richardfoote.wordpress.com/category/descending-indexes/
That's not working either:
SQL> create index sal_desc_idx on emp(sal desc);
Index created.
SQL> select
dbms_stats.create_extended_stats(user,'EMP','(SYS_OP_DESCEND(SAL))')
from
2 dual;
select
dbms_stats.create_extended_stats(user,'EMP','(SYS_OP_DESCEND(SAL))')
from
*
ERROR at line 1:
ORA-20007: extension (SYS_OP_DESCEND(SAL)) already exists in the table ORA-06512: at "SYS.DBMS_STATS", line 8392 ORA-06512: at "SYS.DBMS_STATS", line 32587
SQL> David Fitzjarrell Received on Tue Jun 05 2012 - 15:59:00 CDT