> I have a customers-table with the two columns nrfirst and nrlast, both
> are Integer. I have two indices
> CREATE UNIQUE INDEX I_CSNR_A_U ON CUSTOMERS (NRFIRST,NRLAST);
> CREATE DESCENDING UNIQUE INDEX I_CSNR_D_U ON CUSTOMERS (NRFIRST,NRLAST);
>
> With the following SQL-Statement 'select * from customers order by
> nrfirst,nrlast', I get the plan
> PLAN (CUSTOMERS ORDER I_CSNR_A_U)
> but when I have 'select * from customers order by nrfirst,nrlast desc',
> I get the plan
> PLAN SORT ((CUSTOMERS NATURAL))
> Can anybody explain to me, why not the index I_CSNR_D_U is used ?

IB can use index for ordering only if existing index has _all_ required
fields, in right _order_, and in right _direction_ (asc,desc).
I.e. index I_CSNR_D_U can be used only for
ORDER BY NRFIRST DESC
or
ORDER BY NRFIRST DESC,NRLAST DESC
but not for
ORDER BY NRFIRST DESC,NRLAST DESC, NRMIDDLE DESC
and not for
ORDER BY NRLAST DESC, NRFIRST DESC

IB can't create index with some fields ascending and some descending,
so this clause will never use index for sorting
order by nrfirst ASC,nrlast DESC

Ivan
http://www.volny.cz/iprenosil/interbase