通过bulk collect减少loop处理的开销,
使用Bulk Collect提高Oracle查询效率
Oracle8i中首次引入了Bulk Collect特性,该特性可以让我们在PL/SQL中能使用批查询,批查询在某些情况下能显著提高查询效率。
采用bulk collect可以将查询结果一次性地加载到collections中。
而不是通过cursor一条一条地处理。
可以在select into,fetch into,returning into语句使用bulk collect。
注意在使用bulk collect时,所有的into变量都必须是collections.
举几个简单的例子:
--在select into语句中使用bulk collect
DECLARE
TYPE SALLIST IS TABLE OF EMP.SAL%TYPE;
SALS SALLIST;
BEGIN
-- Limit the number of rows to 100.
SELECT SAL BULK COLLECT INTO SALS FROM EMP WHERE ROWNUM <= 100;
-- Retrieve 10% (approximately) of the rows in the table.
SELECT SAL BULK COLLECT INTO SALS FROM EMP SAMPLE 10;
TYPE SALLIST IS TABLE OF EMP.SAL%TYPE;
SALS SALLIST;
BEGIN
-- Limit the number of rows to 100.
SELECT SAL BULK COLLECT INTO SALS FROM EMP WHERE ROWNUM <= 100;
-- Retrieve 10% (approximately) of the rows in the table.
SELECT SAL BULK COLLECT INTO SALS FROM EMP SAMPLE 10;
END;
/
/
--在fetch into中使用bulk collect
DECLARE TYPE DEPTRECTAB IS TABLE OF DEPT%ROWTYPE;
DEPT_RECS DEPTRECTAB;
CURSOR C1 IS
SELECT DEPTNO, DNAME, LOC FROM DEPT WHERE DEPTNO > 10;
BEGIN
OPEN C1;
FETCH C1 BULK COLLECT
INTO DEPT_RECS;
END;
/
DEPT_RECS DEPTRECTAB;
CURSOR C1 IS
SELECT DEPTNO, DNAME, LOC FROM DEPT WHERE DEPTNO > 10;
BEGIN
OPEN C1;
FETCH C1 BULK COLLECT
INTO DEPT_RECS;
END;
/
--在returning into中使用bulk collect
CREATE TABLE EMP2 AS
SELECT * FROM EMPLOYEES;
DECLARE
TYPE NUMLIST IS TABLE OF EMPLOYEES.EMPLOYEE_ID%TYPE;
ENUMS NUMLIST;
TYPE NAMELIST IS TABLE OF EMPLOYEES.LAST_NAME%TYPE;
NA MES NAMELIST;
BEGIN
DELETE FROM EMP2
WHERE DEPARTMENT_ID = 30
RETURNING EMPLOYEE_ID, LAST_NAME BULK COLLECT INTO ENUMS, NAMES;
DBMS_OUTPUT.PUT_LINE('Deleted ' || SQL%ROWCOUNT || ' rows:');
FOR I IN ENUMS.FIRST .. ENUMS.LAST LOOP
DBMS_OUTPUT.PUT_LINE('Employee #' || ENUMS(I) || ': ' || NAMES(I));
END LOOP;
END;
/ DROP TABLE EMP2;
SELECT * FROM EMPLOYEES;
DECLARE
TYPE NUMLIST IS TABLE OF EMPLOYEES.EMPLOYEE_ID%TYPE;
ENUMS NUMLIST;
TYPE NAMELIST IS TABLE OF EMPLOYEES.LAST_NAME%TYPE;
NA MES NAMELIST;
BEGIN
DELETE FROM EMP2
WHERE DEPARTMENT_ID = 30
RETURNING EMPLOYEE_ID, LAST_NAME BULK COLLECT INTO ENUMS, NAMES;
DBMS_OUTPUT.PUT_LINE('Deleted ' || SQL%ROWCOUNT || ' rows:');
FOR I IN ENUMS.FIRST .. ENUMS.LAST LOOP
DBMS_OUTPUT.PUT_LINE('Employee #' || ENUMS(I) || ': ' || NAMES(I));
END LOOP;
END;
/ DROP TABLE EMP2;
通过上面的测试和分析,我们可以看到Bulk
Collect批查询在某种程度上可以提高查询效率,它首先将所需数据读入内存,然后再统计分析,这样就可以提高查询效率。但是,如果Oracle数据库的内存较小,Shared
Pool Size不足以保存Bulk Collect批查询结果,那么该方法需要将Bulk Collect的集合结果保存在磁盘上,在这种情况下,Bulk
Collect方法的效率反而不如其他两种方法,有兴趣的读者可以进一步测试。
Collect批查询在某种程度上可以提高查询效率,它首先将所需数据读入内存,然后再统计分析,这样就可以提高查询效率。但是,如果Oracle数据库的内存较小,Shared
Pool Size不足以保存Bulk Collect批查询结果,那么该方法需要将Bulk Collect的集合结果保存在磁盘上,在这种情况下,Bulk
Collect方法的效率反而不如其他两种方法,有兴趣的读者可以进一步测试。
另外,除了Bulk Collect批查询外,我们还可以使用FORALL语句来实现批插入、删除和更新,这在大批量数据操作时可以显著提高执行效率。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/24627116/viewspace-754489/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/24627116/viewspace-754489/