FBI with IN (...) clause not working

From: Kevin Brand <kevin.brandx_at_tel.gte.com>
Date: Thu, 29 Aug 2002 15:12:28 -0500
Message-ID: <aklv4e$9kc$1@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 Received on Thu Aug 29 2002 - 15:12:28 CDT