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
[EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
