Re: Views and Performance

Jonathan G Gennick wrote:
>
> I have some questions about how the Oracle 7.2 optimizer handles views
> vs other queries. Recently I've had a couple people tell me views are
> more efficient than "Selects" because Oracle "creates" the view when
> you define it. Conceptually I can see where an optimizer might be able
> to work out a retrieval strategy ahead of time, but I can't see how it
> could actually retrieve rows until a query is executed against a view.
> Is this correct? Is this how it works?
>
> Consider this select:
>
> select * from
> ta, tb, tc
> where ta.id=tb.id and tb.id=tc.id;
>
> Would it be more efficient to use a view:
>
> create view my_view as
> select * from
> ta, tb, tc
> where ta.id=tb.id and tb.id=tc.id;
>
> and then select from that view:
>
> select * from my_view;
>
> Why? How much time savings are we talking about? Would the increased
> efficiency, if there was any, be more noticable when running a long
> query for a report or when running a short query to retrieve a single
> record?
>
> One other question. Recently we had a query similiar to the one shown
> below that ran for most of the day:
>
> select *
> from ta,tb
> where ta.id=tb.id
> and tb.date not in (
> select tc.date from tc where
> tc.begin_date < sysdate
> and tc.end_date > sysdate)
>
> Would Oracle 7.2 execute the subquery for each potential record even
> though the subquery did not depend on any values from the main query?
>
> Performance improved dramatically when this view was created:
>
> create view really_fast as
> select * from tb
> where tb.date not in (
> select tc.date from tc where
> tc.begin_date < sysdate
> and tc.end_date > sysdate)
>
> and the select done like this:
>
> select *
> from ta, really_fast
> where ta.id=really_fast.id
>
> The query ran in one sixth the time, or about one hour instead of
> six+. The DBA never looked at any output from "Explain Plan", but
> claims the performance improvement came from creating the view because
> Oracle had "already created the view". I am of the opinion that
> because the subquery was moved to the view, and the view used in the
> "from" clause, that Oracle only did the work once and that previously
> it could have been executing the subquery repeatedly. But if I'm wrong
> I would like to be corrected.
>
> regards,
>
> Jonathan

I think there is no difference between views and selects. You save time with view because you must not retype your select every time, but the optimizer sees no differences. Received on Sat Feb 22 1997 - 00:00:00 CET