Creating extended stats on a ASCENDING/DESCENDING index

Mladen Gogala

unread,

Jun 5, 2012, 1:21:05 PM6/5/12

to

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

dombrooks

unread,

Jun 5, 2012, 4:36:31 PM6/5/12

to

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/

joel garry

unread,

Jun 5, 2012, 4:43:31 PM6/5/12

to

I dunno, but my first reaction is it expects either a comma, a closing
parens or an operator after the "SAL". The DESC would be an index
DDL, and maybe there is an FBI issue there, too.

jg
--
@home.com is bogus.
"If you think about what the optimizer does to calculate the cost of a
query, you will realise that one of its steps is to work out the
amount of data to be accessed and sorted. Oracle Corp. has been
working on this for many years and still hasn't got it right. " -
Jonathan Lewis

joel garry

unread,

Jun 5, 2012, 4:49:48 PM6/5/12

to

(seeing this after I posted) Ah, that gets it. The docs say you can't
have a virtual column, and Richard says there is a hidden virtual
column as a consequence of the descending FBI.

jg
--
@home.com is bogus.

http://www.utsandiego.com/news/2012/jun/04/mortgage-fraud/

ddf

unread,

Jun 5, 2012, 4:56:07 PM6/5/12

to

I haven't and I have also tried -- apparently extended statistics do
not apply to descending column order, only to column groups or
expressions involving two or more columns.

If anyone else can generate such extended statistics please show us
how it's done.

David Fitzjarrell

ddf

unread,

Jun 5, 2012, 4:59:00 PM6/5/12

to

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

Mladen Gogala

unread,

Jun 5, 2012, 5:35:45 PM6/5/12

to

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

Mladen Gogala

unread,

Jun 5, 2012, 5:36:56 PM6/5/12

to

On Tue, 05 Jun 2012 13:36:31 -0700, dombrooks wrote:

> I'd probably be more interested in why the index isn't being used.

Probably no histogram and default selectivity.

--
http://mgogala.byethost5.com

Jonathan Lewis

unread,

Jun 5, 2012, 5:39:55 PM6/5/12

to

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...@gmail.com> wrote in message
news:jqlf61$e01$1...@solani.org...

Mladen Gogala

unread,

Jun 5, 2012, 10:21:42 PM6/5/12

to

On Tue, 05 Jun 2012 21:35:45 +0000, Mladen Gogala wrote:

> It looks like the extension is created automagically, along with the
> index:

This applies to any function based index:

SQL> select table_name,extension_name,droppable from user_stat_extensions;

no rows selected

Elapsed: 00:00:00.00
SQL> create index test1 on emp(lower(ename));

Index created.

Elapsed: 00:00:00.27
SQL> select table_name,extension_name,droppable from user_stat_extensions;

TABLE_NAME EXTENSION_NAME DRO
------------------------------ ------------------------------ ---
EMP SYS_NC00009$ NO

Elapsed: 00:00:00.01
SQL> drop index test1;

Index dropped.
Elapsed: 00:00:01.12
SQL> select table_name,extension_name,droppable from user_stat_extensions;

no rows selected

Elapsed: 00:00:00.00

So, with a function based index the user will get a free stats extension.
Cute, but would be even cuter if that was documented.

--
http://mgogala.byethost5.com

Mladen Gogala

unread,

Jun 6, 2012, 3:21:19 PM6/6/12

to

On Tue, 05 Jun 2012 22:39:55 +0100, Jonathan Lewis wrote:

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

I am just trying to answer the question asked by developers.

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

As usual, you're spot on.

--
http://mgogala.byethost5.com