| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: speed up a slow view
Sarah Officer wrote:
>
> I have created some views in my database which are complex. The
> select statements which define the views include unions and
> subqueries, and some views depend on other views. Is there a way to
> speed up the execution of the views? How can I tell whether my
> views are executing as quickly as possible? Would it be faster to
> create a stored procedure?
>
> I *know* this is gross. I just don't know any way to get around
> it. Any suggestions or tips would be appreciated. Here's a sample:
>
> ********
> This view is supposed to show only the latest date/time records for
> the pid & category. It also has to join tables. The reason I have
> a subquery here is because of a grouping problem. I only want the
> maximum for each pid & category, but I need lots of other fields
> from the mdata table.
>
> create view CurrentResultsView as
> select r.pid, r.mi_id, r.category, r.count,
> m.mname, m.idatetime, m.processed, m.gfile
> from results r, mdata m
> where r.mi_id = m.mi_id
> and m.processed = 'P'
> and (r.pid, r.category, m.idatetime) in
> (select r2.pid, r2.category, max(m2.idatetime)
> from results r2, mdata m2
> where r2.mi_id = m2.mi_id
> and m2.processed = 'P'
> group by r2.pid, r2.category);
>
> ********
> This view is supposed compare current results to normal value
> ranges, and create a status indicating whether the range is normal,
> outofbounds, or an error for the pid & category. It is supposed to
> take into account that there may be a normalcy record but no current
> result record, in which case the current result should be considered
> 0
>
> create view AlertsView as
> select cr.pid, cr.mi_id, cr.category, cr.count,
> n.normal_min, n.normal_max, 'NORM' status
> from CurrentResultsView cr, Normalcy n
> where cr.pid = n.pid
> and cr.category = n.category
> and cr.count >= n.normal_min
> and cr.count <= n.normal_max
> union
> select cr.pid, cr.mi_id, cr.category, cr.count,
> n.normal_min, n.normal_max, 'OUT-OF_BOUNDS' status
> from CurrentResultsView cr, Normalcy n
> where cr.pid = n.pid
> and cr.category = n.category
> and (cr.count < n.normal_min
> or cr.count > n.normal_max)
> union
> select n.pid, NULL, n.category, 0,
> n.normal_min, n.normal_max, 'NORM' status
> from Normalcy n
> where n.normal_min = 0
> and (n.pid, n.category) not in
> (select r.pid, r.category
> from CurrentResultsView r)
> union
> select n.pid, NULL, n.category, 0,
> n.normal_min, n.normal_max, 'OUT-OF-BOUNDS' status
> from Normalcy n
> where n.normal_min > 0
> and (n.pid, n.category) not in
> (select r.pid, r.category
> from CurrentResultsView r)
> union
> select r.pid, r.mi_id, r.category, r.count,
> 0, 0, 'ERROR' status
> from CurrentResultsView r
> where (r.pid, r.category) not in
> (select n.pid, n.category
> from Normalcy n)
> ;
>
> Sarah Officer
> officers_at_aries.tucson.saic.com
One important thing is how the view is resolved...
If you use views that have a grouping expression (eg group by, sum, max, count, distinct etc etc) joined to another table/view, then oracle will resolve the complex in its entirity BEFORE applying where clauses...
Consider the following...
view SALES_TOT is
select quarter, sum(total)
from sales
group by quarter;
then "select * from sales_tot where quarter = 3" will not necessarily scan the sales table (dependent on indexes etc etc)...
BUT...put the view in a join (say)
select from SALES a, SOME_OTHER_SALES b
where a.quarter = b.quarter
and a.quarter = 3
and Oracle will fully resolve the SALES_TOT view (ie scan all the rows in sales and work out the sum for each quarter) and THEN apply the result to the join (and the where clause for the quarter)
You need to be careful when joining views with grouping...
HTH
--
"Some days you're the pigeon, and some days you're the statue." Received on Wed Jul 14 1999 - 09:22:56 CDT
 |
 |