Problem in inserting large data

[manjunathk]

Hi Friends,
I am trying to insert 1 million records using the following statement:

insert into tableA
select col1,col5,col3 from tableB b, tableC c
where b.col4=c.col4;

However as soon as I give the command to execute, the SQL PLUS starts hanging.
Should one take any extra steps/measures when doing large insertions?
I am running 8.0.5 on a deck alpha machine
Thanks
manjunath

[pwoneill]

How large is the insertion? You could do it in pl/sql and just commit every 1000 records or so. That would also be a lot nicer to your rollback segments, which might by why sqlplus is hanging. Check the alert log and you might see some unable to extend errors.

In any case here's a sample pl/sql block
<font face="courier">
declare
&nbsp;&nbsp;cursor cur is select col1, col5, col3
&nbsp;&nbsp;from tableB b, tableC
&nbsp;&nbsp;c where b.col4=c.col4;
&nbsp;i NUMBER;
begin
&nbsp;&nbsp;i = 0;
&nbsp;&nbsp;for rec in cur loop
&nbsp;&nbsp;&nbsp;&nbsp;insert into tableA values (rec.col1, rec.col5, rec.col3);
&nbsp;&nbsp;&nbsp;&nbsp;if ( mod(1,1000) == 0 ) then
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;commit;
&nbsp;&nbsp;&nbsp;&nbsp;end if;
&nbsp;&nbsp;&nbsp;&nbsp;i := i+1;
&nbsp;&nbsp;end loop;
&nbsp;&nbsp;commit;
end;

that's off the top of my head so syntax is probablly shaky but you get the idea.

[marist89]

1. Make sure your base query is indexed well.
2. use the APPEND hint (http://technet.oracle.com/docs/products/oracle8i/doc_library/817_doc/server.817/a76992/hints.htm#19039)

[marist89]

OK, so how did you get your code to indent on this Forum?

[manjunathk]

Thanks pwoneill
I ran the following , and still the sql plus hangs.

declare
cursor cur is select col1, col5, col3
from tableB b, tableC
c where b.col4=c.col4;
i NUMBER;
begin
i := 0;
for rec in cur loop
insert into tableA values (rec.col1, rec.col5, rec.col3);
if ( mod(1 ,1000) = 0 ) then
commit;
end if;
i := i+1;
end loop;
commit;
end;

Should I try to assign a bigger rbs?
Presently I have 4 rbs of 4mb each.
Thanks
manjunath

[jmodic]

What do you mean by "SQL*Plus hangs"? If it returns an error then let us know what it is. If it simply does not return control that probably mean it is still working. Inserting large dataset might take a lot of time, maybe you simply are not patient enough.

Connect with another Sql*plus session and select count(*) from tableA. If you realy are commiting periodically then you should be able to find this number increasing in batches of 100.

[manjunathk]

I checked up in task manager. And it says sql plus is not responding.
Thanks
manjunath

[jmodic]

But this does not mean the insertion is not taking place! Sql*Plus passes comand (either a query or a PL/SQL block to Oracle Server and waits there to get the control back. But the database will not return control to it until the whole comand is executed. In your case SQL*Plus will probably respond only after all the rows are inserted into TableA.

[pando]

not respondoing doesnt mean it hangs, do you know how to query v$session_wait? if you know find the sid of the user inserting from v$session and then

select event from v$session_wait
where sid=sid_of_user_inserting

This will show what is going on in that session

[pwoneill]

Spacing with judicious use of nbsp :)

As already pointed out just because it's hanging doesn't mean it's not doing anything. How many rows are being inserted?

<font face="courier">
select count(1) from tableB b, tableC c where b.col4 = c.col4;
</font>;

Did you check your alert logs to see if it's actually an RBS extending problem?