Re: Performance and Views

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

In General, "not in" clauses are bad. In my experience try something like this instead:

	select *
	from ta,tb
	where ta.id=tb.id
	and tb.date in (
	    select date from tb
            minus
            select tc.date from tc where
            tc.begin_date < sysdate
            and tc.end_date > sysdate)

Hope this helps.

Bob Morrison Received on Tue Feb 25 1997 - 00:00:00 CET