Is there a reason not to use views in Oracle?

I have recently noticed that nobody uses views in my company (and it's a big company).

I want to create a few vi开发者_JAVA技巧ews largely because they make my queries simpler to the eye, and these views are on somewhat big tables that don't get very frequent updates (once a day).

My alternative is to create a type table of type record an populate it each time a SP is called. Is this better than using a view? (my guess is no)

PS: database is oracle 10g and EDIT: - yes i have asked around but no one could give me a reason. - both the views and the queries that will be using them are heavy on joins.

---

Aesthetics doesn't have a place in SQL, or coding in general, when there's performance implications.

If the optimizer determines that predicate pushing can occur, a view will be as good as directly querying the table(s) the view represents. And as Justin mentions, because a view is a macro that expands into the underlying query the view represents -- a soft parse (re-use of the query from cache) is very likely because the cache check needs to match queries exactly.

But be aware of the following:

• layering views (one view based on another) is a bad practice -- errors won't be encountered until the view is run
• joining views to other tables and or views is highly suspect -- the optimizer might not see things as well if the underlying query is in place of the view reference. I've had such experiences, because the views joined to were doing more than what the query needed -- sometimes, the queries from all the views used were condensed into a single query that ran much better.

I recommend creating your views, and comparing the EXPLAIN plans to make sure that you are at least getting identical performance. I'd need to see your code for populating a TYPE before commenting on the approach, but it sounds like a derived table in essence...

It's possible you would benefit from using materialized views, but they are notorious restricted in what they support.

---

It certainly sounds like creating some views would be helpful in this case.

Have you asked around to see why no one uses views? That seems quite odd and would certainly tend to indicate that you're not reusing your SQL very efficiently. Without views, you'd tend to put the same logic in many different SQL statements rather than in a single view which would make maintenance a pain.

---

One reason not to use views which may or may not be valid... is that they have the potential to create complexity where there isn't any

For example I could write

CREATE VIEW foo as <SOME COMPLEX QUERY>

then later I could write

CREATE Procedure UseFoo as 
BEGIN
       SELECT 
          somefields
       FROM
          x 
          INNER JOIN foo
         .....

So now I'm creating to objects that need to be deployed, maintained, version controlled etc...

Or I could write either

CREATE Procedure UseFoo as 
BEGIN
       WITH foo as (<SOME COMPLEX QUERY>)
       SELECT 
          somefields
       FROM
          x 
          INNER JOIN foo
         .....

or

CREATE Procedure UseFoo as 
BEGIN

       SELECT 
          somefields
       FROM
          x 
          INNER JOIN <SOME COMPLEX QUERY> foo
         .....

And now I only need to deploy, maintain, and version control a single object.

If <SOME COMPLEX QUERY> only exists in one context maintaining two separate objects creates an unnecessary burden. Also after deployment any changes to requires evaluating things that rely on UseFoo. When two object you need to visit anything that evaluating on UseFoo and Foo

Of course on the other hand if Foo represents some shared logic the evaluation is required anyway but you only have to find and change a single object.

---

It has been my experience that when you have a large/complex database and some complex queries and no views, it is just because the users just don't know what views are, or how to use them. Once I explained the benifits of using a view, most people used them with out any problems.

From your description, I would just make a view, not a new table.

---

Views are great for hiding complexity -- if your users can just run the views you create as-is (as opposed to writing a query against the view), this is good.

But views also come with performance issues -- if your users know how to write sql, and they understand the tables they're using, it might be better to let them keep doing that.

Consider also that stored procedures are less prone to (the same) performance issues that views are.

---

here is a link to and a snippet from a nice article that describes views as well as how to tune them for better peformance.

Uses of Views

Views are useful for providing a horizontal or vertical subset of data from a table (possibly for security reasons) ; for hiding the complexity of a query; for ensuring that exactly the same SQL is used throughout your application; and in n-tier applications to retrieve supplementary information about an item from a related table......

http://www.smart-soft.co.uk/Oracle/oracle-tuning-part4-vw-use.htm