bulk collect & delete [message #242141] |
Thu, 31 May 2007 14:41 |
jinga
Messages: 116 Registered: January 2003
|
Senior Member |
|
|
CREATE OR REPLACE PROCEDURE "SP_testproc" (
--VARIABLES
IRM_N IN VARCHAR2,
IRM_N_1 IN VARCHAR2,
IRM_N_2 IN VARCHAR2,
IRM_N_3 IN VARCHAR2,
IRM_N_4 IN VARCHAR2,
IRM_N_5 IN VARCHAR2
)AS
TYPE test1_tab IS TABLE OF TEST_ANU%ROWTYPE;
t_tab test1_tab;
BEGIN
SELECT * BULK COLLECT INTO t_tab
FROM TEST_ANU
WHERE INDEX_RUN_IRM = IRM_N;
FORALL i IN t_tab.first .. t_tab.last
DELETE FROM TEST_ANU
WHERE ROW = t_TAB(i);
COMMIT;
END;
I have to delete records from table test_anu. It has close to 10 million and from that i needc to delete 1.5 million record. Regular delete seems to take a lot of time,so i tried to write with bulk collect. But i am getting error while compiling the proc...
invalid identifier - row. i know there is mitake in the way i have used the collection. could any one help me.
Anu
|
|
|
|
Re: bulk collect & delete [message #242145 is a reply to message #242141] |
Thu, 31 May 2007 14:57 |
jinga
Messages: 116 Registered: January 2003
|
Senior Member |
|
|
so, bulk collect and delete does not help with performance??
I have just put only few lines from the big procedure.. other paramters are used in the procedure..
so what can i do to reduce the time it takes to delete 1.5 million records.
|
|
|
|
|
|