Re: Creating extended stats on a ASCENDING/DESCENDING index

From: Mladen Gogala <gogala.mladen_at_gmail.com>
Date: Tue, 5 Jun 2012 21:35:45 +0000 (UTC)
Message-ID: <jqlu3h$se5$1_at_solani.org>

On Tue, 05 Jun 2012 13:59:00 -0700, ddf wrote:

> 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

It looks like the extension is created automagically, along with the index:
SQL> exec dbms_stats.drop_extended_stats(user,'EMP','(SYS_OP_DESCEND (SAL))'); PL/SQL procedure successfully completed.

Elapsed: 00:00:00.31
SQL> create index test1 on emp(sal desc);

Index created.

Elapsed: 00:00:00.04
SQL> select extension_name,extension from all_stat_extensions   2 where owner=user and table_name='EMP';

EXTENSION_NAME

---

EXTENSION

---

SYS_NC00009$
("SAL") Elapsed: 00:00:00.02
SQL> exec dbms_stats.drop_extended_stats(user,'EMP','(SYS_OP_DESCEND (SAL))');
BEGIN dbms_stats.drop_extended_stats(user,'EMP','(SYS_OP_DESCEND(SAL))'); END; *
ERROR at line 1:

ORA-20000: extension "(SYS_OP_DESCEND(SAL))" is not droppable
ORA-06512: at "SYS.DBMS_STATS", line 8639
ORA-06512: at "SYS.DBMS_STATS", line 32711
ORA-06512: at line 1

Elapsed: 00:00:00.01
SQL> So, all that needs to be done is to collect stats with "FOR ALL HIDDEN COLUMSN SIZE 254" as method_opt. This is neat!

-- 
http://mgogala.byethost5.com

Received on Tue Jun 05 2012 - 16:35:45 CDT