Re: Index by Date Descending: Ignored

Hi Bill,

No definitive answer but some thoughts at least. Here's an example of a b*-tree index from metalink.oracle.com:

CREATE UNIQUE INDEX cblakey.pk_empno
ON cblakey.emp (empno)
PCTFREE 10
INITRANS 2 MAXTRANS 255
TABLESPACE USERS STORAGE INITIAL 51200 NEXT 51200
MINEXTENTS 1 MAXEXTENTS 121 PCTINCREASE 0) /

It is good practice to put indexes in a different tablespace (on a different physical disk) to the table they apply to. This improves performance as Oracle can read the index + table at the same time. The fact that you omit the 'tablespace' clause in your CREATE statement means the index is placed in the creating user's default tablespace.

I am wondering if the SELECT statement has permission to access the index or knows that it is available. I've just looked through the 2 sets of books we have here and this isn't mentioned in either so I'm guessing on this one.

Best of luck,

    Dave W.

<billmil_at_my-deja.com> wrote in message news:8s52il$v1n$1_at_nnrp1.deja.com...
> This seems like a basic problem, but a search of the newsgroup yielded
> no similar answer.
>
> I have an index on a "users" table by a date field
> (column "record_create_date") in descending order. We're running
> 8.1.5.0.2
>
> They query "SELECT * FROM USERS ORDER BY RECORD_CREATE_DATE DESC"
> *never* uses this index.
>
> Does it have anything to do with he fact that I create the command
> using:
> CREATE INDEX USER_CREATE_DATE_I ON
> USERS(record_create_date DESC) ;
>
> But if I look at the script for the index (via the Toad schema browser)
> I see:
> CREATE INDEX USER_CREATE_DATE_I ON
> USERS(SYS_NC00079$);
>
> Any suggestions? What obvious point am I missing?
>
>
> thanks,
>
> bill milbratz
>
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Fri Oct 13 2000 - 09:39:01 CDT