-- 比較使用BULK COLLECT INTO以及既有Cursor的執行效能
-- 建立測試Tables
SQL>CREATE TABLE MY_ALL_OBJECTS
AS SELECT * FROM ALL_OBJECTS;
SQL>CREATE TABLE RESULT_OBJECTS
AS SELECT * FROM MY_ALL_OBJECTS WHERE 1 = 0;
SQL>SELECT COUNT(*) FROM MY_ALL_OBJECTS;
COUNT(*)
------------
40997
-- 建立測試Procedures BULK_TEST
CREATE OR REPLACE PROCEDURE BULK_TEST (p_array_size IN PLS_INTEGER DEFAULT 100)
IS
TYPE ARRAY IS TABLE OF my_all_objects%ROWTYPE;
l_data ARRAY;
CURSOR OBJ_CUR IS SELECT * FROM my_all_objects;
BEGIN
OPEN OBJ_CUR;
LOOP
FETCH OBJ_CUR BULK COLLECT INTO l_data LIMIT p_array_size;
FORALL i IN 1..l_data.COUNT
INSERT INTO RESULT_OBJECTS VALUES l_data(i);
EXIT WHEN l_data.COUNT = 0;
END LOOP;
CLOSE OBJ_CUR;
COMMIT;
END BULK_TEST;
-- 建立測試Procedures BULK_TEST2用既有CURSOR語法
CREATE OR REPLACE PROCEDURE BULK_TEST2
IS
vOWNER my_all_objects.OWNER%TYPE;
vOBJECT_NAME my_all_objects.OBJECT_NAME%TYPE;
vSUBOBJECT_NAME my_all_objects.SUBOBJECT_NAME%TYPE;
vOBJECT_ID my_all_objects.OBJECT_ID%TYPE;
vDATA_OBJECT_ID my_all_objects.DATA_OBJECT_ID%TYPE;
vOBJECT_TYPE my_all_objects.OBJECT_TYPE%TYPE;
vCREATED my_all_objects.CREATED%TYPE;
vLAST_DDL_TIME my_all_objects.LAST_DDL_TIME%TYPE;
vTIMESTAMP my_all_objects.TIMESTAMP%TYPE;
vSTATUS my_all_objects.STATUS%TYPE;
vTEMPORARY my_all_objects.TEMPORARY%TYPE;
vGENERATED my_all_objects.GENERATED%TYPE;
vSECONDARY my_all_objects.SECONDARY%TYPE;
CURSOR OBJ_CUR IS SELECT * FROM my_all_objects;
BEGIN
OPEN OBJ_CUR;
LOOP
FETCH OBJ_CUR INTO vOwner,vObject_name,vSubObject_name,
vOBJECT_ID,vDATA_OBJECT_ID,vOBJECT_TYPE,
vCREATED,vLAST_DDL_TIME,vTIMESTAMP,
vSTATUS,vTEMPORARY,vGENERATED,vSECONDARY;
EXIT WHEN OBJ_CUR%NOTFOUND;
INSERT INTO RESULT_OBJECTS
VALUES (vOwner,vObject_name,vSubObject_name,vOBJECT_ID,vDATA_OBJECT_ID,
vOBJECT_TYPE,vCREATED,vLAST_DDL_TIME,vTIMESTAMP,vSTATUS,
vTEMPORARY,vGENERATED,vSECONDARY);
END LOOP;
CLOSE OBJ_CUR;
COMMIT;
END BULK_TEST2;
--將測試程式包在bulk.sql，內容如下
select current_timestamp from dual;
exec BULK_TEST;
select current_timestamp from dual;
select count(*) from RESULT_OBJECTS;
--執行
SQL>@c:\bulk.sql;
--測試執行時間結果:
CURRENT_TIMESTAMP
-------------------------------------
02-7月 -08 12.29.43.612000 下午 +08:00
已順利完成 PL/SQL 程序.
CURRENT_TIMESTAMP
-------------------------------------
02-7月 -08 12.29.44.112000 下午 +08:00
COUNT(*)
----------
40997
--將測試程式同樣包在bulk.sql，內容如下
select current_timestamp from dual;
exec BULK_TEST2;
select current_timestamp from dual;
select count(*) from RESULT_OBJECTS;
--執行
SQL>@c:\bulk.sql;
--測試執行時間結果:
CURRENT_TIMESTAMP
-------------------------------------
02-7月 -08 12.29.44.453000 下午 +08:00
已順利完成 PL/SQL 程序.
CURRENT_TIMESTAMP
-------------------------------------
02-7月 -08 12.29.47.056000 下午 +08:00
COUNT(*)
----------
40997
--使用舊有的cursor語法執行時間明顯慢了幾秒
※ Bulk binds reduce the number of context switches between the PL/SQL engine and the database engine
※ The tradeoff with BULK COLLECT, like so many other performance-enhancing features, is "run faster but consume more memory." - By Steven Feuerstein.
http://www.oracle.com/technology/oramag/oracle/08-mar/o28plsql.html
