| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: View performance
A copy of this was sent to Umar FArooq <umar.farooq_at_cressoft.com.pk>
(if that email address didn't require changing)
On Fri, 29 May 1998 13:00:49 +0500, you wrote:
>the execution plan and the parse tree are already built and stored and,
>thus, querying from a view saves the corresonding time if a SELCT stmt
>was issued.
>Umar.
>
Thats not true. The execution plans and parse trees are built at run time, not at compile time. The only thing that is 'built' and stored at compile time is the fact that the objects referenced in the view are referenceable (security is checked).
All SQL is dynamic in Oracle. We do not store permanently optimized query plans anywhere.
Oracle supports the concept of a shared sql area. In this area, sql that has already been parsed and optimized by the database since it was started is stored. if anything relating to the parsed/optimized sql changes (eg: an index which would affect query plans is added to the system or taken away) we flush the plans and rebuild them later.
So views and their base queries have very similar performance characteristics. Views have less security checks to go through though (we need to see if you are allowed to access the view, even if the view references 10 tables we only need to check access to the view. OTOH, if you submit the query directly against the 10 tables we have to check access to the 10 tables). So views *might* parse and optimize faster the first time around however the 2'cnd and N'th execution against the view or the base tables (while the plans are in the shared sql area) will be equivalent.
>stegri_at_rocketmail.com wrote:
>
>> Does anybody know if using views instead of tables affects
>> performance?
>> I've heard that view are in some way 'compiled' (although I don't
>> understand
>> what is there to be compiled) and are even more efficient than
>> retriving
>> columns as 'normal' select-stmts.
>>
>> /Stelios Grigoriadis
>
>
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Fri May 29 1998 - 08:28:53 CDT
 |
 |