Re: Function based index with <>
- From: Adric Norris <spikey.mcmarbles@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Wed, 19 Aug 2009 17:05:58 -0500
On Wed, Aug 19, 2009 at 15:40, steve montgomerie <stmontgo@xxxxxxxxx> wrote:
> Oracle 10.2.0,4 on OEL 4.5.
>
> I have an app generating SQL which I can't change. The following sql causes
> FTS on a big table
> AND DELETE_FLG <> 'Y'
> ...
> Can I create an FBI on an expression? I didn't think that was possible.
>
You can certainly create a function based index using either the CASE or
DECODE statement... Mark already provided the syntax for the former case.
Convincing the optimizer to actually use it, on the other hand, seems to be
quite problematic without the ability to modify the query in question.
Here's the setup for my quick 'n dirty test case (Oracle 10.2.0.4 on
Solaris):
create table t (
id number(9) primary key,
flag char(1) not null check (flag in ('Y','N'))
);
create index i on t (
case when flag <> 'Y' then flag end
);
begin
for i in 1..10000 loop
insert into t values (i, decode(mod(i,100),0,'N','Y'));
end loop;
end;
/
commit;
exec dbms_stats.gather_table_stats(user, 'T', cascade => TRUE, method_opt=>
'FOR ALL COLUMNS SIZE 1')
exec dbms_stats.gather_table_stats(user, 'T', cascade => TRUE, method_opt=>
'FOR COLUMNS flag SIZE 2')
exec dbms_stats.set_table_stats(user, 'T', numblks => 100000)
-- this still does a FTS
select * from t where flag <> 'Y';
I've tried a number of variations of the DBMS_STATS invokations, but so far
everything results in a full table scan. :(
I'd definitely be interested if anyone knows how to trigger use of the index
in this type of scenario.
--
"I'm too sexy for my code." - Awk Sed Fred.
Other related posts:
