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.

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.

Using Oracle 10g. The original Oracle designer for this project has moved on and those of us remaining are reasonable developer's for Oracle but we need some tuning and planning assistance.

We have compartmentalized procedures in 'like' packages, some (many) of which have grown in size to include many (50 ish) procedures of varying complexities.

At this point several small procedures (select ID from Contract where Item = 'xyz') in these larger packages take much longer 'than expected' to execute from inside a these packages (from TOAD, SQL Developer, or from .NET Oracle Provider) than it does if the proc is compiled on its own or into a smaller package. (Tables are indexed)

Should there be a performance cost for using such large packages even when calling relatively simple individual procedures or is there some 'other' factor we should be looking for?

(note: upgrade to Oracle 11 is planned but not 'imminent')

The first time any method in a package is invoked, the entire package needs to be read into memory. In general, that is supposed to be an advantage on the assumption that if you are calling one method in a package, it's likely that many related methods will be called either by the one procedure you called or by subsequent application calls. But it does mean that the first execution is potentially slowed by loading much more code may strictly be necessary for the simple function. That penalty should disappear, however, once the package has been loaded into memory. It doesn't sound like you're talking about a problem with the performance of the first call of a procedure, though, which would tend to rule this out.

Is there any code in the package's initialization block that would run before the small procedure was executed that might be skewing the results?

How are you determining how long it takes to call these small procedures and what does "much longer" mean? Are you calling them a handful of times and measuring some small number of elapsed milliseconds and seeing, say, a 30% increase in execution times? Or are you calling them thousands of times and seeing a 1000% increase in execution times?