HOW TO INCREASE PERFORMANCE OF DELETE INSERT UPDATE IN ORACLE

HOW TO INCREASE PERFORMANCE OF DELETE INSERT UPDATE IN ORACLE

-----------Delete-----------

CREATE OR REPLACE PROCEDURE regtest3 IS
CURSOR s_cur IS
SELECT *
FROM STFETRAN
WHERE BRANCE='0002';

TYPE numtab IS TABLE OF regtab.namespace%TYPE
INDEX BY PLS_INTEGER;

s_array numtab;
BEGIN
OPEN s_cur;

FETCH s_cur BULK COLLECT INTO s_array LIMIT 500;

FOR i IN 1 .. s_array.LAST LOOP
DELETE FROM regtab
WHERE namespace = s_array (i);
END LOOP;

CLOSE s_cur;

COMMIT;
END regtest3;


CREATE OR REPLACE PROCEDURE comptest3 IS
CURSOR s_cur IS
SELECT DISTINCT namespace
FROM comptab;

TYPE numtab IS TABLE OF comptab.namespace%TYPE
INDEX BY PLS_INTEGER;

s_array numtab;
BEGIN
OPEN s_cur;

FETCH s_cur
BULK COLLECT INTO s_array LIMIT 500;

FOR i IN 1 .. s_array.COUNT LOOP
DELETE FROM comptab
WHERE namespace = s_array (i);
END LOOP;

CLOSE s_cur;

COMMIT;
END comptest3;

-------------------------------INSERT --------------

CREATE TABLE regtab
TABLESPACE uwdata AS
SELECT * FROM all_objects
WHERE 1=2;
CREATE TABLE comptab
TABLESPACE uwdata COMPRESS AS
SELECT * FROM all_objects
WHERE 1=2;
SELECT table_name, tablespace_name, compression
FROM user_tables
WHERE table_name IN ('REGTAB', 'COMPTAB');
CREATE OR REPLACE PROCEDURE regtest1 IS
BEGIN
FOR i IN 1..10 LOOP
INSERT INTO regtab
SELECT * FROM all_objects
WHERE SUBSTR(object_name,1,1) BETWEEN 'A' AND
'WZZZZZ';
END LOOP;
COMMIT;
END regtest1;
/
CREATE OR REPLACE PROCEDURE comptest1 IS
BEGIN
FOR i IN 1..10 LOOP
INSERT INTO comptab
SELECT * FROM all_objects
WHERE SUBSTR(object_name,1,1) BETWEEN 'A' AND
'WZZZZZ';
END LOOP;
COMMIT;
END comptest1;
/

-----------------UPDATE--------------------

SELECT MIN(object_id), MAX(object_id)
FROM all_objects
WHERE SUBSTR(object_name,1,1) BETWEEN 'A' AND
'WZZZZZ';
CREATE OR REPLACE PROCEDURE regtest2 IS
j PLS_INTEGER;
BEGIN
FOR i IN 1..1000 LOOP
j := TRUNC(dbms_random.value(2, 80984));
UPDATE regtab
SET namespace = i
WHERE object_id = j;
END LOOP;
COMMIT;
END regtest2;
/
CREATE OR REPLACE PROCEDURE comptest2 IS
j PLS_INTEGER;
BEGIN
FOR i IN 1..1000 LOOP
j := TRUNC(dbms_random.value(2, 80984));
UPDATE comptab
SET namespace = i
WHERE object_id = j;
END LOOP;
COMMIT;
END comptest2;
/