Re: Partitioned view problem

In your example, select count(1) from table, Oracle does not need to access rows in the table to count them;
in select count(1) from partition view, each row in the table has to be retrieved and the test

                date_col = to_date('19971118','yyyymmdd') has to be evaluated.

Regarding 'more time for more tables'. This may be because of several different bugs - are you using inernode parallel query on your OPS system ?

Try starting everything with sql-trace true, and seeing what the STAT lines in the PQ slaves say (and what the actual SQL that reaches the PQ slaves is). The most irritating problem with PQ and PV combined is that Explan Plan and tkprof plans aren't always true.

Wilhelm Thieme <w.thieme_at_consunet.nl> wrote in article <66mr6r$s5u$1_at_news.worldonline.nl>...
> Hi everyone
>
> I'm struggeling with a partitioned view which will NOT give
> me the performance as expected.
>
> The view is created as follows:
> create or replace view temp as
> select * from table_19971118 where

 date_col=to_date('19971118','yyyymmdd')
> union all
> select * from table_19971119 where

 date_col=to_date('19971118','yyyymmdd')
> union all
> select * from table_19971120 where

 date_col=to_date('19971118','yyyymmdd')
> union all
> select * from table_19971121 where

 date_col=to_date('19971118','yyyymmdd')
> /
>
> any query directly performed on the underlaying table takes
 considerable
> (depending on the number of tables 5, 10 or more times) less time
 compared
> to
> the same query using the view.
>
> Example:
> select count(1) from table_19971118;
> vs
> select count(1) from temp where

 date_col=to_date('19971118','yyyymmdd');
>
> I noticed the following: the more tables in the view the worse
> the performance.
>
> Things that I've already checked:
> - Parameter partition_view_enabled = TRUE
> - Tables are analysed using compute statistics
> - Cost Based Optimizer is used
> - Explained an execution plan. Filter statement appears for those
 tables
> that should be filtered out (partition elemination).
> - Checked the actual executed SQL-statement. Tables that should be
> eliminated
> by the optimizer have indeed a predicate in the form that always
 evaluates
> to FALSE

 (to_date('19971118','yyyymmdd')=to_date('19971119','yyyymmdd'))
> - Check constraints in stead of where clauses does not matter.
>
> We're using an IBM SP2 configuration (AIX) with OPS 7.3.3 (10
 oracle
> instances)
>
> Any ideas welcome!
>
> Thanks
>
> Wilhelm Thieme
>
>
>
Received on Thu Dec 11 1997 - 00:00:00 CST