Re: Partition Views on Oracle 7.3.2

From: Venkat S. Devraj <vdevraj_at_usa.net>
Date: 1997/05/20
Message-ID: <338227AC.3425@usa.net>#1/1

Roberto Bruno wrote:
>
> I'm having a problem using partition views in Oracle 7.3.2.
> According to Oracle documentation, that is accomplished through...
 

> Well, I did all that and got no partition access. The optimizer tried to
> make full scans in all tables involved, even though it was perfectly
> defined which ones could or could not have rows satisfying the query. I
> made several testes, with 2 to 5 different partitions and different hints,
> and nothing seemed to work. Does anyone have any idea on how to make this
> work out ?
 

> Roberto_Bruno_at_south-america.notes.pw.notes.com
 

> Roberto Bruno

---

Venkat Devraj replies :

---

Based on my experiments, partition views only work with cost-based optimization. Either of the following steps should help : a) Estimate / Compute statistics for the partioned tables (this might not be very feasible, specially if there are a large number of tables involved or if the tables are highly volatile in nature); OR
b) Put in any of the following hints : ALL_ROWS or FIRST_ROWS at the time of the creation of the main-view. These hints would tell the optimizer to be cost-based while evaluating the view-statement. An example would be :
  CREATE OR REPLACE VIEW main_view
  AS
  SELECT /*+ ALL_ROWS */ * FROM first_part_table   UNION ALL
  SELECT /*+ ALL_ROWS */ * FROM second_part_table;

Best of luck,
Venkat S. Devraj
vdevraj_at_usa.net Received on Tue May 20 1997 - 00:00:00 CDT