From elkinsl@flash.net Wed, 02 May 2001 16:53:44 -0700 From: elkinsl@flash.net Date: Wed, 02 May 2001 16:53:44 -0700 Subject: RE: Performance of views Message-ID: MIME-Version: 1.0 Content-Type: text/plain 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: 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). 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@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@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@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).