Views and Performance

From: Jonathan G Gennick <gennick_at_worldnet.att.net>
Date: 1997/02/21
Message-ID: <330da4d8.256225_at_netnews.worldnet.att.net>#1/1


[Quoted] 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 Received on Fri Feb 21 1997 - 00:00:00 CET

Original text of this message