Hi All,
I am executing the script with 100 loops and it takes more time. I found the long executing statement, which runs for 60 seconds from each loop.
INSERT INTO DOC_MAIN
SELECT DISTINCT '12334', T_DOCID, T_DOCTYPE, ''Audit'' FROM TEMP_DOC WHERE NOT exists
( SELECT 1 FROM DOC_MAIN WHERE ID = '12334' AND DOCID = T_DOCID);
1 million records are fetched in the above select. DOC_MAIN also contains more than 10 million records.
I have tried the BULK collect too. But it takes 50 sec
declare
type ttable is table of DOC_MAIN%rowtype;
ottable ttable;
begin
SELECT DISTINCT '12334', T_DOCID, T_DOCTYPE, 'Audit' bulk collect into ottable FROM TEMP_DOC WHERE NOT exists
( SELECT 1 FROM DOC_MAIN WHERE ID = '12334' AND DOCID = T_DOCID);
forall x in ottable.first..ottable.last
insert into DOC_MAIN values ottable(x);
commit;
end;
If I am wrong please correct me.
Is there any way to tune this insert into statement.
Thanks in Advance.
Su.gi