index DESC

[sonaliak]

1.In what situation one would use index and the sort order, like

Create unique index ix_matrixD on test (Mat_Work_ID, Mat_Res_ID, Mat_Role_ID,
Mat_Category, Mat_Date DESC);

Is this only for version 8.1.6 ? What are the advantages/ disadvantages of using it ?


2. In init.ora file for Oracle 8.1.7 server we have set up compatibility option to 8.1.0, we have a customer who has set that up to 8.0.0. Whats the difference ?


Thanks
Sonali

[gpsingh]

1)

U need not specify the keyword DESC in your case , bacause before 8.1.x also , Oracle
could sort the index in the descending order even though the index was created in ASC.

What is new in 8i is if for example u need to sort in this order


select name,empno from emp
order by name ASC,empno DESC

and if u have created a index on name and empno with syntax similar to pre 8i
then Oracle would not be able to use index properly.

So in 8i it is now possible to create index

create index .... (name asc,empno desc);

and for the above query oracle shall first sort by name in ascending and then
by empno in descending order.

Hope it is clear to u.

2)

If compatible is < 8.1.0 for 8i database then u shall not be able to use 8i features like
a) Function based indexes
b) Transportable tablespaces etc...

thanks
GP

[oddster]

FYI :

Function based indexes are only available for 8i Enterprise Edition.

In 9i this feature is available for Standard also!

[sonaliak]

So whats the default sort order for index, is it ASC ?
Why did you say in this case I do not need one, I am confused ? We are using 8.1.7 here.

Create unique index ix_matrixD on test (Mat_Work_ID, Mat_Res_ID, Mat_Role_ID,
Mat_Category, Mat_Date DESC);

Wouldn't Mat_Work_ID, Mat_Res_ID, Mat_Role_ID,
Mat_Category these columns be in ASC order by default and the Mat_Date in DESC in the above example ?

Why would one want to sort one column in ASC and other in DESC in an index, this has really puzzled me.

Note - We are using 8.1.7. This is one of our customer who created this index with DESC.

Thanks again
Sonali

[gpsingh]

I did not see it properly , yes in your case all other columns except last one is in desc and yes
since u r on 8.1.7 a index shall be created with all columns sorted in ascending order
and the last in descending order.

Why would one want to sort one column in ASC and other in DESC in an index, this has really puzzled me.?????

IT may be an application requirement , u may want to sort employees ina dept alphabatically and
then for each alphabet sort by decreasing salary .

NOTE DESC clause shall be ignored if compatible parameter is < 8.1.0 because Oracle internally
use functioned base index to imply that.