I have another problem :
I've created view v1 as (select c1,..,ck from t1 union select c1,...,ck from t2).
And when i perform query like this :
select * from v1 where c1 like 'asd%' and i have indices on c1 in both tables Oracle does not use this index.
Does anybody know why ?? Should I set any initialization parameter ??
Would Oracle use index if I had only index on table t1??
I uses Oracle 8i and rule optimizer
Thanks for any ideas
Can you try recreating the view with the following
create view v1 as (select c1,..,ck from t1 where c1 like 'asd%' union all select c1,...,ck from t2 where c1 like 'asd%').
then query v1, index should get used.
When u query a view, first the query used to create the view will be executed. So that time index is not used. when u use WHERE clause on the view, it will be applied on the results of the UNION query. So thats why index might not have used.