Re: Performance issue

Try putting a hint in your view:

create or replace view foo as
select /*+index(a a_idx) +index(b b_idx)*/ * from a union all select * from b;

Don't know if you should put the hint in the 1st select or split it between selects. explain will tell you.

Hope this helps,
Mitch

I speak for noone.

Allen Kirby wrote:

> Peter McKenzie wrote:
> >
> > Oracle 7.3.....
> >
> > I have two tables - 'A' and 'B' with identical structures. Both are indexed
> > on column X.
> >
> > I have a view 'C' = select * from A union all select * from B.
> >
> > If I select * from 'C' where X='a value'
> > the index on column X is not used (no index is used)
> >
> > I have tried using hints and various other tricks but Oracle refuses to use
> > the index.
> >
> > Any ideas welcome
> >
> > Thanks,
> > Peter
>
> Try this:
>
> select * from A where X = 'a value'
> union all
> select * from B where X = 'a value';
>
> This should use both indexes.
> --
> Allen Kirby
> AT&T Information Technology Services
> Alpharetta, GA.
> The views expressed here are mine, not my employers.
Received on Thu Jan 15 1998 - 00:00:00 CST