Home » SQL & PL/SQL » SQL & PL/SQL » bulk collect & delete
bulk collect & delete [message #242141] Thu, 31 May 2007 14:41 Go to next message
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 #242143 is a reply to message #242141] Thu, 31 May 2007 14:47 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
delete test_anu WHERE INDEX_RUN_IRM = IRM_N;

Here's the query. No PL/SQL needed.
PL/SQL will take much more longer.

Btw, what is the use of IRM_N_% parameters?

Regards
Michel


Re: bulk collect & delete [message #242145 is a reply to message #242141] Thu, 31 May 2007 14:57 Go to previous messageGo to next message
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.
Re: bulk collect & delete [message #242148 is a reply to message #242145] Thu, 31 May 2007 15:03 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Do you have statistics on your table?
Do you have index on INDEX_RUN_IRM?
If yes do you have statistics on it?

Forgot: what is your Oracle version?

Regards
Michel

[Updated on: Thu, 31 May 2007 15:03]

Report message to a moderator

Re: bulk collect & delete [message #242153 is a reply to message #242141] Thu, 31 May 2007 15:39 Go to previous messageGo to next message
jinga
Messages: 116
Registered: January 2003
Senior Member
currently at 9.2.

but moving to 10g in about a month.
Re: bulk collect & delete [message #242208 is a reply to message #242153] Fri, 01 June 2007 01:22 Go to previous message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
And what about the other questions?

Regards
Michel
Previous Topic: Is there such thing as update all??
Next Topic: syntax doubt
Goto Forum:
  


Current Time: Thu Dec 05 08:25:52 CST 2024