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

Thanks,

All statistics were COMPUTEd after the load. Even if I use ('john','john') or break that out into multiple OR's, the thing still refuses to use the index.

Even with
*_index_caching=100
*_index_cost_adjust=1

no dice!

Using the same table and data distribution on the 9i/NT box I find it difficult to convince the optimizer that it should NOT use the index.

-Kevin

"Richard Foote" <richard.foote_at_bigpond.com> wrote in message news:FYDb9.18777$g9.58020_at_newsfeeds.bigpond.com...
> 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 - 08:48:47 CDT