Views within views

SQL Apprentice Question
We have a large database that gets queried often for reporting purposes. For
data access, we are limiting the users to views only, no direct table access.
The view definitions can be quite complex, incorporating as many as 20
tables or more. We have been limiting the view definitions to contain table
joins only, no views, so that the indexes are being utilized.

Some of the developers would like to create views of the common
multiple-table joins to then be re-used within other views.


I was under the impression that using views that bring back thousands of
rows of data within other views leads to performance problems due to the
"table scan" of the subview for the join of that information to the table in
the outer view.


Does anyone have any thoughts?


By the way, yes, we are looking at creating tables with this information for
reporting use in the long-term but we need to know the thoughts on the above
issue in the short-term.


Celko Answers
>> I was under the impression that using views that bring back thousands of rows of data within other views leads to performance problems due to the "table scan" of the subview for the join of that information to the table in the outer view. <<


Not true. The text of the VIEWs are often copied into the parse tree
and then the whole thing is compiled in the usual manner. The
optimizer can re-arrange the query and is not obligated to execute it
in a particular order. That is what happens with procedural language
and not with declarative ones like SQL

In some products, common VIEWs will be materialized and shared among
sessions, increasing overall thruput. This can be at the expense of
one session, but usually the extra main storage makes up for it and all
sessions run faster.