Re: DESC clause in INDEXes

In article <aecj3d$9lv$1_at_wanadoo.fr>, "philou" says...
>
>Who could tell me what are the consequences of a DESCending clause in an
>index (Oracle 8i)?
>ex : CREATE INDEX I_CUSTOMER ON CUSTOMER (CUSTOMER_ID DESC)
>
>It does NOT change the default sort order of ORDER BY clauses, whereas one
>should have thought it would!
>
>
>

it creates a desc index (which is what I though it would do). It is most useful in concatenated indexes:

create index i on t(a, b desc, c)

that'll create an index on A ASCENDING (1,2,3,4,...), B DESCENDING (100,99,98,...) and C ASCENDING.

So, if you "select a,b,c from t where a > 5 and b < 10 and c > 20 order by a, b desc, c

it could potentially be very useful.

Nothing changes the "default" order by -- you have to tell us ASC or DESC (asc being the default).

Consider this paradox if it did:

scott_at_ORA817DEV.US.ORACLE.COM> create index i on emp(ename);
scott_at_ORA817DEV.US.ORACLE.COM> create index i2 on emp(ename desc);
scott_at_ORA817DEV.US.ORACLE.COM> 

what now? ascending or descending???

--
Thomas Kyte (tkyte@oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp