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: sfaroult_at_oriole.com

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