问题描述
嗨，我有这个查询，大约需要45分钟才能将数据填充到AFS_TABLE中。AFS_TABLE在此插入运行之前被截断，并且它没有任何索引或约束。Oracle版本为11.2.0.4。VW_PAYMENT表是一个非常大的IOT，由大约3.32亿行组成。由于查询的编写方式，一次又一次地访问相同的表。
这是此查询的解释计划。
我试图使用xx作为重写查询 (...) 选择...但这似乎没有多大区别。我没有运行查询很长时间，因为我可以看到更多数量的类似操作，并且成本没有太大差异。
此计划中的A行不仅仅是E行，因此我确实收集了统计信息并运行了查询，但没有任何区别。
因此，就如何重写此查询以提高性能寻求您的帮助。
谢谢 & 致以最诚挚的问候!
-a
INSERT INTO AFS_TABLE
SELECT CM_CASEID CASEKEY,
CM_CIF_ICNO ICNO,
CM_PRODUCT PRODUCT,
CMR_OSBAL OSBAL,
CM_AMT_DUE DUE_AMT,
CMR_MTHINARR MIA,
CMR_STATUS STATUS,
CM_PROBLEM_STATUS PROBLEM_STATUS,
CM_STRATEGY STRATEGY,
CM_PROFILE PROFILES,
CMR_AKPK AKPK,
CM_DMP_FLAG DMP,
CMR_PDC_FLAG PDC,
CM_DECEASED DECEASED,
CMR_BANKRUPT_FLAG BANKRUPT,
(SELECT MAX (PAY_DATE)
FROM JCOLLECT.VW_PAYMENT
WHERE PAY_SOURCE = 'SIBS'
AND PAY_CASEKEY = CM_CASEID AND (PAY_CODE IN
(SELECT KBT_TRAN
FROM JCOLLECT.KBTRX
WHERE KBT_SOURCE = 'SIBS' AND PAYMENT = 'Y'))) MAX_PAYDATE,
(SELECT COUNT (*)
FROM JCOLLECT.LGCASEMAST
WHERE CM_CASEKEY = CM_CASEID
AND CM_STATUS IN ('ACTV', 'KIV')
AND CM_TYPE IN ('CSUT', 'FCLS', 'BOTH')) NUM_ACTV_LEGAL_CASES,
(SELECT SUM (PAY_AMT)
FROM JCOLLECT.VW_PAYMENT
WHERE PAY_SOURCE = 'SIBS'
AND PAY_CASEKEY = CM_CASEID
AND PAY_DATE BETWEEN TRUNC (SYSDATE) - 45 AND TRUNC (SYSDATE)) SUM_PAY_BYACC,
(SELECT SUM (PAY_AMT)
FROM JCOLLECT.VW_PAYMENT
WHERE PAY_SOURCE = 'SIBS'
AND PAY_CASEKEY = CM_CASEID
AND PAY_DATE BETWEEN
(SELECT CSUT_NODT
FROM JCOLLECT.JPROCESSDATA
WHERE CASEKEY = CM_CASEID
AND ALTKEY =
(SELECT REC_ID
FROM JCOLLECT.LGCASEMAST
WHERE CM_CASEKEY = CM_CASEID
AND CM_TYPE = 'CSUT' AND CM_STATUS = 'ACTV' AND ROWNUM <= 1))
AND (SELECT CSUT_NODT_EXP_DT
FROM JCOLLECT.JPROCESSDATA
WHERE CASEKEY = CM_CASEID
AND ALTKEY =
(SELECT REC_ID
FROM JCOLLECT.LGCASEMAST
WHERE CM_CASEKEY = CM_CASEID
AND CM_TYPE = 'CSUT' AND CM_STATUS = 'ACTV'
AND ROWNUM <= 1))) SUM_PAY_NODT,
(SELECT SUM (PAY_AMT)
FROM JCOLLECT.VW_PAYMENT
WHERE PAY_SOURCE = 'SIBS'
AND PAY_CASEKEY = CM_CASEID
AND PAY_DATE BETWEEN TRUNC (SYSDATE) - 30 AND TRUNC (SYSDATE)) SUM_PAY_BYACC30,
(SELECT MAX (PAY_DATE)
FROM JCOLLECT.VW_PAYMENT
WHERE PAY_SOURCE = 'SIBS'
AND PAY_CASEKEY = CM_CASEID
AND PAY_CODE = '007') MAX_PAYDATE_HP_LAD
FROM JCOLLECT.JCASEMAST
WHERE CM_PRODUCT_TYPE = '03';
这是此查询的解释计划。
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 98814 (100)| 1581 |00:00:00.02 | 5266 | 0 | | | |
| 1 | SORT AGGREGATE | | 1581 | 1 | 46 | | 1580 |00:00:00.65 | 14162 | 17 | | | |
|* 2 | HASH JOIN | | 1581 | 6 | 276 | 6 (0)| 39103 |00:00:00.63 | 14162 | 17 | 1599K| 1599K| 1298K (0)|
|* 3 | INDEX RANGE SCAN | JPAYMENT_PAY_PKEY | 1581 | 6 | 192 | 4 (0)| 39192 |00:00:00.25 | 7051 | 18 | | | |
|* 4 | TABLE ACCESS FULL | KBTRX | 1187 | 25 | 350 | 2 (0)| 41510 |00:00:00.12 | 7116 | 0 | | | |
| 5 | SORT AGGREGATE | | 1580 | 1 | 24 | | 1580 |00:00:00.05 | 6525 | 0 | | | |
| 6 | INLIST ITERATOR | | 1580 | | | | 79 |00:00:00.04 | 6525 | 0 | | | |
|* 7 | TABLE ACCESS BY INDEX ROWID | LGCASEMAST | 3160 | 1 | 24 | 5 (0)| 79 |00:00:00.03 | 6525 | 0 | | | |
|* 8 | INDEX RANGE SCAN | LGCASMAST_STATUS_CASEKEY | 3160 | 1 | | 4 (0)| 79 |00:00:00.03 | 6445 | 0 | | | |
| 9 | SORT AGGREGATE | | 1580 | 1 | 32 | | 1580 |00:00:00.02 | 4779 | 0 | | | |
|* 10 | FILTER | | 1580 | | | | 870 |00:00:00.02 | 4779 | 0 | | | |
|* 11 | INDEX RANGE SCAN | JPAYMENT_PAY_PKEY | 1580 | 1 | 32 | 4 (0)| 870 |00:00:00.01 | 4779 | 0 | | | |
| 12 | SORT AGGREGATE | | 1580 | 1 | 32 | | 1580 |00:00:00.04 | 5551 | 0 | | | |
|* 13 | INDEX RANGE SCAN | JPAYMENT_PAY_PKEY | 1580 | 1 | 32 | 4 (0)| 14 |00:00:00.04 | 5551 | 0 | | | |
| 14 | TABLE ACCESS BY INDEX ROWID | JPROCESSDATA | 19 | 1 | 18 | 4 (0)| 19 |00:00:00.01 | 209 | 0 | | | |
|* 15 | INDEX RANGE SCAN | JPROCESSDATA_CASEKEY | 19 | 1 | | 3 (0)| 19 |00:00:00.01 | 119 | 0 | | | |
|* 16 | COUNT STOPKEY | | 19 | | | | 19 |00:00:00.01 | 79 | 0 | | | |
|* 17 | TABLE ACCESS BY INDEX ROWID| LGCASEMAST | 19 | 1 | 32 | 4 (0)| 19 |00:00:00.01 | 79 | 0 | | | |
|* 18 | INDEX RANGE SCAN | LGCASMAST_STATUS_CASEKEY | 19 | 1 | | 3 (0)| 19 |00:00:00.01 | 57 | 0 | | | |
| 19 | TABLE ACCESS BY INDEX ROWID | JPROCESSDATA | 1580 | 1 | 18 | 4 (0)| 74 |00:00:00.03 | 5284 | 0 | | | |
|* 20 | INDEX RANGE SCAN | JPROCESSDATA_CASEKEY | 1580 | 1 | | 3 (0)| 74 |00:00:00.03 | 5027 | 0 | | | |
|* 21 | COUNT STOPKEY | | 1580 | | | | 74 |00:00:00.02 | 4878 | 0 | | | |
|* 22 | TABLE ACCESS BY INDEX ROWID| LGCASEMAST | 1580 | 1 | 32 | 4 (0)| 74 |00:00:00.01 | 4878 | 0 | | | |
|* 23 | INDEX RANGE SCAN | LGCASMAST_STATUS_CASEKEY | 1580 | 1 | | 3 (0)| 74 |00:00:00.01 | 4800 | 0 | | | |
| 24 | SORT AGGREGATE | | 1580 | 1 | 32 | | 1580 |00:00:00.02 | 4779 | 0 | | | |
|* 25 | FILTER | | 1580 | | | | 568 |00:00:00.02 | 4779 | 0 | | | |
|* 26 | INDEX RANGE SCAN | JPAYMENT_PAY_PKEY | 1580 | 1 | 32 | 4 (0)| 568 |00:00:00.01 | 4779 | 0 | | | |
| 27 | SORT AGGREGATE | | 1580 | 1 | 32 | | 1580 |00:00:00.03 | 5468 | 0 | | | |
|* 28 | INDEX RANGE SCAN | JPAYMENT_PAY_PKEY | 1580 | 1 | 32 | 4 (0)| 33 |00:00:00.03 | 5468 | 0 | | | |
|* 29 | TABLE ACCESS FULL | JCASEMAST | 1 | 992K| 74M| 98814 (2)| 1581 |00:00:00.02 | 5266 | 0 | | | |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("PAY_CODE"="KBT_TRAN")
3 - access("PAY_CASEKEY"=:B1)
filter("PAY_SOURCE"='SIBS')
4 - filter(("KBT_SOURCE"='SIBS' AND "PAYMENT"='Y'))
7 - filter(("CM_TYPE"='BOTH' OR "CM_TYPE"='CSUT' OR "CM_TYPE"='FCLS'))
8 - access((("CM_STATUS"='ACTV' OR "CM_STATUS"='KIV')) AND "CM_CASEKEY"=:B1)
10 - filter(TRUNC(SYSDATE@!)>=TRUNC(SYSDATE@!)-45)
11 - access("PAY_CASEKEY"=:B1 AND "PAY_DATE">=TRUNC(SYSDATE@!)-45 AND "PAY_DATE"<=TRUNC(SYSDATE@!))
filter("PAY_SOURCE"='SIBS')
13 - access("PAY_CASEKEY"=:B1 AND "PAY_DATE">= AND "PAY_DATE"<=)
filter("PAY_SOURCE"='SIBS')
15 - access("CASEKEY"=:B1 AND "ALTKEY"=)
16 - filter(ROWNUM<=1)
17 - filter("CM_TYPE"='CSUT')
18 - access("CM_STATUS"='ACTV' AND "CM_CASEKEY"=:B1)
20 - access("CASEKEY"=:B1 AND "ALTKEY"=)
21 - filter(ROWNUM<=1)
22 - filter("CM_TYPE"='CSUT')
23 - access("CM_STATUS"='ACTV' AND "CM_CASEKEY"=:B1)
25 - filter(TRUNC(SYSDATE@!)>=TRUNC(SYSDATE@!)-30)
26 - access("PAY_CASEKEY"=:B1 AND "PAY_DATE">=TRUNC(SYSDATE@!)-30 AND "PAY_DATE"<=TRUNC(SYSDATE@!))
filter("PAY_SOURCE"='SIBS')
28 - access("PAY_CASEKEY"=:B1 AND "PAY_CODE"='007')
filter(("PAY_CODE"='007' AND "PAY_SOURCE"='SIBS'))
29 - filter("CM_PRODUCT_TYPE"='03')
我试图使用xx作为重写查询 (...) 选择...但这似乎没有多大区别。我没有运行查询很长时间，因为我可以看到更多数量的类似操作，并且成本没有太大差异。
WITH VPD AS ( SELECT PAY_CASEKEY, PAY_CODE, PAY_DATE, PAY_AMT FROM
JCOLLECT.VW_PAYMENT WHERE PAY_SOURCE = 'SIBS' AND PAY_CASEKEY IN(SELECT
CM_CASEID FROM JCOLLECT.JCASEMAST WHERE CM_PRODUCT_TYPE = '03')) SELECT
/*+ gather_plan_statistics */CM_CASEID CASEKEY, CM_CIF_ICNO ICNO,
CM_PRODUCT PRODUCT, CMR_OSBAL OSBAL, CM_AMT_DUE DUE_AMT, CMR_MTHINARR
MIA, CMR_STATUS STATUS, CM_PROBLEM_STATUS PROBLEM_STATUS, CM_STRATEGY
STRATEGY, CM_PROFILE PROFILES, CMR_AKPK AKPK, CM_DMP_FLAG DMP,
CMR_PDC_FLAG PDC, CM_DECEASED DECEASED, CMR_BANKRUPT_FLAG BANKRUPT,
(SELECT MAX (PAY_DATE) FROM VPD WHERE PAY_CASEKEY = CM_CASEID AND
(PAY_CODE IN (SELECT KBT_TRAN FROM JCOLLECT.KBTRX WHERE KBT_SOURCE =
'SIBS' AND PAYMENT = 'Y'))) MAX_PAYDATE, (SELECT COUNT (*) FROM
JCOLLECT.LGCASEMAST WHERE CM_CASEKEY = CM_CASEID AND CM_STATUS IN
('ACTV', 'KIV') AND CM_TYPE IN ('CSUT', 'FCLS', 'BOTH'))
NUM_ACTV_LEGAL_CASES, (SELECT SUM (PAY_AMT) FROM VPD WHERE PAY_CASEKEY
= CM_CASEID AND PAY_DATE BETWEEN TRUNC (SYSDATE) - 45 AND T
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 289K(100)| 0 |00:00:00.01 | 0 | 0 | | | |
| 1 | SORT AGGREGATE | | 0 | 1 | | 0 |00:00:00.01 | 0 | 0 | | | |
|* 2 | HASH JOIN | | 0 | 2449K| 6210 (3)| 0 |00:00:00.01 | 0 | 0 | 1368K| 1368K| |
|* 3 | TABLE ACCESS FULL | KBTRX | 0 | 25 | 2 (0)| 0 |00:00:00.01 | 0 | 0 | | | |
|* 4 | VIEW | | 0 | 5554K| 6163 (2)| 0 |00:00:00.01 | 0 | 0 | | | |
| 5 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6611_A234A94 | 0 | 5554K| 6163 (2)| 0 |00:00:00.01 | 0 | 0 | | | |
| 6 | SORT AGGREGATE | | 0 | 1 | | 0 |00:00:00.01 | 0 | 0 | | | |
| 7 | INLIST ITERATOR | | 0 | | | 0 |00:00:00.01 | 0 | 0 | | | |
|* 8 | TABLE ACCESS BY INDEX ROWID | LGCASEMAST | 0 | 1 | 5 (0)| 0 |00:00:00.01 | 0 | 0 | | | |
|* 9 | INDEX RANGE SCAN | LGCASMAST_STATUS_CASEKEY | 0 | 1 | 4 (0)| 0 |00:00:00.01 | 0 | 0 | | | |
| 10 | SORT AGGREGATE | | 0 | 1 | | 0 |00:00:00.01 | 0 | 0 | | | |
|* 11 | FILTER | | 0 | | | 0 |00:00:00.01 | 0 | 0 | | | |
|* 12 | VIEW | | 0 | 5554K| 6163 (2)| 0 |00:00:00.01 | 0 | 0 | | | |
| 13 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6611_A234A94 | 0 | 5554K| 6163 (2)| 0 |00:00:00.01 | 0 | 0 | | | |
| 14 | SORT AGGREGATE | | 0 | 1 | | 0 |00:00:00.01 | 0 | 0 | | | |
|* 15 | FILTER | | 0 | | | 0 |00:00:00.01 | 0 | 0 | | | |
|* 16 | VIEW | | 0 | 5554K| 6163 (2)| 0 |00:00:00.01 | 0 | 0 | | | |
| 17 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6611_A234A94 | 0 | 5554K| 6163 (2)| 0 |00:00:00.01 | 0 | 0 | | | |
| 18 | TABLE ACCESS BY INDEX ROWID | JPROCESSDATA | 0 | 1 | 4 (0)| 0 |00:00:00.01 | 0 | 0 | | | |
|* 19 | INDEX RANGE SCAN | JPROCESSDATA_CASEKEY | 0 | 1 | 3 (0)| 0 |00:00:00.01 | 0 | 0 | | | |
|* 20 | COUNT STOPKEY | | 0 | | | 0 |00:00:00.01 | 0 | 0 | | | |
|* 21 | TABLE ACCESS BY INDEX ROWID| LGCASEMAST | 0 | 1 | 4 (0)| 0 |00:00:00.01 | 0 | 0 | | | |
|* 22 | INDEX RANGE SCAN | LGCASMAST_STATUS_CASEKEY | 0 | 1 | 3 (0)| 0 |00:00:00.01 | 0 | 0 | | | |
| 23 | TABLE ACCESS BY INDEX ROWID | JPROCESSDATA | 0 | 1 | 4 (0)| 0 |00:00:00.01 | 0 | 0 | | | |
|* 24 | INDEX RANGE SCAN | JPROCESSDATA_CASEKEY | 0 | 1 | 3 (0)| 0 |00:00:00.01 | 0 | 0 | | | |
|* 25 | COUNT STOPKEY | | 0 | | | 0 |00:00:00.01 | 0 | 0 | | | |
|* 26 | TABLE ACCESS BY INDEX ROWID| LGCASEMAST | 0 | 1 | 4 (0)| 0 |00:00:00.01 | 0 | 0 | | | |
|* 27 | INDEX RANGE SCAN | LGCASMAST_STATUS_CASEKEY | 0 | 1 | 3 (0)| 0 |00:00:00.01 | 0 | 0 | | | |
| 28 | SORT AGGREGATE | | 0 | 1 | | 0 |00:00:00.01 | 0 | 0 | | | |
|* 29 | FILTER | | 0 | | | 0 |00:00:00.01 | 0 | 0 | | | |
|* 30 | VIEW | | 0 | 5554K| 6163 (2)| 0 |00:00:00.01 | 0 | 0 | | | |
| 31 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6611_A234A94 | 0 | 5554K| 6163 (2)| 0 |00:00:00.01 | 0 | 0 | | | |
| 32 | SORT AGGREGATE | | 0 | 1 | | 0 |00:00:00.01 | 0 | 0 | | | |
|* 33 | VIEW | | 0 | 5554K| 6163 (2)| 0 |00:00:00.01 | 0 | 0 | | | |
| 34 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6611_A234A94 | 0 | 5554K| 6163 (2)| 0 |00:00:00.01 | 0 | 0 | | | |
| 35 | TEMP TABLE TRANSFORMATION | | 1 | | | 0 |00:00:00.01 | 0 | 0 | | | |
| 36 | LOAD AS SELECT | | 1 | | | 0 |00:00:00.01 | 0 | 0 | 256K| 256K| 530K (0)|
|* 37 | HASH JOIN | | 1 | 5554K| 190K (2)| 5926K|00:00:18.50 | 302K| 5597 | 51M| 8094K| 53M (0)|
|* 38 | INDEX FAST FULL SCAN | JCASEMAST_PROD_PLACE_IDX | 1 | 968K| 4274 (2)| 970K|00:00:00.43 | 28225 | 0 | | | |
|* 39 | INDEX FAST FULL SCAN | JPAYMENT_PAY_PKEY | 1 | 20M| 170K (1)| 13M|00:00:07.48 | 274K| 5597 | | | |
|* 40 | TABLE ACCESS FULL | JCASEMAST | 0 | 968K| 98793 (2)| 0 |00:00:00.01 | 0 | 0 | | | |
----------------------------------------------------------------------------------
此计划中的A行不仅仅是E行，因此我确实收集了统计信息并运行了查询，但没有任何区别。
因此，就如何重写此查询以提高性能寻求您的帮助。
谢谢 & 致以最诚挚的问候!
-a
专家解答
数据库为从主表返回的每一行执行一次标量子查询。您可以通过查看 “开始” 列值与JCASEMAST中的A行匹配 (1,580，给出或取一行) 来验证这一点。
使用与子句对您没有帮助，因为:
-数据库仍然为JCASEMAST的每一行执行一次子查询
-此子查询处理大量行 (590万从哈希联接中出来)，并且需要约18分钟才能执行!
而是在主from子句中加入JCOLLECT.VW_PAYMENT:
然后，您需要按从JCASEMAST中选择的列进行分组。并过滤汇总在和/计数内的值。
例如，替换以下子查询:
使用此max (即删除子查询，只需使用过滤器即可使用max):
完成此操作后，请查看它如何影响性能。
让我们知道你过得怎么样。
使用与子句对您没有帮助，因为:
-数据库仍然为JCASEMAST的每一行执行一次子查询
-此子查询处理大量行 (590万从哈希联接中出来)，并且需要约18分钟才能执行!
而是在主from子句中加入JCOLLECT.VW_PAYMENT:
FROM JCOLLECT.JCASEMAST JOIN JCOLLECT.VW_PAYMENT ON PAY_CASEKEY = CM_CASEID WHERE CM_PRODUCT_TYPE = '03' AND PAY_SOURCE = 'SIBS';
然后，您需要按从JCASEMAST中选择的列进行分组。并过滤汇总在和/计数内的值。
例如，替换以下子查询:
(SELECT MAX (PAY_DATE)
FROM JCOLLECT.VW_PAYMENT
WHERE PAY_SOURCE = 'SIBS'
AND PAY_CASEKEY = CM_CASEID
AND PAY_CODE = '007')使用此max (即删除子查询，只需使用过滤器即可使用max):
max (
case
when PAY_CODE = '007'
then pay_date
end
) 完成此操作后，请查看它如何影响性能。
让我们知道你过得怎么样。
文章转载自ASKTOM，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
