Re: FBI with IN (...) clause not working

It's a bug/deficiency in 8.1.7.0 (at least) which is not present in 9.2.0.1 (at least)

alter session set query_rewrite_enabled = true;

create table t1 as
select rownum id, lpad(rownum) padding
from all_objects
where rownum <= 10000
;

create index t1_normal on t1(id);
create index t1_fbi on t1(id+1)

analyze table t1 compute statistics;

select * from t1 where id in (99,199);
select * from t1 where id + 1 in (100,200);

Two queries to return the same result set. the two sets of indexes for the indexes and their columns are identical. One does an INLIST ITERATOR, the other does a FTS.

Checking the 10053 trace, the FBI does look at the SYS_NC column for stats, and determines the correct computed cardinality, but does not follow up by checking the index.

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Next Seminars
 UK  Sept, Nov
 USA x 2  November

http://www.jlcomp.demon.co.uk/seminar.html







Richard Foote wrote in message ...


>Hi Kevin,

>

>There was a post here not too long ago when some else had problems using a

>functioned based index in an IN clause (not sure if it was ever resolved ?)

>Seems to be a little pattern happening ...

>

>Assuming that all statistics are accurate and assuming that 'John' or 'Doe'

>don't return too many rows and that the index indeed is most appropriate

>then it's certainly odd. The fact that the bitmap index kicks in suggests

>that the optimizer is not too impressed with the cardinality of the non

>bitmap functioned based index and the expected number of returned rows.

>

>Playing with the stats (such as deleting the buggers and 'blinding' the

>CBO), using the appropriate hints which are now generally not ignored and

>using a stored outline to 'save' the desired execution plan is a possible

>alternative.

>

>Cheers

>

>Richard

>

>

>"Kevin Brand" <kevin.brandx_at_tel.gte.com> wrote in message

>news:aklv4e$9kc$1_at_news.gte.com...

>>

>> OK  guys,  I'm beating myself up on this one.  Version 8.1.7.3 EE on

32bit


>> AIX

>>

>> A simple example:

>>

>> create table fn1 ( col1 varchar2(20) );

>> insert as much data as you want ( I used 2000000 rows )

>>

>> create index fn1_ix on fn1 ( rtrim(col1) );

>>

>> set appropriate session parameters:

>> alter session set optimizer_index_caching=30;

>> alter session set optimizer_index_cost_adj=60;

>> alter session set QUERY_REWRITE_ENABLED=TRUE;

>> alter session set QUERY_REWRITE_INTEGRITY=TRUSTED;

>> alter session set db_file_multiblock_read_count=8;

>>

>> Try these statements:

>> 1)select * from fn1 where rtrim(col1) in ('john');

>> 2)select * from fn1 where rtrim(col1) in ('john','doe');

>>

>> On my system, query 1) above uses the function based index while query 2)

>> does not.  This is true no matter how I setup the session parameters and

>> when using histograms too.  Even INDEX hints don't do the trick.

>>

>> Now, if I create a bitmap FBI, query 2) above runs like it should.

>>

>> On an NT/9i system, the optimizer chooses the FBI in all appropriate

>cases.

>>

>> WTF

>>

>> -Kevin

>>

>>

>>

>> --

>>

>> Remove the x from address for email

>>

>>

>

>