Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> Re: in PL/SQL : String replace on some condiations - i hate IF ELSE
baka wrote:
> 1.There is csv data (Data-ware house kind ...) ---EXTRACT PHASE
> 2.Load that data into raw table using SQL*LOADER -- Bcos FAST
> 3. Read that table and link break some column,make some column, replace
> some column,get some info from other table.
> if not exists put some default value. do some more for each record
> ---Transform PHASE
> 4. Then insert the modified data to new table. --LOAD PHASE
Why not see how well this works using EXTERNAL TABLES and a pipelined table function to do the transformation?
Instead of SQL*Load'ing into a table and then hitting that table to process the data, these two serial processes can effectively run concurrently.
Something like this:
INSERT /*+ APPEND*/ INTO target
SELECT * FROM TABLE( pipeline( CURSOR(SELECT * FROM externaltable)) )
The APPEND hint provides a direct path load. The CURSOR provides the CSV input into a pipeline table function (a PL/SQL function) that transforms a CSV row into a target row. The CSV data is loaded using an external table (which is still SQL*Loader under the hood).
I would also enable parallel DML - as the pipeline table function can be run in parallel and so too (I assume) the SQL*Load via external table as SQL*Load itself support parallel processing. If the complete process can be parallelised, performance gains should be very noticable.
-- BillyReceived on Tue Jun 14 2005 - 08:28:11 CDT
![]() |
![]() |