| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: anyone use pipelined functions?
Directly in the SQL. We use Designer TAPI autosequence generation for
day-to-day operations, but triggers slow down inserts and of course can't
be enabled for direct path inserts.
Adam
Jared.Still_at_radisys.com
Sent by: ml-errors_at_fatcity.com
12/31/2003 11:29 AM
Please respond to
ORACLE-L_at_fatcity.com
To
Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
cc
Subject
Re: anyone use pipelined functions?
That would be cool if you have time for it.
Re the sequence: is it assigned in a trigger, or directly in the SQL?
AdamDonahue_at_maximus.com
Sent by: ml-errors_at_fatcity.com
12/31/2003 11:19 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L
<ORACLE-L_at_fatcity.com>
cc:
Subject: Re: anyone use pipelined functions?
In the interests of documentation, and if I have time, I could engineer a similar 'dumb' procedure, perform trace as each modification is made, and post the results here. It's pretty easy to come up with an artificial routine, though, to do this kind of analysis oneself. Use Tom Kyte's BIG_TABLE approach, and then create a procedure to populate a separate table using single-row inserts, and subsequently, an insert append.
Adam
Jared.Still_at_radisys.com
Sent by: ml-errors_at_fatcity.com
12/31/2003 10:14 AM
Please respond to
ORACLE-L_at_fatcity.com
To
Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
cc
Subject
Re: anyone use pipelined functions?
Fantastic results Adam.
You didn't perhaps do interim testing did you, so that you know how much of the benefit was due to the pipelined functions?
You made quite a few changes, and a breakdown of the the benefits of each would be interesting to see.
Jared
AdamDonahue_at_maximus.com
Sent by: ml-errors_at_fatcity.com
12/31/2003 09:04 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
cc:
Subject: Re: anyone use pipelined functions?
I recently rewrote a poor-performing data load procedure (with single row inserts, commit batches of 2000) to a pipelined table function, which enabled insert /*+ append */ into the target table, which greatly enhanced
performance. The original routine contained an embedded select, a second select using a top-level select key, and then a large loop with data operations culminating with an insert of each row (and sequence value generation). The routine took about four hours to run.
I joined the queries into a single inner join select, parallelized; added a cache to the sequence (which had been set to zero); added a second index
to the source table to enable FFS; engineered the function to leverage pipelining (moving the to an insert /*+ append */ into ... select * from table( function ); made the requisite modifications to the target table, and reran the load. The time came down to about 25 minutes. The total work spent in engineering the procedure was about 2 hours, so the work put
into tuning it + its improved runtime came in at less than the total original runtime! (Of course, this does not include reenabling constraints, triggers, etc., but these things bring the total runtime up to about ~1 hour in this case, still an improvement.)
The benefit-cost ratio here was quite high!
Adam
<ryan_oracle_at_cox.net>
Sent by: ml-errors_at_fatcity.com
12/31/2003 06:24 AM
Please respond to
ORACLE-L_at_fatcity.com
To
Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
cc
Subject
anyone use pipelined functions?
I read the little blurb in the 9i new features on it. The example there doesnt seem very useful. What have people used it for?
any good articles with good examples on this?
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: <ryan_oracle_at_cox.net
INET: ryan_oracle_at_cox.net
Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services ---------------------------------------------------------------------To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
INET: AdamDonahue_at_maximus.com
Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services ---------------------------------------------------------------------To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
INET: AdamDonahue_at_maximus.com
Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services ---------------------------------------------------------------------To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
INET: AdamDonahue_at_maximus.com
Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services ---------------------------------------------------------------------To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Wed Dec 31 2003 - 15:04:25 CST
 |
 |