Re: Why doesnt partiotining elimination work?

>c) create view v_a as select * from a1 union select * from a2

    Was this a typo creating the posting, or what you actually have.     The partition view should be defined with UNION ALL, not UNION

Also check your init.ora for

    partition_views_enabled = true;

Have you analyzed all the tables to make sure that CBO will kick in ? Partitioning cannot occur under RBO.

Also, if this is a test set of data, the volume of data might be so small that the overhead of checking for partition-elimination is considered to be greater than the saving from eliminating.

You might like to check out my web-site, features topic area, for an article on partition views.

--

Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk

TT-Boy wrote in message <37A5F52B.A240C0FA_at_Rocketmail.com>...
>First I do this..
>a) create table a1 as select * from old_a where year = 1998
>b) create table a2 as select * from old_a where year = 1999
>c) create view v_a as select * from a1 union select * from a2
>d) create check contraint on a1 (year = 1998)
>e) create check contraint on a2 (year = 1999)
>f) create primary keys on a1.year and a2.year
>
>If I do...
>Select * on v_a where year = 1999
>
>The explain plan show that I access both tables a1 and a2;
>Why?
>Oracle does partiotining elimination when having check contraints or
>not???
Received on Tue Aug 03 1999 - 03:21:09 CDT