Re: Index by Date Descending: Ignored

Hi.

1. CBO probably "thinks" that using SORT is cheaper than going for each rows through DESC index. Here a couple of variables it is considering:
2. SORT_AREA_SIZE - bigger SAZ means cheaper sorts
3. db_block_size and db_multiblock_io_count - bigger values mean that FULL table scans are going to be cheaper as well.
4. Try ( just for testing ) to add "WHERE ROWNUM < 10" and see what happens.
5. The reason for strange field name in index is that Oracle treats DESC index as FUNCTION_BASED_INDEX, so for each FBI it creates a fields with generated name. Look for that field in ALL_TAB_COLUMNS - one of it's LONG columns containd the original field name and DESC keyword.

 HTH. Michael.

In article <8s52il$v1n$1_at_nnrp1.deja.com>,   billmil_at_my-deja.com wrote:
> 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.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Oct 12 2000 - 16:42:04 CDT