Re: Index by Date Descending: Ignored

From: <billmil_at_my-deja.com>
Date: Fri, 13 Oct 2000 17:56:24 GMT
Message-ID: <8s7ic5$1a$1@nnrp1.deja.com>

Thanks for the reply, Dave.

> It is good practice to put indexes in a different tablespace
> (on a different physical disk) to the table they apply to.
We actually do have our indexes and tables in separate tablespaces (LIN_IDX and LIN_TAB). I left them out of the example to reduce clutter. I should have made that clear.

> 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.

This is a very good point in light of the fact the Oracle treats a descending index like a function-based index. An article on Tom Kyte's site http://govt.oracle.com/~tkyte/article1/index.html discusses how you need to set the two values either on the session or system level:

QUERY_REWRITE_INTEGRITY=TRUSTED
QUERY_REWRITE_ENABLED=TRUE Only then will the CBO use the function-based index.

Alas, I set these values and the CBO still performs a full-table scan.

bill

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Oct 13 2000 - 12:56:24 CDT