Re: Index by Date Descending: Ignored

Although the index is described as 'function-based' in the dictionary, this is a special case and you do not need to set the query_rewrite parameters to use it.

BTW - the 'integrity' parameter is needed only if you function-based index is based on a user-defined function rather than a built-in function.

--

Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Nhuan_at_Lieu.org wrote in message <8snnbn$l9h$1_at_nnrp1.deja.com>...


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