Re: How can I simplify queries with inline views?

Tim X wrote:

> 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

Tim ... inline views have nothing to do with views.

Go to http://tahiti.oracle.com and look up inline views.

Daniel Morgan Received on Mon Dec 23 2002 - 02:34:42 CST