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.

--
Billy