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;
/
Halim is a Sr. Database Engineer/Data Architect (in Atlanta, USA) who is an Oracle certified (OCP) DBA, (OCP) Developer, Certified Cloud Architect Professional as well as OCI Autonomous DB specialist with extensive expertise in Database design, configuration, tuning, capacity planning, RAC, DG, Scripting, Python, PL/SQL etc. He achieved 16th position in worldwide first-ever PL/SQL Challenge cup playoff- http://plsql-challenge.blogspot.com/2010/07/winners-of-first-plsql-challenge.html
Subscribe to:
Post Comments (Atom)
My Blog List
-
-
Index Usage – 21 week ago
-
-
-
-
-
-
-
-
Oracle Cloud & Third party tools3 years ago
-
-
-
Moving Sideways7 years ago
-
Upcoming Events...10 years ago
-
No comments:
Post a Comment