Re: How can I simplify queries with inline views?

anon_at_anon.com (Don) writes:

> We are writing PL/SQL code that have some huge queries with many
> inline views. These queries are used to insert data into a relational
> database from a staging area. We usually use the inline views to
> avoid making many updates to the source table - the updates occur in
> the inline view.

Sorry, I find this a bit confusing. Isn't the view just a subset of what is in the master table and as such, any update via the view must be updating the master table - so how can you have less updates updating via the view than updating directly via the master table? the only thing I can guess is the view is actually a view on many multiple tables, so doing an update via this view would mean "writing" one update statement, but you are still updating lots of tables? Personally, I don't like this as it obscures what is really going on and I think the developers should understand the real structure of the database - while something like this might appear to make coding simpler, I think the danger is development based on misconceptions regarding the real structure of the system and potentially more inefficient code - plus plus you more often will need to use trace/explain plan to work out exactly what is going on.

> Our problem is when you have a select statement with an in-line view,
> and this in-line view has a couple of in-lines views, and these
> in-line views .... etc. As you see it can get very hard to follow.
>

Yuk! I worked on a well known student database management system for a university which had this sort of setup - it was a nightmare. Different people would operate at different levels of view depth and it was often very dificult to work out exactly what was going on.

I tend to feel that if you have lots of deeply nested views then you need to review your database model and all the code. Often what happens is somebody creates a view for a particular report. Someone else comes along and creates another view based on the previous view to make it easier for them to code their SQL, someone else comes along and creates a view on that view ..... Often, many of the reasons for the original views have been removed as the application or problem domain evolves, but noboby wants to remove the views because its too difficult to determine what other views are based on them etc. In the end you just have a lot of pointless indirection which may have originally made life easier for a couple of developers and which is now a constant nightmare for everyone else and is possibly degrading performance.

> Do you know of an easier way to do this?
>
> We could create views and use them but I don't really want a lot of
> views around that will not be used again. Is it possible to create a
> temporary view that automatically goes away when the session dies?
> (like a temp table).
>
> Is there a way to have a PL/SQL function return a table or view? Then
> in the FROM clause we could call the function.
>

Do you really need to use the views? Is the SQL based on the master tables that complicated you need a view to simplify it or is using the view just easy? What/why exactly do you want to use a view?

My gut feeling is that if you are wanting to insert/update, then don't use a view at all. If its reporting you are working on, determine how often the queries will be run and unless they are a very common query which involves lots of joins etc, don't use the view. If the query is a report hwich collects some sort of summary/statistical information from your master tables, have a look at materialised views (8i or better), but avoid deeply nested views.

Tim

-- 
Tim X.
tcross (at) northnet com au