Home » RDBMS Server » Performance Tuning » Bulk delete
Bulk delete [message #169111] Tue, 25 April 2006 02:36 Go to next message
murtuja_oracle
Messages: 5
Registered: December 2005
Junior Member
Hello friends

I want syntax (sample procedure code) for bulk delete.I m inserting data using bulk collection and Forall Loop.

Can u provide me syntax (sample procedure code) for bulk delete.

Note:I hv to delete very less percentage of rows so Truncate
and create table as select is not useful for me.

Below procedure is for Bulk insert and it works fine:

CREATE OR REPLACE PROCEDURE fast_insert
AS
vCount NUMBER;
vSysdate DATE;
TYPE books_aat
IS TABLE OF OLD_CARDIO_SET%ROWTYPE
INDEX BY PLS_INTEGER;
books books_aat;
BEGIN

SELECT /*+ rule */
d.*
BULK COLLECT INTO books
FROM
CARDIO_WORKOUT a,
CARDIO_ACTIVITY b,
CARDIO_ACTIVITY_CHANGED c,
OLD_CARDIO_SET d
WHERE a.workout_nr = b.workout_nr
AND b.activity_nr = c.new_activity_nr
AND c.change_nr = d.change_nr
AND a.person_nr =123456;
FORALL book_index
IN books.FIRST .. books.LAST
INSERT INTO OLD_CARDIO_SET
VALUES books(book_index);
vCount := SQL%rowcount;
END;
/


Summary:

I want to delete records by using Bulk Collection


Re: Bulk delete [message #169113 is a reply to message #169111] Tue, 25 April 2006 02:52 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Why use pl/sql?

delete <table> where <where-clause> 
Re: Bulk delete [message #169121 is a reply to message #169111] Tue, 25 April 2006 03:26 Go to previous message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
Took about 45 seconds to do a search on Google:

Oracle "Bulk Delete."

The first one of the 840 results was
http://asktom.oracle.com/pls/ask/f?p=4950:8:4342812905485134667::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:5033906925164.

Which pretty much answers your question exactly. (Including the point that Frank makes.

Jim
Previous Topic: use HASH instead of index range scan,why the CBO choose the wrong plan?!!
Next Topic: Check My Query Plz
Goto Forum:
  


Current Time: Mon May 27 04:10:34 CDT 2024