Oracle views and performance 1

[johnherman]

What benefits or drawbacks can be expected by using Oracle views overlaying tables rather than directly accessing the tables with joins? Are there differences if the database is tuned for Data Warehouse as opposed to Transaction Processing?

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[SantaMufasa]

John,

Think of an Oracle VIEW as simply a query (i.e., a SELECT statement) that is stored in Oracle's data dictionary, and has a name affixed to that stored query.

Therefore, the difference in performance between a VIEW and (as you said) "directly accessing the tables with joins", is the infinitesimally tiny amount of time it takes Oracle to read in the SELECT statement from the data dictionary.

There is never a measurable penalty for using a VIEW instead of a direct query.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[jimbopalmer]

At least in earlier versions of Oracle (6, 7, & 8) the optimizer can become less accurate if you use views of views. I do encourage you to always make views of base tables, never views of views. But SantaMufasa is right, for end user queries, there is almost no hit to using views. If you are creating objects, there is more of a risk you will defeat the optimizer. (and for all I know the optimizer got wiser after Oracle 8)

I tried to remain child-like, all I acheived was childish.