I am not sure I completely understand what you want to do, and you do not
say what version of Oracle you are using, but an analytic function might do
what you want. These are available since 8.1.6, I believe. You could get
your count for each part with a single table access and sort. It is the kind
of thing we do in data warehousing when remodeling data, and I have been
impressed with the performance improvement I can get in certain cases using
analytic functions. By using the three inline views in a FROM clause and
joining them, you are essentially doing a triple self-join on the underlying
table. This is one of the situations where I would start thinking about
using analytic functions.
Analytic functions are in the Oracle docs, but may be a little hard to find.
Look at the data warehousing documents, if I remember correctly.
In my mind, SQL with multiple nested subqueries or inline views is not
necessarily a bad thing when manipulating large data sets--as long as they
are tuned and efficient. This is probably less commonly seen in
transactional applications.
CSL
"Don" <anon_at_anon.com> wrote in message
news:3e086760.46662421_at_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 Mon Jan 06 2003 - 23:41:18 CST
