Re: Descending indexes in 10g

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Thu, 25 May 2006 22:16:24 +0100
Message-ID: <286dneDxVsw6g-vZRVny2g@bt.com>

"Andy Kent" <andykent.bristol1095_at_virgin.net> wrote in message news:1148551826.902078.240170_at_j73g2000cwa.googlegroups.com...
> How significant are descending indexes in 10gr2? If you have an
> ascending index on something can it still be used for descending sorts?
>
> e.g. if you want to do
> ORDER BY a,b DESC, c
> do you need an index on
> a, b DESC, c
> or is one on
> a,b,c
> enough?
>
> If the answer is that you do need the DESC index column, how would you
> get that into a primary key?
>
> Thanks
>
> Andy Kent
>

If you want to give Oracle the option for using the index to acquire the data in order - without doing a sort - then you would need an index (a, b desc, c). But the presence of the index would not guarantee that Oracle would use the index rather than doing a simple sort.

You cannot get a descending column into a primary key (at present). 10g example:

SQL> drop table t1;

Table dropped.

SQL> create table t1 (
  2 a number, b number, c number , d varchar2(10)   3 );

Table created.

SQL>
SQL> create index i1 on t1(
  2 a, b desc, c
  3 );

Index created.

SQL> alter table t1 add constraint t1_pk primary key (a,b,c);

Table altered.

SQL> select index_name from user_indexes where table_name = 'T1';

INDEX_NAME

---

I1
T1_PK

2 rows selected.

SQL> select index_name, column_name from user_ind_columns   2 where table_name = 'T1'
  3 order by index_name, column_position;

INDEX_NAME COLUMN_NAME

-------------------- --------------------
I1                   A
I1                   SYS_NC00005$
I1                   C
T1_PK                A
T1_PK                B
T1_PK                C

6 rows selected.

Note how Oracle has had to create a new index to support the primary key constraint - if it were possible to use the index with the descending column to support the primary key constraint, Oracle would not have created index t1_pk.

-- 
Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

Received on Thu May 25 2006 - 16:16:24 CDT