Performance question

[rksmageri]

Hi Gurus,

In many books you can read that an "insert into" statement is much moore effective
than an "for loop" construction. In my case it isn't. I have try the queries below on
both 8.1.7.3 (AIX) and 9.2.0.1.0 (AIX) and get the same result, the for loop is much
moore faster.

insert into test_tab(ag_no,agobj_no)
select ao.ag_no,ao.agobj_no
from ag_obj ao,
agreement a
where a.ag_flag = 'N'
and a.ag_no = ao.ag_no


for rec in (select ao.ag_no,ao.agobj_no
from ag_obj ao,
agreement a
where a.ag_flag = 'N'
and a.ag_no = ao.ag_no
)

loop

insert into test_tab.....;
end loop;


Any suggestions?

Thanks

[gandolf989]

There is something else going on here. What is the explain plan for the query? What are the timings for each method? Do you have an insert trigger on the test_tab table? Something is causing this to not look right. The SQL INSERT should always be faster. If there is a lot of sorting, one single large insert might take a lot of resources compared to many smaller inserts. But the amount of overhead to resolve the query could be minimized with some tuning, thus making the SQL INSERT faster.