Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Performance of views

RE: Performance of views

From: <>
Date: Thu, 03 May 2001 05:36:47 -0700
Message-ID: <>


When you tune views you have to keep in mind how you select from the view - you may tune perfectly the select statement that builds the view but when you select from the view the query may be very slow. When you run "select * from myview" the performance may be much different than "select * from myview where mycolumn = somevalue"
Whenever I had to tune a view I used the view select statement but added the condition that was going to be used later and tuned all together. If the view was going to be used with two different conditions, we ended up with two different views hinted in different ways. IN the final result the view select statement may be slow but the select statement from the view has to be fast.

If you need more details, let me know

HTH Witold

Witold Iwaniec
IT Consultant

> 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:

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: (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).
Received on Thu May 03 2001 - 07:36:47 CDT

Original text of this message