At 02:40 AM 11/2/2001 +0100, guido.klapperich@... wrote:

>CREATE UNIQUE INDEX I_CSNR_A_U ON CUSTOMERS (NRFIRST,NRLAST);
>CREATE DESCENDING UNIQUE INDEX I_CSNR_D_U ON CUSTOMERS (NRFIRST,NRLAST);
>
> 'select * from customers order by nrfirst,nrlast'
>PLAN (CUSTOMERS ORDER I_CSNR_A_U)
>
>'select * from customers order by nrfirst,nrlast desc',
>PLAN SORT ((CUSTOMERS NATURAL))

Because you've asked for the customers in ascending order
of first name and descending order of last. Indexes are
either ascending or descending on all segments of the key.

Sorts can specify some key fields ascending and others
descending and the default is ascending. The descending qualifier
is not "sticky" so to get the result you want you need a select
like this:

> 'select * from customers order by nrfirst desc, nrlast desc',

As an aside, you might try some timing tests. My guess is that
the PLAN SORT ((CUSTOMERS NATURAL)) is faster than using the
index. It's somewhat data dependent, but often what you gain
by avoiding the sort you lose by seeking all over the disk to
get the records in the right order.



Regards,

Ann
www.ibphoenix.com
We have answers.