Re: Index by Date Descending: Ignored

Michael correctly pointed out that Oracle did a FUNCTION-BASED INDEX on the index as evidenced by the system-generated "SYS_NC00079$" index column name mentioned. In this case, you need to do several things to activate the FBI and run the SQL again:

1. Activate functional index features in system: ALTER SYSTEM SET query_rewrite_enabled=true & ALTER SYSTEM SET QUERY_REWRITE_INTEGRITY = TRUSTED;
2. Run table analyzer using ANALYZE TABLE table_name COMPUTE STATISTICS;
3. Rerun the query with EXPLAIN PLAN to check for table scan.

Good luck!

In article <8s5b7b$76t$1_at_nnrp1.deja.com>,   Michael Bialik <michael_bialik_at_my-deja.com> wrote:
> 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:
> a. SORT_AREA_SIZE - bigger SAZ means cheaper sorts
> b. db_block_size and db_multiblock_io_count - bigger values mean
> that FULL table scans are going to be cheaper as well.
> c. Try ( just for testing ) to add "WHERE ROWNUM < 10" and see
> what happens.
>
> 2. 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.
>

--
Remove no "_NoSpam" to email.


Sent via Deja.com http://www.deja.com/
Before you buy.