create index on emp ( job, sal DESC, ename );

That will sort the data by job from low to high (A-Z) and salary HIGH to low and within the same salaries by ename.

That would show you the highest paid people FIRST by job for example.

We already do this. We read indexes backwards and forwards all of the time.

This happens naturally all of the time.

the discussion above points out that just reading the index backwards and forwards is not the same as using ASC/DESC on each column -- when you mix ASC and DESC up (when some columns are ASC and some are DESC)


so no, they are not equivalent.

you are using the brain dead RBO which does not see the DESC and if you flipped to the CBO, you have an empty table....

but the CBO would see it when it makes sense to see it.



ops$tkyte@ORA9IR2> select * from testtable order by col2 desc, col3;
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   SORT (ORDER BY)
   2    1     TABLE ACCESS (FULL) OF 'TESTTABLE'
 
 
 
ops$tkyte@ORA9IR2> select /*+ FIRST_ROWS */ * from testtable order by col2 desc, col3;
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=826 Card=82 Bytes=4674)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TESTTABLE' (Cost=826 Card=82 Bytes=4674)
   2    1     INDEX (FULL SCAN) OF 'IND2' (NON-UNIQUE) (Cost=26 Card=82)
 

but that doesn't do what you said above.

"Consider my key value is populated by decrementing sequence"

the decision for asc/desc is made based on HOW YOU USE the index more than anything else.

In the special case of a single column index, it is ok to use this concept, but in general, you would not take an index on (a,b,c) and decide to make b DESC due to this -- you would have to see how you use the index.

If you take a snippet of code such as:


void show_leaf( char * msg )
{
exec sql begin declare section;
varchar name[50];
int value;
exec sql end declare section;

exec sql whenever sqlerror do die();
printf( "%s\n", msg );
exec sql declare c cursor for
select a.name, b.value
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and lower(a.name) like 'leaf%';


exec sql open c;
exec sql whenever notfound do break;
for( ;; )
{
exec sql fetch c into :name, :value;
printf( "%30.*s %d\n", name.len, name.arr, value );
}
exec sql whenever notfound continue;
exec sql close c;
}

void process(void)
{
exec sql begin declare section;
int i;
exec sql end declare section;

exec sql whenever sqlerror continue;
exec sql drop table t1;
exec sql drop table t2;

exec sql whenever sqlerror do die();
exec sql create table t1 ( x int );
exec sql create index t1_idx on t1(x);

exec sql create table t2 ( x int );
exec sql create index t2_idx on t2(x desc);

show_leaf( "before ascending insert" );
for( i = 0; i < 100000; i++ )
{
exec sql insert into t1(x) values (:i);
}
show_leaf( "after ascending, before descending" );
for( i = 100000; i > -1; i-- )
{
exec sql insert into t2(x) values (:i);
}
show_leaf( "after descending" );
}


you can see the 90/10 splits happening for both indexes:


[tkyte@xtkyte-pc t]$ ./t
connecting
connected
before ascending insert
leaf node splits 0
leaf node 90-10 splits 0
after ascending, before descending
leaf node splits 198
leaf node 90-10 splits 198
after descending
leaf node splits 409
leaf node 90-10 splits 409
[tkyte@xtkyte-pc t]$


if you remove the DESC from the second create index, it would look more like this:

[tkyte@xtkyte-pc t]$ ./t
connecting
connected
before ascending insert
leaf node splits 0
leaf node 90-10 splits 0
after ascending, before descending
leaf node splits 198
leaf node 90-10 splits 198
after descending
leaf node splits 600
leaf node 90-10 splits 198


but again, this would not be my only deciding factor.

yes...

select * from t where .... order by X desc, Y asc, Z desc;

maybe I want an index on (x DESC,y ASC, z DESC) for that.

what does a doubly linked list have to do with it?

If you need to retrieve the data sorted by

X desc
Y asc
Z desc

and the index is on (x desc, y asc, z desc)

then the plan could use the index to read the data sorted in that order.

ops$tkyte@ORA10GR1> select t1.id, t2.id
  2    from ( select id, row_number() over (order by id) rn from a ) t1,
  3         ( select id, row_number() over (order by id desc) rn from a ) t2
  4   where t1.rn = t2.rn;
 
        ID         ID
---------- ----------
         1          4
         2          3
         3          2
         4          1
 

Not going to be great with tons of data obviously. 

if you are indexing a field that is always "growing" (eg: a sequence populated column, a date populated with sysdate for example), then the index only grows on the "right hand side"

when we recognize that - that the index is "righ handed", only increasing values are being added - when the right hand block fills up - we split it 90/10 - 90% of the data goes "left" and 10% goes "right".

That way, the index is about 90% full all of the time, nicely packed (assuming just inserts in this trivial case)


If we did a 60/40 (consider it a 50/50 split for ease of thinking) split during this operation - the index would be about 60% used instead.

if you do not insert "always growing" values - the index will do a normal split putting about half of the data to the left and the right - because data is inserted all over the place - not in order, this makes sense.

there is no assurance that this data is sorted by anything.

unless there is an order by, I was just doing the "join"

no, it does not seem very unlikely...

it is purely by accident.

you want data sorted, use order by on the query. the order by on the analytics are "accidental", incidental, non-assured side effects.

(you see the sorts are in different orders? one asc, one desc....