Discussions
Categories
- 196.8K All Categories
- 2.2K Data
- 239 Big Data Appliance
- 1.9K Data Science
- 450.3K Databases
- 221.7K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 550 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 544 SQLcl
- 4K SQL Developer Data Modeler
- 187K SQL & PL/SQL
- 21.3K SQL Developer
- 295.8K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.5K Development Tools
- 107 DevOps
- 3.1K QA/Testing
- 646K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 155 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 18 Java Essentials
- 160 Java 8 Questions
- 86K Java Programming
- 80 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 204 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 439 LiveLabs
- 38 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 171 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 232 Portuguese
Deletion on more than 7m records

Hi All,
I have a requirement where i need to drop somewhere around 7.2 millions records based on one particular condition which is mentioned below in the cursor, please suggest if there is any better way to do it.
set timing on;
set serveroutput on;
DECLARE
CURSOR c1 IS SELECT dstr_alt_id FROM SEODS01.DSTR_ALT_ID DST
WHERE length(DST.dstr_alt_id)=27;
v_cmit_nbr NUMBER(8):=50000;
TYPE ARRAY IS TABLE OF c1%ROWTYPE;
v_stg_array ARRAY;
tot_cnt number;
BEGIN
---------27 length dstr_alt_id Record Deletion started--------------------------------
DBMS_OUTPUT.PUT_LINE('Deletion of records where length of dstr_alt_id is 27 in DSTR ALT ID table started:');
OPEN c1;
LOOP
FETCH C1 BULK COLLECT INTO V_STG_ARRAY LIMIT v_cmit_nbr;
DBMS_OUTPUT.PUT_LINE('Deleting ' ||V_STG_ARRAY.count|| ' records');
FORALL i IN 1..v_stg_array.COUNT
DELETE FROM SEODS01.DSTR_ALT_ID WHERE dstr_alt_id=v_stg_array(i).dstr_alt_id;
COMMIT;
EXIT WHEN c1%NOTFOUND;
END LOOP;
CLOSE C1;
DBMS_OUTPUT.PUT_LINE('Deletion of records where length of dstr_alt_id is 27 in DSTR ALT ID table started completed: ');
---------27 length dstr_alt_id Record Deletion completed------------------------------
Comments
-
Plain
DELETE EODS01.DSTR_ALT_ID WHERE length(DST.dstr_alt_id)=27 /
will be faster that PL/SQL. If you don't have enough UNDO you could use
DECLARE V_CNT NUMBER := 50000; -- change it to desired value BEGIN LOOP DELETE EODS01.DSTR_ALT_ID WHERE length(DST.dstr_alt_id)=27 AND ROWNUM <= V_CNT; EXIT WHEN SQL%ROWCOUNT < V_CNT; END LOOP: END; /
However there might be a faster way. You are deleting 7.2 milliom rows. How many rows will be left in the table. If it is less than number of deleted rows you could:
CREATE TABLE SEODS01.DSTR_ALT_ID_TEMP AS SELECT dstr_alt_id FROM SEODS01.DSTR_ALT_ID DST WHERE nvl(length(DST.dstr_alt_id),0) != 27 / TRUNCATE TABLE SEODS01.DSTR_ALT_ID / INSERT INTO SEODS01.DSTR_ALT_ID SELECT * FROM SEODS01.DSTR_ALT_ID_TEMP / DROP TABLE SEODS01.DSTR_ALT_ID_TEMP PURGE /
You could make it even faster if you:
CREATE TABLE SEODS01.DSTR_ALT_ID_TEMP -- add same table properties as SEODS01.DSTR_ALT_ID has (e.g. tablespace, partitioning, etc.) AS SELECT dstr_alt_id FROM SEODS01.DSTR_ALT_ID DST WHERE nvl(length(DST.dstr_alt_id),0) != 27 / DROP TABLE SEODS01.DSTR_ALT_ID PURGE / ALTER TABLE SEODS01.DSTR_ALT_ID_TEMP RENAME TO SEODS01.DSTR_ALT_ID / -- now you need to to recreate table SEODS01.DSTR_ALT_ID constraints, indexes and grants and recompilE invalid objects.
SY.
-
I agree with Solomon. Performance-wise, SQL is faster than PL/SQL. And to delete a large percentage of rows from a table, the CTAS (CREATE TABLE AS SELECT) method is the most effective.