sql - 如何优化使用游标的 PL/SQL 代码
问题描述
我正在尝试优化运行大约 3 小时以获取/更新 280 万条记录的查询。该代码使用游标,我们需要更新整个 280 万条记录集。我尝试使用批量收集修改查询,但我认为它没有多大帮助。有人可以告诉我我还能做些什么来优化代码。老实说,我认为即使批量收集也不会更好......我正在使用oracle19c
旧代码是:
DECLARE
CURSOR PROVNUMCURSOR
IS
SELECT DISTINCT RCRD_NBR, ID, Entity FROM SPSMDMRW.GTT_SCW_PROFILE_INTRM ;
v_FirstRow SPSMDMRW.GTT_SCW_PROFILE_INTRM.RCRD_NBR%type;
v_ID SPSMDMRW.GTT_SCW_PROFILE_INTRM.ID%type;
v_Entity SPSMDMRW.GTT_SCW_PROFILE_INTRM.Entity%type;
BEGIN
OPEN PROVNUMCURSOR;
--v_FirstRow:=0;v_ID:=0;v_Entity:='0';
FETCH PROVNUMCURSOR
INTO v_FirstRow,
v_ID,
v_Entity;
WHILE PROVNUMCURSOR%FOUND
LOOP
SELECT
CASE
WHEN v_ID = v_EndRow
THEN v_MaxRow
ELSE
(SELECT RCRD_NBR-1 FROM SPSMDMRW.GTT_SCW_PROFILE_INTRM WHERE ID = v_ID+1
)
END
INTO v_LastRow
FROM dual;
IF v_Entity = '3' THEN
UPDATE SPSMDMRW.GTT_SCW_SSB_SC_PDF a
SET ProvNum = LTRIM(RTRIM(TO_CHAR(SUBSTR(Column0, 1, 13))))
||'I'
WHERE RCRD_NBR BETWEEN v_FirstRow AND v_LastRow;
ELSE
IF v_Entity = '4' THEN
UPDATE SPSMDMRW.GTT_SCW_SSB_SC_PDF a
SET ProvNum = LTRIM(RTRIM(TO_CHAR(SUBSTR(Column0, 1, 13))))
|| 'II'
WHERE RCRD_NBR BETWEEN v_FirstRow AND v_LastRow;
ELSE
UPDATE SPSMDMRW.GTT_SCW_SSB_SC_PDF a
SET ProvNum = LTRIM(RTRIM(TO_CHAR(SUBSTR(Column0, 1, 13))))
WHERE RCRD_NBR BETWEEN v_FirstRow AND v_LastRow;
END IF;
END IF;
FETCH PROVNUMCURSOR INTO v_FirstRow, v_ID, v_Entity;
EXIT
WHEN PROVNUMCURSOR%NOTFOUND;
END LOOP;
--END;
CLOSE PROVNUMCURSOR;
END;
我修改的代码如下:
DECLARE
CURSOR PROVNUMCURSOR
IS
SELECT DISTINCT RCRD_NBR,
ID,
Entity
FROM SPSMDMRW.GTT_SCW_PROFILE_INTRM_TEST ;
type v_ID_type
IS
TABLE OF SPSMDMRW.GTT_SCW_PROFILE_INTRM_TEST.ID%type;
type v_Entity_type
IS
TABLE OF SPSMDMRW.GTT_SCW_PROFILE_INTRM_TEST.Entity%type;
type v_FirstRow_type
IS
TABLE OF SPSMDMRW.GTT_SCW_PROFILE_INTRM_TEST.RCRD_NBR%type;
v_FirstRow v_FirstRow_type;
v_ID v_ID_type;
v_Entity v_Entity_type;
c_limit PLS_INTEGER := 100;
BEGIN
OPEN PROVNUMCURSOR;
--v_FirstRow:=0;v_ID:=0;v_Entity:='0';
LOOP
FETCH PROVNUMCURSOR bulk collect INTO v_FirstRow, v_ID,v_Entity LIMIT C_LIMIT;
EXIT
WHEN V_FIRSTROW.COUNT = 0;
--dbms_output.put_line(V_FIRSTROW.COUNT);
FOR INDX IN 1..V_FIRSTROW.COUNT
LOOP
-- dbms_output.put_line(1);
SELECT
CASE
WHEN v_ID(INDX) = v_EndRow
THEN v_MaxRow
ELSE
(SELECT RCRD_NBR-1
FROM SPSMDMRW.GTT_SCW_PROFILE_INTRM_TEST
WHERE ID = v_ID(INDX)+1
)
END
INTO v_LastRow
FROM dual;
IF v_Entity(INDX) = '3' THEN
UPDATE SPSMDMRW.GTT_SCW_SSB_SC_PDF_TEST a
SET ProvNum = LTRIM(RTRIM(TO_CHAR(SUBSTR(Column0, 1, 13))))
||'I'
WHERE RCRD_NBR BETWEEN v_FirstRow(INDX) AND v_LastRow;
ELSIF v_Entity(INDX) = '4' THEN
--DBMS_OUTPUT.PUT_LINE(v_entity || ' First Row '|| v_FirstRow || ' Last row '||v_LastRow);
UPDATE SPSMDMRW.GTT_SCW_SSB_SC_PDF_TEST a
SET ProvNum = LTRIM(RTRIM(TO_CHAR(SUBSTR(Column0, 1, 13))))
|| 'II'
WHERE RCRD_NBR BETWEEN v_FirstRow(INDX) AND v_LastRow;
ELSE
-- DBMS_OUTPUT.PUT_LINE(v_entity || ' First Row '|| v_FirstRow || ' Last row '||v_LastRow);
UPDATE SPSMDMRW.GTT_SCW_SSB_SC_PDF_TEST a
SET ProvNum = LTRIM(RTRIM(TO_CHAR(SUBSTR(Column0, 1, 13))))
WHERE RCRD_NBR BETWEEN v_FirstRow(INDX) AND v_LastRow;
END IF;
END LOOP;
END LOOP;
CLOSE PROVNUMCURSOR;
END;
Sample data:
RCRD_NBR FILE_NM PROVNUM ID ENTITY
225184 sample.txt 1681 2
225241 sample.txt 1682 3
225352 sample.txt 1683 4
225436 sample.txt 1684 5
225493 sample.txt 1685 2
解决方案
没有任何样本数据并且不知道逻辑,很难提供解决方案。我认为这可以通过一个单一的声明来完成,该声明的方向如下:
UPDATE SPSMDMRW.GTT_SCW_SSB_SC_PDF a SET ProvNum =
with t as
(SELECT DISTINCT
RCRD_NBR as FirstRow,
LEAD(RCRD_NBR, 1) -1 OVER (ORDER BY ID) AS LastRow,
CASE Entity
when '3' then TRIM(SUBSTR(Column0, 1, 13))||'I'
when '4' then TRIM(SUBSTR(Column0, 1, 13))||'II'
else TRIM(SUBSTR(Column0, 1, 13))
end case as ProvNum
FROM SPSMDMRW.GTT_SCW_PROFILE_INTRM)
SELECT t.ProvNum
from t
where a.RCRD_NBR BETWEEN FirstRow AND LastRow;
推荐阅读
- r - if_else() `false` 必须是 double 类型,而不是整数 - 在 R 中
- javascript - 使用javascript从右侧删除字符串中的文本
- javascript - 如何在多个 Docker 副本中只运行一次计划代码?
- yii2 - Yii2 将 Asset 分配给特定布局
- c++ - 如何在 Node N-API 中使用第三方 dll、头文件和 lib 文件
- paypal - 使用 java SDK 的 Paypal ExpressCheckout 重试后失败,但向客户收费
- python - 如何在侧面输出中显示python输入代码
- java - 在 Spring 配置或测试中排除或取消 bean
- nginx - Openresty LUA Mysql连接错误
- web - Primeface 6.2 / Java --> 推送功能 / 更新(xhtml 组件)