通过bulk collect减少loop处理的开销
采用bulk collect可以将查询结果一次性地加载到collections中。
而不是通过cursor一条一条地处理。
可以在select into,fetch into,returning into语句使用bulk collect。
注意在使用bulk collect时,所有的into变量都必须是collections.
-------------------------------------------------------------------------------------------
采用bulk collect可以将查询结果一次性地加载到collections中。
而不是通过cursor一条一条地处理。
可以在select into,fetch into,returning into语句使用bulk collect。
注意在使用bulk collect时,所有的into变量都必须是collections.
-------------------------------------------------------------------------------------------
简单举个例:
CREATE OR REPLACE TYPE NODETYPE AS OBJECT
(
SEQ NUMBER(10),
NODE_CODE VARCHAR2(30)
)
/
(
SEQ NUMBER(10),
NODE_CODE VARCHAR2(30)
)
/
------------------------------
CREATE OR REPLACE TYPE NODETYPE_LIST AS TABLE OF NODETYPE
/
/
-------------------------------
FUNCTION GET_QUARTER_OF_MONTH_LIST(P_I_DATE IN VARCHAR2)
RETURN NODETYPE_LIST IS
V_YEAR VARCHAR2(4);
V_MONTH VARCHAR2(2);
RESULT NODETYPE_LIST;
BEGIN
V_YEAR := SUBSTR(P_I_DATE, 1, 4);
V_MONTH := SUBSTR(P_I_DATE, 5, 2);
RESULT := NODETYPE_LIST();
CASE
WHEN V_MONTH IN ('01', '02', '03') THEN
SELECT NODETYPE(ROWNUM,
TO_CHAR(LAST_DAY(TO_DATE(F_DATE, 'YYYYMMDD')),
'YYYYMMDD')) BULK COLLECT
INTO RESULT
FROM (SELECT V_YEAR || '01' || '01' AS F_DATE
FROM DUAL
UNION ALL
SELECT V_YEAR || '02' || '01' AS F_DATE
FROM DUAL
UNION ALL
SELECT V_YEAR || '03' || '01' AS F_DATE FROM DUAL);
WHEN V_MONTH IN ('04', '05', '06') THEN
SELECT NODETYPE(ROWNUM,
TO_CHAR(LAST_DAY(TO_DATE(F_DATE, 'YYYYMMDD')),
'YYYYMMDD')) BULK COLLECT
INTO RESULT
FROM (SELECT V_YEAR || '04' || '01' AS F_DATE
FROM DUAL
UNION ALL
SELECT V_YEAR || '05' || '01' AS F_DATE
FROM DUAL
UNION ALL
SELECT V_YEAR || '06' || '01' AS F_DATE FROM DUAL);
WHEN V_MONTH IN ('07', '08', '09') THEN
SELECT NODETYPE(ROWNUM,
TO_CHAR(LAST_DAY(TO_DATE(F_DATE, 'YYYYMMDD')),
'YYYYMMDD')) BULK COLLECT
INTO RESULT
FROM (SELECT V_YEAR || '07' || '01' AS F_DATE
FROM DUAL
UNION ALL
SELECT V_YEAR || '08' || '01' AS F_DATE
FROM DUAL
UNION ALL
SELECT V_YEAR || '09' || '01' AS F_DATE FROM DUAL);
WHEN V_MONTH IN ('10', '11', '12') THEN
SELECT NODETYPE(ROWNUM,
TO_CHAR(LAST_DAY(TO_DATE(F_DATE, 'YYYYMMDD')),
'YYYYMMDD')) BULK COLLECT
INTO RESULT
FROM (SELECT V_YEAR || '10' || '01' AS F_DATE
FROM DUAL
UNION ALL
SELECT V_YEAR || '11' || '01' AS F_DATE
FROM DUAL
UNION ALL
SELECT V_YEAR || '12' || '01' AS F_DATE FROM DUAL);
END CASE;
RETURN RESULT;
END;
RETURN NODETYPE_LIST IS
V_YEAR VARCHAR2(4);
V_MONTH VARCHAR2(2);
RESULT NODETYPE_LIST;
BEGIN
V_YEAR := SUBSTR(P_I_DATE, 1, 4);
V_MONTH := SUBSTR(P_I_DATE, 5, 2);
RESULT := NODETYPE_LIST();
CASE
WHEN V_MONTH IN ('01', '02', '03') THEN
SELECT NODETYPE(ROWNUM,
TO_CHAR(LAST_DAY(TO_DATE(F_DATE, 'YYYYMMDD')),
'YYYYMMDD')) BULK COLLECT
INTO RESULT
FROM (SELECT V_YEAR || '01' || '01' AS F_DATE
FROM DUAL
UNION ALL
SELECT V_YEAR || '02' || '01' AS F_DATE
FROM DUAL
UNION ALL
SELECT V_YEAR || '03' || '01' AS F_DATE FROM DUAL);
WHEN V_MONTH IN ('04', '05', '06') THEN
SELECT NODETYPE(ROWNUM,
TO_CHAR(LAST_DAY(TO_DATE(F_DATE, 'YYYYMMDD')),
'YYYYMMDD')) BULK COLLECT
INTO RESULT
FROM (SELECT V_YEAR || '04' || '01' AS F_DATE
FROM DUAL
UNION ALL
SELECT V_YEAR || '05' || '01' AS F_DATE
FROM DUAL
UNION ALL
SELECT V_YEAR || '06' || '01' AS F_DATE FROM DUAL);
WHEN V_MONTH IN ('07', '08', '09') THEN
SELECT NODETYPE(ROWNUM,
TO_CHAR(LAST_DAY(TO_DATE(F_DATE, 'YYYYMMDD')),
'YYYYMMDD')) BULK COLLECT
INTO RESULT
FROM (SELECT V_YEAR || '07' || '01' AS F_DATE
FROM DUAL
UNION ALL
SELECT V_YEAR || '08' || '01' AS F_DATE
FROM DUAL
UNION ALL
SELECT V_YEAR || '09' || '01' AS F_DATE FROM DUAL);
WHEN V_MONTH IN ('10', '11', '12') THEN
SELECT NODETYPE(ROWNUM,
TO_CHAR(LAST_DAY(TO_DATE(F_DATE, 'YYYYMMDD')),
'YYYYMMDD')) BULK COLLECT
INTO RESULT
FROM (SELECT V_YEAR || '10' || '01' AS F_DATE
FROM DUAL
UNION ALL
SELECT V_YEAR || '11' || '01' AS F_DATE
FROM DUAL
UNION ALL
SELECT V_YEAR || '12' || '01' AS F_DATE FROM DUAL);
END CASE;
RETURN RESULT;
END;
/
-----------查询结果
SELECT NODE_CODE
FROM TABLE(GET_QUARTER_OF_MONTH_LIST(‘20121015’));
FROM TABLE(GET_QUARTER_OF_MONTH_LIST(‘20121015’));
---------结果为:
20121031
20121130
20121231
--------------------------------------------------------------------------
通过上面的测试和分析,我们可以看到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/26423870/viewspace-754634/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/26423870/viewspace-754634/