Re: Oracle view riddle I am being asked , help me out here.
The create view does not access the data at all. It just create an object in the data dictionary. What you need to compare is 'select * from view' against doing the select from sqlplus. Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, April 02, 2003 7:34 PM So why would the sql statement used to create a view run faster (significantly) than selecting from that view built with the same sql statement? thanks, David Ehresmann -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ehresmann, David INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Yechiel Adar INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
Re: Oracle view riddle I am being asked , help me out here.
Ehresmann, David wrote: So why would the sql statement used to create a view run faster (significantly) than selecting from that view built with the same sql statement? thanks, David Ehresmann David, A view is a STORED object, which means that it executes using a plan which was 'frozen' at compilation time. Nowadays stats updates normally cause a view to reevaluate the plan but there are some parameters (sort_area_size, hash_area_size, to name a few) which may significantly change the execution plan without being perceived by Oracle as worth of a recompilation (or cursor re-evaluation). Another possibility is that the statement was compiled in another schema, and references similar named, but different, objects. Check the execution plans, they are probably different. Recompile the view, and (hopefully) it should be better. I assume of course that you have no additional WHERE clause. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
Re: Oracle view riddle I am being asked , help me out here.
Run autotrace or sql_trace and determine if the execution plans are different. If they are the same, increase the sql_trace level and find out what the wait events are. -- Daniel W. Fink http://www.optimaldba.com IOUG-A Live! April 27 - May 1, 2003 Orlando, FL Sunday, April 27 8:30am - 4:30pm - Problem Solving with Oracle 9i SQL Thursday, May 1 1:00pm - 2:00pm - Automatic Undo Internals Ehresmann, David wrote: So why would the sql statement used to create a view run faster (significantly) than selecting from that view built with the same sql statement? thanks, David Ehresmann -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Daniel W. Fink INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
