Is there a reason not to use views in Oracle?

Question

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

I want to create a few views 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.

Answer 1

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.

Answer 2

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.