RE: DB Design and Views

Stephan,

Contrary to what has been mentioned, the optimizer actually performs a number of transformations on the submitted query using the Query transformer - this includes 'view merging' which basically rewrites the query by merging the view query block into the query block that contains the view. According to the doco, most of the views are merged, with an exception of few types of views. Now there is no detail on what these few types are, but I do know that 'simple' views that provide a layer for security (and multiple 'simple' layers for that matter) are efficiently merged. I think Guy Harrison's SQL tuning book has the details.

Warm wishes for the season,
John Kanagaraj
Oracle Applications DBA
DBSoft Inc
(W): 408-970-7002

Wanna know the reason for the season? Click on 'http://www.needhim.org'

• The opinions and statements above are entirely my own and not those of my employer or clients **

> -----Original Message-----
> From: Kimberly Smith [mailto:ksmith2_at_myfirstlink.net]
> Sent: Tuesday, December 11, 2001 6:40 AM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: DB Design and Views
>
>
> I would avoid views based on view. As well, I would avoid using views
> where you will later turn around and throw a distinct or a group by
> or anything else on it (especially if you are dealing with a
> lot of data).
> Basically, You cannot be assured that the optimizer will pick
> the proper
> path once the select is within a view and you do anything other then a
> straight select on that. I have seen it work fine and I have seen it
> bring an application to a grinding halt.
>
> -----Original Message-----
> Jahnke
> Sent: Tuesday, December 11, 2001 4:30 AM
> To: Multiple recipients of list ORACLE-L
>
>
> Hi,
>
> as I remember, it was always recommended to avoid the use of
> views upon
> views upon views in the design of an Oracle DB (as for version 7/8),
> since the optimizer might get confused.
> Does that still apply ?
> I'm supposed to give some guidelines to developers about the usage of
> views.
> My point of view is, that views should only be used to grant limited
> access to tables in a schema (horizontal/vertical) or to
> consolidate/pre-calculate data.
>
> Any input ?
>
> Regards,
> Stefan
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Stefan Jahnke
> INET: stefan.jahnke_at_d2vodafone.de
>
> 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).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Kimberly Smith
> INET: ksmith2_at_myfirstlink.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).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: John Kanagaraj
  INET: john.kanagaraj_at_hds.com

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).