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:[EMAIL PROTECTED]] > 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: [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). > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Kimberly Smith > 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). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Kanagaraj 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).
