Using cursors to insert data 1

[Erikxxx]

Hi all,

I've been told that inserting data into a table using a cursor (see example below) is very
inefficient and will take long time to process. I think this is the example that was given.
If this is true, why? and what is a better way
of doing it?

Thanks
E

Example

DECLARE
CURSOR testCur IS select * from emp;
myRec testCur%ROWTYPE;

BEGIN
OPEN testCur;
LOOP
FETCH testCur INTO myRec;
EXIT WHEN testCur%NOTFOUND;
INSERT INTO emp2 VALUES (myRec.empno);
END LOOP;
CLOSE testCur;
END;

 

[SantaMufasa]

Erik,

First of all, all input/output activities against Oracle tables occur as a result of cursors, either explicit (as you have created) or implicit. An example of an implicit cursor is that which results from an SQL command that accomplishes the same result as your PL/SQL code, but without explicit cursors:

INSERT INTO emp2 (empno) SELECT empno from emp;

One test is worth 100 expert opinions.

Apparently, an "expert" told you that INSERTs via a cursor are inefficient. If I were in your position, I would test both methods with SQL*Plus's "SET TIMING ON", which should give you a decent comparison of time consumption when you try each method.

Let us know your findings once you try both methods. (Remember to COMMIT once saving your INSERTs is appropriate.)

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[sem]

Using insert..select is far more efficient because:

1. It uses arrays and doesn't process each row individually
2. It may be parallelized
3. It doesn't cause pl/sql-sql context switching
4. It doesn't require intermediate assignments

But you may observe it only when processing at least thousands of rows


Regards, Dima

http://seminihin.narod.ru

 

[SantaMufasa]

My comments on another thread about Sem apply here, as well:

It's always nice to have your wisdom settle gently upon an obscure technical problem, causing the obscurity to disappear like an early-morning fog lifting from a dewy meadow. I just wish there were not a 9-hour time difference between us so that I wouldn't have to wait so long for your contributions on threads.

Why can't you just stay awake 24x7 and just monitor Tek-Tips issues? Have a Purple Star.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[Erikxxx]

Hi,

Thanks for your replies. Well, I actually came across this question in a job interview I attended the other week. From what I remember this was the example given and the guy said. How can you modify this bit of code to make the the inserts running faster. According to him, he said that this is a very inefficient way and it will take long time to process even with just a few records (a few 100rec). Well, the only way I could think of was to write the select statement directly in the insert statement. However I'm still a bit confused about his question.

Thanks
E

 

[SantaMufasa]

So, Erik, which way, exactly, did he say was "slow" and which way was the "best"? If he asserted that "INSERT...SELECT" is slow, he's nuts...It is perhaps the fastest SQL method to bulk insert rows into a table.

Let us know.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[BJCooperIT]

Nuts? Strong words for you Dave.

The *only* time I have ever seen INSERT...SELECT to be slow, was when the SELECT's Explain Plan showed the SELECT to be inefficient (full table scans, cartesian products, etc.).

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
Author and Sole Proprietor of: Emu Products Plus

 

[SantaMufasa]

Sorry, Barb...you are right...the words were too strong, but I haven't eaten breakfast yet (my cup of ZipFizz for my diet), so I'm a bit "short fused" at the moment . I should have clarified that "all other things being equal (including the execution plan), INSERT...SELECT is faster than an equivalent one-row-at-a-time scheme within a PL/SQL 'FOR' loop."

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.