Partitioned view problem

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 Wed Dec 10 1997 - 00:00:00 CST