Re: anyone use pipelined functions?

From: <AdamDonahue_at_maximus.com>
Date: Wed, 31 Dec 2003 09:04:26 -0800
Message-ID: <F001.005DB5DA.20031231090426@fatcity.com>

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
---------------------------------------------------------------------