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

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
>
>
Received on Fri Aug 30 2002 - 01:16:34 CDT