RE: Performance of views

Helmut,

Oracle *can* "push" the criteria down into the view definition in many situations. Certain operations, though, will not allow this to happen. You can find some info on Metalink, and, in the Oracle 8i Designing and Tuning for Performance manual. There are other source as well. Here is a little snippet from the Oracle 8i Designing and Tuning for Performance manual:

<start>
To merge the view’s query into a referencing query block in the accessing statement, the optimizer replaces the name of the view with the names of its base
tables in the query block and adds the condition of the view’s query’s WHERE clause
to the accessing query block’s WHERE clause.

This optimization applies to select-project-join views, which are views that contain
only selections, projections, and joins—that is, views that do not contain set
operators, aggregate functions, DISTINCT, GROUP BY, CONNECT BY, and so on (as
described in "Mergeable and Non-mergeable Views" on page 4-77). <end>

Your best bet is to do explain plans on queries against the view(s) and verify the access paths are optimal.

Lots more to say but I'm trying to keep this brief.

Regards,

Larry G. Elkins
elkinsl_at_flash.net
>
> Hi!
>
> Some of our developers are having concerns about using views in the
> application. So they approached me and wanted to clarify some of their
> issues.
>
> When I issues a "select * from viewname", Oracle executes the underlying
> select statement of the view. This underlying statement should be
> optimized
> (using availabale indexes on tables etc.)
>
> If I issue a "select * from viewname where condition < 3" or the
> like, will
> the indexes still be used. Or how is this statement executed? Does Oracle
> first run the underlying select statement and then apply the "where
> condition < 3" to the returned result set? Or is the statement being
> rewritten internally?
>
> The Oracle documentation is not very clear on this. Any ideas would be
> appreciated.
>
> This is 8.1.6 on Win2k.
>
> Thanks,
> Helmut

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: elkinsl_at_flash.net

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).