Re: How can I simplify queries with inline views?

Don wrote:

> 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.
>
> 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.
>
> 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.
>
> Any tips would be appreciated.
>
> Don

Based on only what you have told us ... no hardware, no O/S, no Oracle version or edition, no SQL, no explain plan, no really useful information about what you are doing or the amount of data involved (KB, MB, GB, TB, or # of rows) ...

... my general impression is that your code was put together by using bubblegum and paperclips to tack stuff onto stuff onto stuff.

I'd go back to the beginning and look at the entire process because what you are doing sounds horrible. And I'd start by reexamining the base assumption that you are doing this to avoid a lot of updates. With well designed, written code, and appropriate use of indexes and the optimizer the straight forward approach would likely be far more efficient: And certainly easier to maintain.

Daniel Morgan Received on Fri Dec 20 2002 - 11:09:40 CST