Re: Partitioned Views

hmm... interesting.... did you use explain plan to determine the execution plan?

In any event, it should not add too much overhead if index range scan is done on all the tables, because all but one table will generate a miss on the index lookup rigth away. If you have ten or 20 tables in the view, it shouldn't cause too much an overhead. If you have (heaven forbid!) hundreds or thousands of tables in the view, it might be a different story...

It would also be interesting to look at the sql trace/tkprof output on the query against the view vs. a modified version of the query that goes against only the table containing the right date. The difference would be the overhead (watch out for SGA caching that might skew the performance results).

Pei

Michael S. Abbey <masint_at_istar.ca> wrote in article <351431a1.1129764_at_news.istar.ca>...
> On Wed, 18 Mar 1998 18:12:11 -0500, Dennis M Regan
> <dregan_at_frontiernet.net> wrote:
>
> >Help,
> >
> >I've partitioned a very large table into serveral smaller tables using
> >date field criteria as the partition. Next, I created indexes on all the
> >partitions for this date field. Then I alter the tables to create the
> >check constraint (date_file between ...). I then analyze the tables and
> >create the view using union all. When I query the view, I specify a date
> >in the where clause, and Oracle does an index range scan on all the
> >tables! Why doesn't and isn't Oracle supposed to be able to eliminate
> >partitions? What's up?
> >
> >DR
> >
>
> My experience has shown that partition elimination elimination working
> is questionable.
>
> Michael
> +------------------------------------
> + Michael S. Abbey Ottawa Canada
> + Co-author of Oracle8: A Beginner's Guide
> + and 5 other works in the Oracle Press Series
> +------------------------------------
>
  Received on Sun Mar 22 1998 - 00:00:00 CST