1748031 Members
5301 Online
108757 Solutions
New Discussion юеВ

Re: plain sql or plsql?

 
SOLVED
Go to solution
Gyankr
Frequent Advisor

plain sql or plsql?

Hi,

Consider this scenario.

I want to insert data (about 1 million records) of Table A into Table B in a database (locally).

i am thinking of 2 options:

1) Use only sql- insert into Table B (select * from table A);

2) Use cursor - cursor ins_cursor is select * from table A and then use the cursor to insert into Table B.

Which option do you think is good from performance point of view ?

First impression would be to use option 1,but what if i want to find out the number of records inserted ? Can this be done without pl-sql?


Regards,
Gyan
4 REPLIES 4
TwoProc
Honored Contributor

Re: plain sql or plsql?

Option 1) should be faster... but... since you're doing a million rows, it pretty much precludes you from getting a commit somewhere in there, unless you've got loads of undo.

Option 2) should be slower - but - this will give you more control of the load and how it goes. It also gives you the ability to save rollback by doing a saving (by one of many methods) where you are in the list of work to do, doing a commit, releasing the cursors (and thus, finally releasing all of the rollback (undo). Then you can restart.
This method only works if you have a method to determine which records have been processed, and which have not. For this I usually make a temporary table of the unique primary keys of the table I'm moving, if that table has a pk that can be formed, or, at least a unique combination to every row. Otherwise, a commit across fetches does no good except update the redo logs, but you won't release rollback(undo) until you've released the cursor.

The easiest thing to do would be simply to do option 1). If it works without running out of undo(rollback) with a "Snapshot too old" error - then just count the number of rows in the new table after you're done to make sure you've got all the records you thought you should. HOWEVER, if this is process that needs to run routinely (not by hand) and unattended, and robustly - well option two is the best way to go.

The answer is "it depends" and that is based on what you're using this for, and what your expectations for the routine are.
We are the people our parents warned us about --Jimmy Buffett
Gyankr
Frequent Advisor

Re: plain sql or plsql?

Hi ,

This will be running everyday and infact Table A is a staging table before loading into the main table (Table B).

Regards,
Gyan
TwoProc
Honored Contributor
Solution

Re: plain sql or plsql?

Definitely option 2. Use some controls and methods to verify expected number of rows and actual. Use controls to reflect current processing stage(s) and use commit points along the way. Make your code let the cursors close, commit, and then reopen for more processing. Do that every 50K rows or so for starters.
We are the people our parents warned us about --Jimmy Buffett
Sandman!
Honored Contributor

Re: plain sql or plsql?

As mentioned by TwoProc I would also suggest going with option 2 i.e. a PL/SQL cursor or anonymous block. Not only can you get an interim count of the no. of records that have been inserted but you can have a commit marker in your code which frees up the UNDO tablespace. And if your program errors out then you can start exactly where you left off instead of having to kick start the load all over again. Also truncate the staging table after finishing the daily load.

~cheers