Re: Descending Indexes

From: David Pattinson <dpattinson_at_enternet.com.au>
Date: Tue, 08 Feb 2000 17:00:11 +1100
Message-ID: <389FB0EB.228DC436@enternet.com.au>

I have just noticed that I have a bunch of indexes in our Oracle 8i database that were declared as having a descending column like so:

CREATE INDEX XIE2t_proc_err_log ON t_proc_err_log (

       id_app                         ASC,
       dt_tm_err                      DESC

);

Looking at all_ind_columns I see that column name for the descending column is not what I would expect:

select index_name, column_name, descend from all_ind_columns
where INDEX_name = 'XIE2T_PROC_ERR_LOG' AND TABLE_OWNER = 'TCTST';

INDEX_NAME                              COLUMN_NAME    DESCEND
XIE2T_PROC_ERR_LOG             ID_APP                      ASC
XIE2T_PROC_ERR_LOG             SYS_NC00017$        DESC

I checked the all_indexes view to find that the index was defined as 'FUNCTION-BASED NORMAL'. Looking in the documentation, this implies that the index is being created on a function of the column, the name of which is being system generated (hence SYS_NC00017$).

From this I suspect that the DESC keyword is being set up as a function based index in the same way as one can set up an index in 8i on UPPER(column_name) for example. When the optimiser, (or whichever part does this) sees ORDER BY column_name DESC in your SQL it recognises that it has an index on that function and uses the index to order the results. There must be a dictionary mapping between 'column_name DESC' and the generated function name somewhere I guess.

Regards, David.

Ritu wrote:

> In article <389EBE3E.4661_at_yahoo.com>,
> connor_mcdonald_at_yahoo.com wrote:
> > Ritu wrote:
> > >
> > > Hi
> > > I have struggled so much but couldn't find a way to create desceding
> > > indexes using Oracle8 and Delphi4. My problem is that I want to
> display
> > > dates in descending order and also at the same time give the
> facility to
> > > update them so cannot use queries and views don't allow order by
> clause.
> > >
> > > Can anybody tell help me in solving the problem
> > >
> > > Ritu
> > >
> > > Sent via Deja.com http://www.deja.com/
> > > Before you buy.
> >
> > descending index syntax is supported in all oracle versions...actual
> > descending indexes are supported from 8i upwards.
> >

>

> Yes you are right. Infact, I was trying the following syntax for
> creating indexes

>

> create index indaa on tblbb (col1, col2 desc)

>

> but didn't work. As in Oracle8's documentation for Create Index,it is
> written that
> " ASC and DESC mean asceding and Descending and are allowed for DB2
> compatibility but have no effect."

>

> You mean to say this syntax has effect in Oracle 8i?

>

> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Tue Feb 08 2000 - 00:00:11 CST