Re: Any way to disable UNDO (rollback) with temporary tables?

NetComrade wrote:
>
> On Tue, 02 Dec 2003 17:48:04 GMT, Hans Forbrich <forbrich_at_yahoo.net>
> wrote:
>
>
> Probably I confuse you a bit here.. the actual implementation is an
> array of records.. Difference between records and objects, if I
> recall, is that objects have methods.. i think :) sorry for the
> confusion. As comparing it to a table.. you can say a record is a row
> of a table.. but that's the thing.. I don't want to return a row of a
> table, I want to return an array of records, since the array would be
> populated from different sources.

Yes, that makes more sense.

> >This is the part that I was thinking about - in some cases people are
> >using Pro*C or other procedural code, just 'cause they can't see a way
> >to do it using SQL.
>
> I am almost sure, that some of the things we are doing could be done
> in SQL, since a lot of it has been written years ago. But when you
> look thru one sql statement, and have to select from different tables
> based on each row, and extra fetching can produce multiple additional
> rows (cancelling the option of a function), we are forced to look into
> an array of records (note, ideally, the record would have a maximum
> length equal to maximum number of columns returned in record-set, so a
> return could be something like)

Common mistake is thinking that looping and handling each row indidually is always going to involve less fetching than handling the entire set. The idea that joins are 'universally expensive' is a myth that should have been left behind with Oracle 7.0 At this point, there is only one way to tell for sure and that is to test the variants.

In general, I've found that a complex join will almost always involve less resources than attempting to reproduce the same effect in some procedural language, including PL/SQL.

There are several techniques, many described well in 'Mastering SQL' (pub. by O'Reilly) for optimizing selects.

>
> field1 fileld2 filed3 field4 field5 field6 field7 field8
> abc 098 987 78 987 98 123 12
> dba 32 243
> zvq 123 323 23 08 78 98
> abk 12 12 32 098
> zds kkk
> bil asd 13 123 32
>

I'd almost guess that a very significant chunk of this could be solved with inline views and the WITH clause. But I've found that both concepts are relatively foreign to many developers. (Once they catch on, though .... <g>)

So far you have not convinced me that a temp table is necessary. If this could be reduced to one (potentially gosh-awfully-complex) SQL statement, you can avoid a lot of your issues.

> Any of those rows can come from a number of queries, and some 'fields'
> within each record can be empty, as you can see
>
> >> All of this logic right now is in middle tier, and certain pages
> >> (applications) require 100's of round-trips to oracle, which we are
> >> trying to avoid. Oracle has showed us a case where we can copy objects
> >> back to Pro*C, but it does so one by one, which defeats the idea of
> >> eliminating network round trips, another things we tried is passing
> >> multiple arrays (e.g. one for each column of the object), but that
> >> seemed even worse (network traffic increased dramatically)
> >
> >A generalism (which may not be appropriate for your case) is that
> >anything that needs more than 1/2 dozen round trips should be carefully
> >re-evaluated to determine whether more can be resolved at the database
> >side.
>
> That is what we are trying to accomplish, but are facing higher
> overhead than we expected.
>
> >- PL/SQL block embedded in the Pro*C code
>
> I think we looked into this, but I'll take another look. How will that
> help with network traffic?

May be able to resolve the data in the PL/SQL, which is done at the server. Potentially this might reduce the 100s of round trips to one per row, or possibly allow use of the array of records that you need.

>
> >- OraParamArray (not sure which version this was introduced)
>
> Will look into
>
> >- dropping to OCI
>
> Um.. my understanding was that Pro*C is just some wrap around OCI
> (once again, i am not a developer), and any oci call should be
> available in pro*c.. and how would we be able to copy arrays of
> records in bulk in OCI :)

Not quite. OCI is roughly the next level down and you have a LOT more control. But, IIRC (and it's been a while), Pro*C has a different lineage, not OCI .... I'm sure I will get 1/2 doz. corrections on this, though.

In general, good luck with this. It's an interesting problem .... be nice to hear your resolution. Received on Tue Dec 02 2003 - 15:39:48 CST