Re: ORACLE Data Partitioning?

In article <799845118snz_at_jlcomp.demon.co.uk>

           Jonathan_at_jlcomp.demon.co.uk "Jonathan Lewis" writes:

> In article <3ockmm$sk7_at_natasha.rmii.com>
> jgentry_at_dbintellect.com "Jeff Gentry" writes:
>
> : E A Macnaghten <ar02_at_dial.pipex.com> wrote:
> : >
> : > Be warned:
> : >
> : > In Oracle V6 the optomiser did not use indexes when querying
> : > views with a UNION in it, I do not know if this has changed in
> : > V7
> :
> : This has not changed in v7 (based on v7.0.16).
> :
>
>
> Wrong. There is an example somewhere in the manuals that shows how
> the optimiser can now fold 'where clauses' back inside a UNION.
> I can't find the page but try:
>
> create table t1 (c1 varchar2(10) primary key, c2 varchar2(20));
> create table t2 (c1 varchar2(10) primary key, c2 varchar2(20));
>
> create view v1 as
> select c1,c2 from t1
> union
> select c1,c2 from t2;
>
> explain plan for select c2 from v1 where c1 = 'XXX';
>
> This shows
> Indexed access on t1
> Indexed access of t2
> _followed by_ union-all, sort unique, projection
>
>
> Version: 7.0.16: HP-UX based.
>
> --
> Jonathan Lewis
>

Having tweeked about on 7.0.16 (VMS) with various view options on this kind of thing I've found that whilst ORACLE will use indexes it doen't always behave as expected.

Queries using outer joins can cause some real problems, as can WHERE IN clauses using more than one value - i.e. WHERE IN ('X') can use indexes but in certain cases of complex WHERE clauses WHERE IN ('X','Y') removes the use of an index.

These problems arise more when you join other tables to your view.

I think what you really have to do is test out the case in mind taking account of how complex some of your SQL statements are.

-- 
Mark Bairden