Re: Oracle Views efficency

A copy of this was sent to Jiggy <jshah_at_kpmg.com> (if that email address didn't require changing) On Thu, 18 Nov 1999 12:37:11 -0500, you wrote:

>Is my understanding correct that views are essentially pre compiled SQL
>statements?
>
>Lets say I create a view such that -
>
>CREATE VIEW myview AS
>SELECT name, department FROM employee;
>
>And now, I execute the following SQL against the view-
>
>SELECT name FROM myview WHERE department = 'HR';
>
>My understanding is the that Oracle will -
>1. Execute the view SQL
>2. Filter the o/p of the view SQL where department = 'HR'
>
>A coworker of mine argues that Oracle will add my WHERE clause to the
>view SQL and then execute it efficiently-
>
>SELECT name FROM employee WHERE department = 'HR';
>
>Any one has any idea who is right?
>
>-jiggy

See the TUNING manual for more info but given the example above, your coworker is correct -- the predicate is pushed into the view (the query against the view is rewritten to be a query against the base table and then optimized -- the where clause is 'pushed' into the view).

from the tuning manual:

Optimizing Statements That Access Views

To optimize a statement that accesses a view, the optimizer chooses one of these alternatives:

o transforming the statement into an equivalent statement that accesses the view’s base tables

o issuing the view’s query, collecting all the returned rows, and then accessing this set of rows with the original statement as though it were a table

So, it does both.... under different circumstances...

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu Nov 18 1999 - 12:06:38 CST