Home » SQL & PL/SQL » SQL & PL/SQL » Which is the best cursor loop for ETL with large no of columns
Which is the best cursor loop for ETL with large no of columns [message #196719] Fri, 06 October 2006 15:01 Go to next message
mohapatra
Messages: 24
Registered: August 2006
Location: New Jersey
Junior Member
Hi All,
Need suggestion.

In ETL I have one table T1 with 30 columns and T2 with 25 columns.
I need to
- validate each column in T1
- Insert all the valid rows from step 1 into Table T2.

What is the best cursor , that can be used.
1. For loop - End loop
2. OPEN FETCH CLOSE
3. Bulk collect

which one is faster .option 1 or 2.
For option 3 , as the no of columns are different , i think i CAN NOT use records - Bulk collect. So i tried using each column , an array object and only inserted the 25 out of 30 columns into T2.

Can anybody suggest some good solution to this kind of operation.
Re: Which is the best cursor loop for ETL with large no of columns [message #196722 is a reply to message #196719] Fri, 06 October 2006 15:26 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I guess that pure SQL solution *should* be faster than PL/SQL one (cursor loops). As you didn't say how this column validation should be done, let me suggest another approach: could you, for example, write something like this:
INSERT INTO second_table (col1, col2, ..., col25)
SELECT DECODE(col1, 1, 'x', 2, 'y', null),
       DECODE(col3, 'A', 2, 'C', 5, 'W', 7, 9),
       ...
FROM first_table;

In other words: could validation be put into the SELECT statement? CASE might be another option (instead of DECODE).

[Updated on: Fri, 06 October 2006 15:27]

Report message to a moderator

Re: Which is the best cursor loop for ETL with large no of columns [message #196723 is a reply to message #196722] Fri, 06 October 2006 15:31 Go to previous messageGo to next message
mohapatra
Messages: 24
Registered: August 2006
Location: New Jersey
Junior Member
THnx for the suggestion . But in SQL where you will handle exceptions ?
Re: Which is the best cursor loop for ETL with large no of columns [message #196725 is a reply to message #196723] Fri, 06 October 2006 15:47 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Once again: I don't know the way you want to validate those values; perhaps (or, better said, probably) it can't be done in SQL. A quick example could, though, help me imagine what you have in mind.

Is it, for example, checking whether a column value is a number, or a character, etc.? If such a test is to be done upon a few columns, you could create a function which would return true or false - such a thing could speed things up - you wouldn't have to code the same thing for each column that needs to be validated.

Your first and second cursor loop basically do the same thing. The main difference is that the first option is easier to maintain - you don't even have to declare a cursor - put it directly into the FOR statememnt. No need to open it, fetch, care about end of data set and close it - Oracle does everything for you.
Re: Which is the best cursor loop for ETL with large no of columns [message #196726 is a reply to message #196725] Fri, 06 October 2006 15:56 Go to previous messageGo to next message
mohapatra
Messages: 24
Registered: August 2006
Location: New Jersey
Junior Member
Thanks again.
Yes to validate i have the functions...
But my point was that .. To use bulk collect .
As 2 tables dont match in column numbers , how can i use the bulk collect.

Lets say ...

Table T1 (col1 , col2,col3)
insert/update to -> Table T2 (col1, col2)
Here if i had to use Bulk collect for
- validating each column using functions and rejected ones , insert to table invalid_t1_recs .
- Then in another pl/sql program , i intend to insert the remaining valid rows of T1 into T2 .

Now if you have any better examples. you can plz share.

I ve already written it and its kinda working.But teh issue is i had to delare all 3column as arrays .Its not good for maintenance .Simple cursor was good.

Bottm line .. give some examples of Bulk collect and Bulk update
when source and destination tables have different columns.


Re: Which is the best cursor loop for ETL with large no of columns [message #196747 is a reply to message #196726] Sat, 07 October 2006 00:19 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
If you want to use PL/SQL, you can Bulk Collect into one Nested Table, use PL/SQL commands to loop through and load a second Nested Table that is based on the target, and then FORALL loop INSERT into the target.

You can use pure SQL in 10g R2 if you code all of your validation conditions as constraints. A new feature in this version allows you to write exceptions to another table.

Ross Leishman
Re: Which is the best cursor loop for ETL with large no of columns [message #196750 is a reply to message #196747] Sat, 07 October 2006 00:44 Go to previous messageGo to next message
mohapatra
Messages: 24
Registered: August 2006
Location: New Jersey
Junior Member
Can yu give a small example of what ever you said.
u can base ur answer on my example given earlier.

secondly in forall , u cant use loop (i think).
so if you want to redirect all rows that r rejected during insert , it cant be done
U have to use FOR LOOP END LOOP;
Re: Which is the best cursor loop for ETL with large no of columns [message #196872 is a reply to message #196750] Sun, 08 October 2006 22:15 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
There is plenty of info available in the doco.

Ross Leishman
Previous Topic: how to retrieve attributes in the starts with of the connect by
Next Topic: writting to a text file
Goto Forum:
  


Current Time: Sun Dec 04 02:17:03 CST 2016

Total time taken to generate the page: 0.14751 seconds