Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: How can I simplify queries with inline views?

Re: How can I simplify queries with inline views?

From: Don <anon_at_anon.com>
Date: 24 Dec 2002 08:06:13 -0600
Message-ID: <3e086760.46662421@news-east.newscene.com>


There is some confusion as to what I mean by updates. This is a simplified version of whats happening.

Table A has these columns:
PART, MASTER_PART, TYPE Now I need to get the part and master_part, for a certain type, and count the records and put it in a qty field. For example, SELECT part, MAX(master_part), count(*)

   FROM A
   WHERE type='someType'
   GROUP BY part

There are 3 types so I must do this 3 times. The original script got this count and updated a QTY field in the table, then removed dups (identified by part), thne inserted into the final table.

I wanted to avoid these updates and do this on the fly. I made inline views for all 3 types (like the one above). Once I join all 3 together I must group by the master_part so this entire select becomes an inline view.

It is a little more complicated than this but this is close to what I'm doing. I could not find out a way to do it without inline views.

Don

On 23 Dec 2002 09:43:24 +1100, Tim X <timx_at_spamto.devnul.com> 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
Received on Tue Dec 24 2002 - 08:06:13 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US