查询语句大量阻塞，event为latch:buffer cache chain（该语句属于热链）。
查询结果跑不出来。
explain plan for
SELECT K.IDX_CODE, NVL(AA.COUNT, 0) VALUE
FROM K_AMR_CONFIG K
LEFT JOIN (SELECT F.GDS_NO,
F1.AUTH_USER_NO,
COUNT(DISTINCT F.TG_ID) COUNT,
(SELECT C.IDX_CODE
FROM K_AMR_CONFIG C
WHERE C.TAG_NO = F.TAG_NO
AND C.TAG_TYPE = '02') IDX_CODE
FROM K_AMR_EXCEP_FLOW F,
(SELECT T.ORG_NO, T.TG_ID, A.AUTH_USER_NO
FROM K_ID_G_TG T,
K_ID_GR_TG_RELA R,
K_ID_GRID_BUSS B,
K_ID_STAFF A
WHERE R.DATA_OPER_TYPE != 'D'
AND R.TG_ID = T.TG_ID
AND B.GR_ID = R.GR_ID
AND B.DATA_OPER_TYPE != 'D'
AND A.DATA_OPER_TYPE != 'D'
AND A.STAFF_NO <> '无'
AND A.STAFF_NO IS NOT NULL
AND B.BUSS_NO = A.STAFF_NO
AND A.AUTH_USER_NO IS NOT NULL
AND A.AUTH_USER_NO != '无') F1
WHERE F1.TG_ID = F.TG_ID
AND F1.ORG_NO = F.GDS_NO
AND F.TG_ID IS NOT NULL
AND F.GDS_NO IS NOT NULL
AND F.TAG_NO IN
('TagPBGZ', 'TagPBZZ', 'TagPBYZBPH', 'TagPBDDY')
AND SUBSTR(REPLACE(FIRST_TIME, '-', ''), 1, 6) =
TO_CHAR(SYSDATE, 'yyyymm')
AND F1.AUTH_USER_NO = :B1
GROUP BY F1.AUTH_USER_NO, F.GDS_NO, F.TAG_NO
UNION ALL
SELECT FF.GDS_NO,
F1.AUTH_USER_NO,
COUNT(DISTINCT FF.TG_ID) COUNT,
'PD0009' IDX_CODE
FROM (SELECT F.GDS_NO, F.TG_ID
FROM K_AMR_GBTD_FLOW F
WHERE F.TG_ID IS NOT NULL
AND F.GDS_NO IS NOT NULL
AND F.TAG_NO = 'TagPBGZTD'
AND SUBSTR(REPLACE(MAKE_TIME, '-', ''), 1, 6) =
TO_CHAR(SYSDATE, 'yyyymm')
GROUP BY F.GDS_NO, F.TG_ID
HAVING COUNT(DISTINCT F.MSG_ID) >= 2) FF,
(SELECT T.ORG_NO, T.TG_ID, A.AUTH_USER_NO
FROM K_ID_G_TG T,
K_ID_GR_TG_RELA R,
K_ID_GRID_BUSS B,
K_ID_STAFF A
WHERE R.DATA_OPER_TYPE != 'D'
AND R.TG_ID = T.TG_ID
AND B.GR_ID = R.GR_ID
AND B.DATA_OPER_TYPE != 'D'
AND A.DATA_OPER_TYPE != 'D'
AND B.BUSS_NO = A.STAFF_NO
AND A.STAFF_NO <> '无'
AND A.STAFF_NO IS NOT NULL
AND A.AUTH_USER_NO IS NOT NULL
AND A.AUTH_USER_NO != '无') F1
WHERE F1.TG_ID = FF.TG_ID
AND F1.ORG_NO = FF.GDS_NO
AND F1.AUTH_USER_NO = :B1
GROUP BY F1.AUTH_USER_NO, FF.GDS_NO
UNION ALL
SELECT FF.GDS_NO,
F1.AUTH_USER_NO,
COUNT(DISTINCT FF.DEVICE_NAME) COUNT,
'PD0012' IDX_CODE
FROM (SELECT F.GDS_NO,
F.TG_ID,
DEVICE_NAME,
COUNT(DISTINCT F.MSG_ID) CNT
FROM K_AMR_LBTZ_FLOW F
WHERE F.TG_ID IS NOT NULL
AND F.GDS_NO IS NOT NULL
AND F.TAG_NO = 'TagLBTZ'
AND SUBSTR(REPLACE(FIRST_TIME, '-', ''), 1, 6) =
TO_CHAR(SYSDATE, 'yyyymm')
GROUP BY F.GDS_NO, F.TG_ID, DEVICE_NAME
HAVING COUNT(DISTINCT F.MSG_ID) >= 3) FF,
(SELECT T.ORG_NO, T.TG_ID, A.AUTH_USER_NO
FROM K_ID_G_TG T,
K_ID_GR_TG_RELA R,
K_ID_GRID_BUSS B,
K_ID_STAFF A
WHERE R.DATA_OPER_TYPE != 'D'
AND R.TG_ID = T.TG_ID
AND B.GR_ID = R.GR_ID
AND B.DATA_OPER_TYPE != 'D'
AND A.DATA_OPER_TYPE != 'D'
AND B.BUSS_NO = A.STAFF_NO
AND A.STAFF_NO <> '无'
AND A.STAFF_NO IS NOT NULL
AND A.AUTH_USER_NO IS NOT NULL
AND A.AUTH_USER_NO != '无') F1
WHERE F1.TG_ID = FF.TG_ID
AND F1.ORG_NO = FF.GDS_NO
AND F1.AUTH_USER_NO = :B1
GROUP BY F1.AUTH_USER_NO, FF.GDS_NO
UNION ALL
SELECT F.GDS_NO,
F1.AUTH_USER_NO,
COUNT(DISTINCT F.MSG_ID) COUNT,
'PD0013' IDX_CODE
FROM K_AMR_GBTD_FLOW F,
(SELECT T.ORG_NO, T.TG_ID, A.AUTH_USER_NO
FROM K_ID_G_TG T,
K_ID_GR_TG_RELA R,
K_ID_GRID_BUSS B,
K_ID_STAFF A
WHERE R.DATA_OPER_TYPE != 'D'
AND R.TG_ID = T.TG_ID
AND B.GR_ID = R.GR_ID
AND B.DATA_OPER_TYPE != 'D'
AND A.DATA_OPER_TYPE != 'D'
AND B.BUSS_NO = A.STAFF_NO
AND A.STAFF_NO <> '无'
AND A.STAFF_NO IS NOT NULL
AND A.AUTH_USER_NO IS NOT NULL
AND A.AUTH_USER_NO != '无') F1
WHERE F1.TG_ID = F.TG_ID
AND F.TG_ID IS NOT NULL
AND F.GDS_NO IS NOT NULL
AND F.TAG_NO = 'TagZBBS'
AND SUBSTR(REPLACE(MAKE_TIME, '-', ''), 1, 6) =
TO_CHAR(SYSDATE, 'yyyymm')
AND F1.AUTH_USER_NO = :B1
GROUP BY F1.AUTH_USER_NO, F.GDS_NO
UNION ALL
SELECT FF.GDS_NO,
F1.AUTH_USER_NO,
COUNT(DISTINCT FF.DEVICE_NAME) COUNT,
'PD0014' IDX_CODE
FROM (SELECT F.GDS_NO,
F.TG_ID,
DEVICE_NAME,
COUNT(DISTINCT F.MSG_ID) CNT
FROM K_AMR_GBTD_FLOW F
WHERE F.TG_ID IS NOT NULL
AND F.GDS_NO IS NOT NULL
AND F.TAG_NO = 'TagZBBS'
AND SUBSTR(REPLACE(MAKE_TIME, '-', ''), 1, 6) =
TO_CHAR(SYSDATE, 'yyyymm')
GROUP BY F.GDS_NO, F.TG_ID, DEVICE_NAME
HAVING COUNT(DISTINCT F.MSG_ID) >= 2) FF,
(SELECT T.ORG_NO, T.TG_ID, A.AUTH_USER_NO
FROM K_ID_G_TG T,
K_ID_GR_TG_RELA R,
K_ID_GRID_BUSS B,
K_ID_STAFF A
WHERE R.DATA_OPER_TYPE != 'D'
AND R.TG_ID = T.TG_ID
AND B.GR_ID = R.GR_ID
AND B.DATA_OPER_TYPE != 'D'
AND A.DATA_OPER_TYPE != 'D'
AND B.BUSS_NO = A.STAFF_NO
AND A.STAFF_NO <> '无'
AND A.STAFF_NO IS NOT NULL
AND A.AUTH_USER_NO IS NOT NULL
AND A.AUTH_USER_NO != '无') F1
WHERE F1.TG_ID = FF.TG_ID
AND F1.ORG_NO = FF.GDS_NO
AND F1.AUTH_USER_NO = :B1
GROUP BY F1.AUTH_USER_NO, FF.GDS_NO) AA
ON AA.IDX_CODE = K.IDX_CODE
WHERE K.IDX_CODE IN ('PD0002',
'PD0004',
'PD0006',
'PD0008',
'PD0009',
'PD0012',
'PD0013',
'PD0014');
Plan hash value: 852542323
-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 256 | 84261 (1)| 00:16:52 |
|* 1 | HASH JOIN OUTER | | 8 | 256 | 84261 (1)| 00:16:52 |
| 2 | INLIST ITERATOR | | | | | |
|* 3 | INDEX RANGE SCAN | K_AMR_CONFIG_IDX_CODE | 8 | 56 | 1 (0)| 00:00:01 |
|* 4 | VIEW | | 5 | 125 | 84260 (1)| 00:16:52 |
| 5 | UNION-ALL | | | | | |
|* 6 | TABLE ACCESS BY INDEX ROWID | K_AMR_CONFIG | 1 | 19 | 1 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | K_AMR_CONFIG_TAG_NO | 2 | | 1 (0)| 00:00:01 |
| 8 | SORT GROUP BY | | 1 | 132 | 4012 (1)| 00:00:49 |
| 9 | NESTED LOOPS | | 1 | 132 | 4011 (1)| 00:00:49 |
| 10 | NESTED LOOPS | | 9 | 132 | 4011 (1)| 00:00:49 |
|* 11 | HASH JOIN | | 1 | 121 | 4010 (1)| 00:00:49 |
| 12 | INLIST ITERATOR | | | | | |
|* 13 | TABLE ACCESS BY INDEX ROWID | K_AMR_EXCEP_FLOW | 28 | 1120 | 144 (0)| 00:00:02 |
|* 14 | INDEX RANGE SCAN | IDX_TAG_NO | 9897 | | 7 (0)| 00:00:01 |
|* 15 | TABLE ACCESS BY INDEX ROWID | K_IC_DIM_VALUE | 1 | 37 | 1 (0)| 00:00:01 |
|* 16 | INDEX RANGE SCAN | IDX_K_IC_DIM_VALUE_N2 | 1 | | 1 (0)| 00:00:01 |
| 17 | MERGE JOIN CARTESIAN | | 60220 | 4763K| 3866 (1)| 00:00:47 |
| 18 | NESTED LOOPS | | 1 | 41 | 2 (0)| 00:00:01 |
| 19 | NESTED LOOPS | | 2 | 41 | 2 (0)| 00:00:01 |
|* 20 | TABLE ACCESS BY INDEX ROWID | K_ID_STAFF | 1 | 21 | 1 (0)| 00:00:01 |
|* 21 | INDEX RANGE SCAN | IDX_K_ID_STAFF_N4 | 1 | | 1 (0)| 00:00:01 |
|* 22 | INDEX RANGE SCAN | IDX_BUSS_NO | 2 | | 1 (0)| 00:00:01 |
|* 23 | TABLE ACCESS BY INDEX ROWID | K_ID_GRID_BUSS | 1 | 20 | 1 (0)| 00:00:01 |
| 24 | BUFFER SORT | | 262K| 10M| 3865 (1)| 00:00:47 |
|* 25 | VIEW | | 262K| 10M| 3864 (1)| 00:00:47 |
|* 26 | FILTER | | | | | |
|* 27 | TABLE ACCESS FULL | G_TG | 262K| 6146K| 3864 (1)| 00:00:47 |
|* 28 | INDEX RANGE SCAN | IDX_GR_TG_GR_ID | 9 | | 1 (0)| 00:00:01 |
|* 29 | TABLE ACCESS BY INDEX ROWID | K_ID_GR_TG_RELA | 1 | 11 | 1 (0)| 00:00:01 |
| 30 | HASH GROUP BY | | 1 | 31 | 62111 (1)| 00:12:26 |
| 31 | VIEW | VM_NWVW_2 | 1 | 31 | 62110 (1)| 00:12:26 |
| 32 | HASH GROUP BY | | 1 | 97 | 62110 (1)| 00:12:26 |
| 33 | NESTED LOOPS | | 1 | 97 | 62109 (1)| 00:12:26 |
| 34 | NESTED LOOPS | | 9 | 97 | 62109 (1)| 00:12:26 |
|* 35 | HASH JOIN | | 1 | 86 | 62108 (1)| 00:12:26 |
| 36 | VIEW | | 47 | 235 | 58243 (1)| 00:11:39 |
|* 37 | FILTER | | | | | |
| 38 | HASH GROUP BY | | 47 | 6345 | 58243 (1)| 00:11:39 |
| 39 | VIEW | VM_NWVW_1 | 929 | 122K| 58243 (1)| 00:11:39 |
| 40 | HASH GROUP BY | | 929 | 67817 | 58243 (1)| 00:11:39 |
|* 41 | TABLE ACCESS BY INDEX ROWID| K_AMR_GBTD_FLOW | 929 | 67817 | 58242 (1)| 00:11:39 |
|* 42 | INDEX RANGE SCAN | IDX_TAG_NO1 | 5278K| | 3107 (1)| 00:00:38 |
|* 43 | TABLE ACCESS BY INDEX ROWID | K_IC_DIM_VALUE | 1 | 37 | 1 (0)| 00:00:01 |
|* 44 | INDEX RANGE SCAN | IDX_K_IC_DIM_VALUE_N2 | 1 | | 1 (0)| 00:00:01 |
| 45 | MERGE JOIN CARTESIAN | | 60220 | 4763K| 3865 (1)| 00:00:47 |
| 46 | NESTED LOOPS | | 1 | 41 | 2 (0)| 00:00:01 |
| 47 | NESTED LOOPS | | 2 | 41 | 2 (0)| 00:00:01 |
|* 48 | TABLE ACCESS BY INDEX ROWID | K_ID_STAFF | 1 | 21 | 1 (0)| 00:00:01 |
|* 49 | INDEX RANGE SCAN | IDX_K_ID_STAFF_N4 | 1 | | 1 (0)| 00:00:01 |
|* 50 | INDEX RANGE SCAN | IDX_BUSS_NO | 2 | | 1 (0)| 00:00:01 |
|* 51 | TABLE ACCESS BY INDEX ROWID | K_ID_GRID_BUSS | 1 | 20 | 1 (0)| 00:00:01 |
| 52 | BUFFER SORT | | 262K| 10M| 3864 (1)| 00:00:47 |
|* 53 | VIEW | | 262K| 10M| 3863 (1)| 00:00:47 |
|* 54 | FILTER | | | | | |
|* 55 | TABLE ACCESS FULL | G_TG | 262K| 6146K| 3863 (1)| 00:00:47 |
|* 56 | INDEX RANGE SCAN | IDX_GR_TG_GR_ID | 9 | | 1 (0)| 00:00:01 |
|* 57 | TABLE ACCESS BY INDEX ROWID | K_ID_GR_TG_RELA | 1 | 11 | 1 (0)| 00:00:01 |
| 58 | HASH GROUP BY | | 1 | 150 | 11901 (1)| 00:02:23 |
| 59 | VIEW | VM_NWVW_4 | 1 | 150 | 11900 (1)| 00:02:23 |
| 60 | HASH GROUP BY | | 1 | 139 | 11900 (1)| 00:02:23 |
| 61 | NESTED LOOPS | | 1 | 139 | 11899 (1)| 00:02:23 |
| 62 | NESTED LOOPS | | 9 | 139 | 11899 (1)| 00:02:23 |
|* 63 | HASH JOIN | | 1 | 128 | 11898 (1)| 00:02:23 |
| 64 | VIEW | | 267 | 12549 | 8033 (1)| 00:01:37 |
|* 65 | FILTER | | | | | |
| 66 | HASH GROUP BY | | 267 | 47259 | 8033 (1)| 00:01:37 |
| 67 | VIEW | VM_NWVW_3 | 5333 | 921K| 8033 (1)| 00:01:37 |
| 68 | HASH GROUP BY | | 5333 | 572K| 8033 (1)| 00:01:37 |
|* 69 | TABLE ACCESS FULL | K_AMR_LBTZ_FLOW | 5333 | 572K| 8032 (1)| 00:01:37 |
|* 70 | TABLE ACCESS BY INDEX ROWID | K_IC_DIM_VALUE | 1 | 37 | 1 (0)| 00:00:01 |
|* 71 | INDEX RANGE SCAN | IDX_K_IC_DIM_VALUE_N2 | 1 | | 1 (0)| 00:00:01 |
| 72 | MERGE JOIN CARTESIAN | | 60220 | 4763K| 3865 (1)| 00:00:47 |
| 73 | NESTED LOOPS | | 1 | 41 | 2 (0)| 00:00:01 |
| 74 | NESTED LOOPS | | 2 | 41 | 2 (0)| 00:00:01 |
|* 75 | TABLE ACCESS BY INDEX ROWID | K_ID_STAFF | 1 | 21 | 1 (0)| 00:00:01 |
|* 76 | INDEX RANGE SCAN | IDX_K_ID_STAFF_N4 | 1 | | 1 (0)| 00:00:01 |
|* 77 | INDEX RANGE SCAN | IDX_BUSS_NO | 2 | | 1 (0)| 00:00:01 |
|* 78 | TABLE ACCESS BY INDEX ROWID | K_ID_GRID_BUSS | 1 | 20 | 1 (0)| 00:00:01 |
| 79 | BUFFER SORT | | 262K| 10M| 3864 (1)| 00:00:47 |
|* 80 | VIEW | | 262K| 10M| 3863 (1)| 00:00:47 |
|* 81 | FILTER | | | | | |
|* 82 | TABLE ACCESS FULL | G_TG | 262K| 6146K| 3863 (1)| 00:00:47 |
|* 83 | INDEX RANGE SCAN | IDX_GR_TG_GR_ID | 9 | | 1 (0)| 00:00:01 |
|* 84 | TABLE ACCESS BY INDEX ROWID | K_ID_GR_TG_RELA | 1 | 11 | 1 (0)| 00:00:01 |
| 85 | HASH GROUP BY | | 1 | 143 | 1187 (1)| 00:00:15 |
| 86 | VIEW | VM_NWVW_5 | 1 | 143 | 1186 (1)| 00:00:15 |
| 87 | HASH GROUP BY | | 1 | 149 | 1186 (1)| 00:00:15 |
|* 88 | FILTER | | | | | |
|* 89 | FILTER | | | | | |
|* 90 | HASH JOIN | | 1 | 149 | 1184 (1)| 00:00:15 |
| 91 | NESTED LOOPS | | 1 | 76 | 4 (0)| 00:00:01 |
| 92 | NESTED LOOPS | | 1 | 76 | 4 (0)| 00:00:01 |
| 93 | NESTED LOOPS | | 1 | 52 | 3 (0)| 00:00:01 |
| 94 | NESTED LOOPS | | 1 | 41 | 2 (0)| 00:00:01 |
|* 95 | TABLE ACCESS BY INDEX ROWID | K_ID_STAFF | 1 | 21 | 1 (0)| 00:00:01 |
|* 96 | INDEX RANGE SCAN | IDX_K_ID_STAFF_N4 | 1 | | 1 (0)| 00:00:01 |
|* 97 | TABLE ACCESS BY INDEX ROWID | K_ID_GRID_BUSS | 1 | 20 | 1 (0)| 00:00:01 |
|* 98 | INDEX RANGE SCAN | IDX_BUSS_NO | 2 | | 1 (0)| 00:00:01 |
|* 99 | TABLE ACCESS BY INDEX ROWID | K_ID_GR_TG_RELA | 1 | 11 | 1 (0)| 00:00:01 |
|*100 | INDEX RANGE SCAN | IDX_GR_TG_GR_ID | 9 | | 1 (0)| 00:00:01 |
|*101 | INDEX RANGE SCAN | IDX_G_TG_TG_ID | 1 | | 1 (0)| 00:00:01 |
|*102 | TABLE ACCESS BY INDEX ROWID | G_TG | 1 | 24 | 1 (0)| 00:00:01 |
|*103 | TABLE ACCESS BY INDEX ROWID | K_AMR_GBTD_FLOW | 19 | 1387 | 1180 (1)| 00:00:15 |
|*104 | INDEX RANGE SCAN | IDX_TAG_NO1 | 106K| | 63 (0)| 00:00:01 |
|*105 | TABLE ACCESS BY INDEX ROWID | K_IC_DIM_VALUE | 1 | 37 | 1 (0)| 00:00:01 |
|*106 | INDEX RANGE SCAN | IDX_K_IC_DIM_VALUE_N2 | 1 | | 1 (0)| 00:00:01 |
| 107 | HASH GROUP BY | | 1 | 143 | 5049 (1)| 00:01:01 |
| 108 | VIEW | VM_NWVW_7 | 1 | 143 | 5048 (1)| 00:01:01 |
| 109 | HASH GROUP BY | | 1 | 131 | 5048 (1)| 00:01:01 |
| 110 | NESTED LOOPS | | 1 | 131 | 5047 (1)| 00:01:01 |
| 111 | NESTED LOOPS | | 9 | 131 | 5047 (1)| 00:01:01 |
|*112 | HASH JOIN | | 1 | 120 | 5046 (1)| 00:01:01 |
| 113 | VIEW | | 1 | 39 | 1181 (1)| 00:00:15 |
|*114 | FILTER | | | | | |
| 115 | HASH GROUP BY | | 1 | 169 | 1181 (1)| 00:00:15 |
| 116 | VIEW | VM_NWVW_6 | 19 | 3211 | 1181 (1)| 00:00:15 |
| 117 | HASH GROUP BY | | 19 | 2033 | 1181 (1)| 00:00:15 |
|*118 | TABLE ACCESS BY INDEX ROWID| K_AMR_GBTD_FLOW | 19 | 2033 | 1180 (1)| 00:00:15 |
|*119 | INDEX RANGE SCAN | IDX_TAG_NO1 | 106K| | 63 (0)| 00:00:01 |
|*120 | TABLE ACCESS BY INDEX ROWID | K_IC_DIM_VALUE | 1 | 37 | 1 (0)| 00:00:01 |
|*121 | INDEX RANGE SCAN | IDX_K_IC_DIM_VALUE_N2 | 1 | | 1 (0)| 00:00:01 |
| 122 | MERGE JOIN CARTESIAN | | 60220 | 4763K| 3865 (1)| 00:00:47 |
| 123 | NESTED LOOPS | | 1 | 41 | 2 (0)| 00:00:01 |
| 124 | NESTED LOOPS | | 2 | 41 | 2 (0)| 00:00:01 |
|*125 | TABLE ACCESS BY INDEX ROWID | K_ID_STAFF | 1 | 21 | 1 (0)| 00:00:01 |
|*126 | INDEX RANGE SCAN | IDX_K_ID_STAFF_N4 | 1 | | 1 (0)| 00:00:01 |
|*127 | INDEX RANGE SCAN | IDX_BUSS_NO | 2 | | 1 (0)| 00:00:01 |
|*128 | TABLE ACCESS BY INDEX ROWID | K_ID_GRID_BUSS | 1 | 20 | 1 (0)| 00:00:01 |
| 129 | BUFFER SORT | | 262K| 10M| 3864 (1)| 00:00:47 |
|*130 | VIEW | | 262K| 10M| 3863 (1)| 00:00:47 |
|*131 | FILTER | | | | | |
|*132 | TABLE ACCESS FULL | G_TG | 262K| 6146K| 3863 (1)| 00:00:47 |
|*133 | INDEX RANGE SCAN | IDX_GR_TG_GR_ID | 9 | | 1 (0)| 00:00:01 |
|*134 | TABLE ACCESS BY INDEX ROWID | K_ID_GR_TG_RELA | 1 | 11 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("AA"."IDX_CODE"(+)="K"."IDX_CODE")
3 - access("K"."IDX_CODE"='PD0002' OR "K"."IDX_CODE"='PD0004' OR "K"."IDX_CODE"='PD0006' OR
"K"."IDX_CODE"='PD0008' OR "K"."IDX_CODE"='PD0009' OR "K"."IDX_CODE"='PD0012' OR "K"."IDX_CODE"='PD0013'
OR "K"."IDX_CODE"='PD0014')
4 - filter("AA"."IDX_CODE"(+)='PD0002' OR "AA"."IDX_CODE"(+)='PD0004' OR "AA"."IDX_CODE"(+)='PD0006' OR
"AA"."IDX_CODE"(+)='PD0008' OR "AA"."IDX_CODE"(+)='PD0009' OR "AA"."IDX_CODE"(+)='PD0012' OR
"AA"."IDX_CODE"(+)='PD0013' OR "AA"."IDX_CODE"(+)='PD0014')
6 - filter("C"."TAG_TYPE"='02')
7 - access("C"."TAG_NO"=:B1)
11 - access("TG_ID"=TO_NUMBER("F"."TG_ID") AND "ORG_NO"="F"."GDS_NO")
13 - filter("F"."GDS_NO" IS NOT NULL AND "F"."TG_ID" IS NOT NULL AND
SUBSTR(REPLACE("FIRST_TIME",'-',''),1,6)=TO_CHAR(SYSDATE@!,'yyyymm'))
14 - access("F"."TAG_NO"='TagPBDDY' OR "F"."TAG_NO"='TagPBGZ' OR "F"."TAG_NO"='TagPBYZBPH' OR
"F"."TAG_NO"='TagPBZZ')
15 - filter("DIM_CODE"='dept')
16 - access("DIM_VALUE"=:B1)
20 - filter("A"."STAFF_NO"<>'无' AND "A"."DATA_OPER_TYPE"<>'D' AND "A"."STAFF_NO" IS NOT NULL)
21 - access("A"."AUTH_USER_NO"=:B1)
filter("A"."AUTH_USER_NO"<>'无' AND "A"."AUTH_USER_NO" IS NOT NULL)
22 - access("B"."BUSS_NO"="A"."STAFF_NO")
filter("B"."BUSS_NO"<>'无')
23 - filter("B"."DATA_OPER_TYPE"<>'D')
25 - filter("ORG_NO" IS NOT NULL)
26 - filter('无'<>:B1)
27 - filter("A"."PUB_PRIV_FLAG"='01' AND "RUN_STATUS_CODE"='01')
28 - access("B"."GR_ID"="R"."GR_ID")
filter("R"."GR_ID" IS NOT NULL)
29 - filter("R"."DATA_OPER_TYPE"<>'D' AND "R"."TG_ID"="TG_ID")
35 - access("TG_ID"=TO_NUMBER("FF"."TG_ID") AND "ORG_NO"="FF"."GDS_NO")
37 - filter(COUNT("$vm_col_1")>=2)
41 - filter("F"."GDS_NO" IS NOT NULL AND "F"."TG_ID" IS NOT NULL AND
SUBSTR(REPLACE("MAKE_TIME",'-',''),1,6)=TO_CHAR(SYSDATE@!,'yyyymm'))
42 - access("F"."TAG_NO"='TagPBGZTD')
43 - filter("DIM_CODE"='dept')
44 - access("DIM_VALUE"=:B1)
48 - filter("A"."STAFF_NO"<>'无' AND "A"."DATA_OPER_TYPE"<>'D' AND "A"."STAFF_NO" IS NOT NULL)
49 - access("A"."AUTH_USER_NO"=:B1)
filter("A"."AUTH_USER_NO"<>'无' AND "A"."AUTH_USER_NO" IS NOT NULL)
50 - access("B"."BUSS_NO"="A"."STAFF_NO")
filter("B"."BUSS_NO"<>'无')
51 - filter("B"."DATA_OPER_TYPE"<>'D')
53 - filter("ORG_NO" IS NOT NULL)
54 - filter('无'<>:B1)
55 - filter("A"."PUB_PRIV_FLAG"='01' AND "RUN_STATUS_CODE"='01')
56 - access("B"."GR_ID"="R"."GR_ID")
filter("R"."GR_ID" IS NOT NULL)
57 - filter("R"."DATA_OPER_TYPE"<>'D' AND "R"."TG_ID"="TG_ID")
63 - access("TG_ID"=TO_NUMBER("FF"."TG_ID") AND "ORG_NO"="FF"."GDS_NO")
65 - filter(COUNT("$vm_col_1")>=3)
69 - filter("F"."GDS_NO" IS NOT NULL AND "F"."TG_ID" IS NOT NULL AND
SUBSTR(REPLACE("FIRST_TIME",'-',''),1,6)=TO_CHAR(SYSDATE@!,'yyyymm') AND "F"."TAG_NO"='TagLBTZ')
70 - filter("DIM_CODE"='dept')
71 - access("DIM_VALUE"=:B1)
75 - filter("A"."STAFF_NO"<>'无' AND "A"."DATA_OPER_TYPE"<>'D' AND "A"."STAFF_NO" IS NOT NULL)
76 - access("A"."AUTH_USER_NO"=:B1)
filter("A"."AUTH_USER_NO"<>'无' AND "A"."AUTH_USER_NO" IS NOT NULL)
77 - access("B"."BUSS_NO"="A"."STAFF_NO")
filter("B"."BUSS_NO"<>'无')
78 - filter("B"."DATA_OPER_TYPE"<>'D')
80 - filter("ORG_NO" IS NOT NULL)
81 - filter('无'<>:B1)
82 - filter("A"."PUB_PRIV_FLAG"='01' AND "RUN_STATUS_CODE"='01')
83 - access("B"."GR_ID"="R"."GR_ID")
filter("R"."GR_ID" IS NOT NULL)
84 - filter("R"."DATA_OPER_TYPE"<>'D' AND "R"."TG_ID"="TG_ID")
88 - filter( (SELECT "P_DIM_ID" FROM CPSS."K_IC_DIM_VALUE" "K_IC_DIM_VALUE" WHERE "DIM_VALUE"=:B1 AND
"DIM_CODE"='dept') IS NOT NULL)
89 - filter('无'<>:B1)
90 - access("TG_ID"=TO_NUMBER("F"."TG_ID"))
95 - filter("A"."STAFF_NO"<>'无' AND "A"."DATA_OPER_TYPE"<>'D' AND "A"."STAFF_NO" IS NOT NULL)
96 - access("A"."AUTH_USER_NO"=:B1)
filter("A"."AUTH_USER_NO"<>'无' AND "A"."AUTH_USER_NO" IS NOT NULL)
97 - filter("B"."DATA_OPER_TYPE"<>'D')
98 - access("B"."BUSS_NO"="A"."STAFF_NO")
filter("B"."BUSS_NO"<>'无')
99 - filter("R"."DATA_OPER_TYPE"<>'D')
100 - access("B"."GR_ID"="R"."GR_ID")
filter("R"."GR_ID" IS NOT NULL)
101 - access("R"."TG_ID"="TG_ID")
102 - filter("A"."PUB_PRIV_FLAG"='01' AND "RUN_STATUS_CODE"='01')
103 - filter("F"."GDS_NO" IS NOT NULL AND "F"."TG_ID" IS NOT NULL AND
SUBSTR(REPLACE("MAKE_TIME",'-',''),1,6)=TO_CHAR(SYSDATE@!,'yyyymm'))
104 - access("F"."TAG_NO"='TagZBBS')
105 - filter("DIM_CODE"='dept')
106 - access("DIM_VALUE"=:B1)
112 - access("TG_ID"=TO_NUMBER("FF"."TG_ID") AND "ORG_NO"="FF"."GDS_NO")
114 - filter(COUNT("$vm_col_1")>=2)
118 - filter("F"."GDS_NO" IS NOT NULL AND "F"."TG_ID" IS NOT NULL AND
SUBSTR(REPLACE("MAKE_TIME",'-',''),1,6)=TO_CHAR(SYSDATE@!,'yyyymm'))
119 - access("F"."TAG_NO"='TagZBBS')
120 - filter("DIM_CODE"='dept')
121 - access("DIM_VALUE"=:B1)
125 - filter("A"."STAFF_NO"<>'无' AND "A"."DATA_OPER_TYPE"<>'D' AND "A"."STAFF_NO" IS NOT NULL)
126 - access("A"."AUTH_USER_NO"=:B1)
filter("A"."AUTH_USER_NO"<>'无' AND "A"."AUTH_USER_NO" IS NOT NULL)
127 - access("B"."BUSS_NO"="A"."STAFF_NO")
filter("B"."BUSS_NO"<>'无')
128 - filter("B"."DATA_OPER_TYPE"<>'D')
130 - filter("ORG_NO" IS NOT NULL)
131 - filter('无'<>:B1)
132 - filter("A"."PUB_PRIV_FLAG"='01' AND "RUN_STATUS_CODE"='01')
133 - access("B"."GR_ID"="R"."GR_ID")
filter("R"."GR_ID" IS NOT NULL)
134 - filter("R"."DATA_OPER_TYPE"<>'D' AND "R"."TG_ID"="TG_ID")
仔细阅读sql我们发现该sql有一个重复出现的查询体
(SELECT T.ORG_NO, T.TG_ID, A.AUTH_USER_NO
FROM K_ID_G_TG T,
K_ID_GR_TG_RELA R,
K_ID_GRID_BUSS B,
K_ID_STAFF A
WHERE R.DATA_OPER_TYPE != 'D'
AND R.TG_ID = T.TG_ID
AND B.GR_ID = R.GR_ID
AND B.DATA_OPER_TYPE != 'D'
AND A.DATA_OPER_TYPE != 'D'
AND A.STAFF_NO <> '无'
AND A.STAFF_NO IS NOT NULL
AND B.BUSS_NO = A.STAFF_NO
AND A.AUTH_USER_NO IS NOT NULL
AND A.AUTH_USER_NO != '无') F1
共出现了5次。
对于这类重复出现的查询体，我们通常用with进行改写
从执行计划看到访问G_TG表，才发现K_ID_G_TG表对应的是一个视图。
create or replace view k_id_g_tg as
select "TG_ID","ORG_NO","TG_NO","TG_NAME","TG_CAP","INST_ADDR","CHG_DATE","PUB_PRIV_FLAG","RUN_STATUS_CODE","REMARKS","AREA_TYPE","PF","IS_EXCE_TG","RATE_TIME","DWDM","DATA_TIME"
from (select tg_id,
(select p_dim_id
from k_ic_dim_value
where dim_value = a.org_no
and dim_code = 'dept') org_no,
tg_no,
tg_name,
tg_cap,
inst_addr,
chg_date,
pub_priv_flag,
run_status_code,
remark remarks,
area_type,
pf,
(select is_exce_tg from k_id_g_tg_jp where tg_id=a.tg_id and rownum=1) is_exce_tg、,
(select rate_time from k_id_g_tg_jp where tg_id=a.tg_id and rownum=1) rate_time,
org_no dwdm,
sysdate data_time
from g_tg a
where a.pub_priv_flag = '01'
and run_status_code = '01')
where org_no is not null;
查看视图，视图中出现的org_no、is_exce_tg、rate_time字段，我们根本用不上,查询基表足矣。
所以with可以改写如下：
with F1 as
(
SELECT T.ORG_NO, T.TG_ID, A.AUTH_USER_NO
FROM g_tg T,
K_ID_GR_TG_RELA R,
K_ID_GRID_BUSS B,
K_ID_STAFF A
WHERE R.DATA_OPER_TYPE != 'D'
AND R.TG_ID = T.TG_ID
AND B.GR_ID = R.GR_ID
AND B.DATA_OPER_TYPE != 'D'
AND A.DATA_OPER_TYPE != 'D'
AND A.STAFF_NO <> '无'
AND A.STAFF_NO IS NOT NULL
AND B.BUSS_NO = A.STAFF_NO
AND A.AUTH_USER_NO IS NOT NULL
AND A.AUTH_USER_NO != '无'
AND A.AUTH_USER_NO = 'P6xxxxxxx1'
AND T.pub_priv_flag = '01'
and T.run_status_code = '01'
)
SELECT K.IDX_CODE, NVL(AA.COUNT, 0) VALUE
FROM K_AMR_CONFIG K
LEFT JOIN (
SELECT F.GDS_NO,
F1.AUTH_USER_NO,
COUNT(DISTINCT F.TG_ID) COUNT,
(SELECT C.IDX_CODE
FROM K_AMR_CONFIG C
WHERE C.TAG_NO = F.TAG_NO
AND C.TAG_TYPE = '02') IDX_CODE
FROM K_AMR_EXCEP_FLOW F,
F1
WHERE F1.TG_ID = F.TG_ID
AND F1.ORG_NO = F.GDS_NO
AND F.TG_ID IS NOT NULL
AND F.GDS_NO IS NOT NULL
AND F.TAG_NO IN
('TagPBGZ', 'TagPBZZ', 'TagPBYZBPH', 'TagPBDDY')
AND SUBSTR(REPLACE(FIRST_TIME, '-', ''), 1, 6) =
TO_CHAR(SYSDATE, 'yyyymm')
GROUP BY F1.AUTH_USER_NO, F.GDS_NO, F.TAG_NO
UNION ALL
SELECT FF.GDS_NO,
F1.AUTH_USER_NO,
COUNT(DISTINCT FF.TG_ID) COUNT,
'PD0009' IDX_CODE
FROM (SELECT F.GDS_NO, F.TG_ID
FROM K_AMR_GBTD_FLOW F
WHERE F.TG_ID IS NOT NULL
AND F.GDS_NO IS NOT NULL
AND F.TAG_NO = 'TagPBGZTD'
AND SUBSTR(REPLACE(MAKE_TIME, '-', ''), 1, 6) =
TO_CHAR(SYSDATE, 'yyyymm')
GROUP BY F.GDS_NO, F.TG_ID
HAVING COUNT(DISTINCT F.MSG_ID) >= 2) FF,
F1
WHERE F1.TG_ID = FF.TG_ID
AND F1.ORG_NO = FF.GDS_NO
GROUP BY F1.AUTH_USER_NO, FF.GDS_NO
UNION ALL
SELECT FF.GDS_NO,
F1.AUTH_USER_NO,
COUNT(DISTINCT FF.DEVICE_NAME) COUNT,
'PD0012' IDX_CODE
FROM (SELECT F.GDS_NO,
F.TG_ID,
DEVICE_NAME,
COUNT(DISTINCT F.MSG_ID) CNT
FROM K_AMR_LBTZ_FLOW F
WHERE F.TG_ID IS NOT NULL
AND F.GDS_NO IS NOT NULL
AND F.TAG_NO = 'TagLBTZ'
AND SUBSTR(REPLACE(FIRST_TIME, '-', ''), 1, 6) =
TO_CHAR(SYSDATE, 'yyyymm')
GROUP BY F.GDS_NO, F.TG_ID, DEVICE_NAME
HAVING COUNT(DISTINCT F.MSG_ID) >= 3) FF,
F1
WHERE F1.TG_ID = FF.TG_ID
AND F1.ORG_NO = FF.GDS_NO
GROUP BY F1.AUTH_USER_NO, FF.GDS_NO
UNION ALL
SELECT F.GDS_NO,
F1.AUTH_USER_NO,
COUNT(DISTINCT F.MSG_ID) COUNT,
'PD0013' IDX_CODE
FROM K_AMR_GBTD_FLOW F,
F1
WHERE F1.TG_ID = F.TG_ID
AND F.TG_ID IS NOT NULL
AND F.GDS_NO IS NOT NULL
AND F.TAG_NO = 'TagZBBS'
AND SUBSTR(REPLACE(MAKE_TIME, '-', ''), 1, 6) =
TO_CHAR(SYSDATE, 'yyyymm')
GROUP BY F1.AUTH_USER_NO, F.GDS_NO
UNION ALL
SELECT FF.GDS_NO,
F1.AUTH_USER_NO,
COUNT(DISTINCT FF.DEVICE_NAME) COUNT,
'PD0014' IDX_CODE
FROM (SELECT F.GDS_NO,
F.TG_ID,
DEVICE_NAME,
COUNT(DISTINCT F.MSG_ID) CNT
FROM K_AMR_GBTD_FLOW F
WHERE F.TG_ID IS NOT NULL
AND F.GDS_NO IS NOT NULL
AND F.TAG_NO = 'TagZBBS'
AND SUBSTR(REPLACE(MAKE_TIME, '-', ''), 1, 6) =
TO_CHAR(SYSDATE, 'yyyymm')
GROUP BY F.GDS_NO, F.TG_ID, DEVICE_NAME
HAVING COUNT(DISTINCT F.MSG_ID) >= 2) FF,
F1
WHERE F1.TG_ID = FF.TG_ID
AND F1.ORG_NO = FF.GDS_NO
GROUP BY F1.AUTH_USER_NO, FF.GDS_NO
) AA
ON AA.IDX_CODE = K.IDX_CODE
WHERE K.IDX_CODE IN ('PD0002',
'PD0004',
'PD0006',
'PD0008',
'PD0009',
'PD0012',
'PD0013',
'PD0014');
再次执行查询，还是查不动。
我们分别对union all上下关联的sql进行分析
SELECT FF.GDS_NO,
F1.AUTH_USER_NO,
COUNT(DISTINCT FF.TG_ID) COUNT,
'PD0009' IDX_CODE
FROM (SELECT F.GDS_NO, F.TG_ID
FROM K_AMR_GBTD_FLOW F
WHERE F.TG_ID IS NOT NULL
AND F.GDS_NO IS NOT NULL
AND F.TAG_NO = 'TagPBGZTD'
AND SUBSTR(REPLACE(MAKE_TIME, '-', ''), 1, 6) =
TO_CHAR(SYSDATE, 'yyyymm')
GROUP BY F.GDS_NO, F.TG_ID
HAVING COUNT(DISTINCT F.MSG_ID) >= 2) FF,
F1
WHERE F1.TG_ID = FF.TG_ID
AND F1.ORG_NO = FF.GDS_NO
GROUP BY F1.AUTH_USER_NO, FF.GDS_NO
该步骤执行慢。
从执行计划中看出，走的是TAG_NO的索引。
通过dba_tab_col_statistics发现TAG_NO distinct值为2。
全表数据量约为500w。
分析其他过滤条件MAKE_TIME列，数据比较倾斜，201910月数据为200w，其他月份数据量偏少，最高只有10w左右。
所以删除了原先TAG_NO列索引 drop index cpss.IDX_TAG_NO1;
创建函数索引
create index cpss.idx_MAKE_TIME on cpss.K_AMR_GBTD_FLOW(substr(replace(MAKE_TIME, '-', ''), 1, 6),TAG_NO);
执行计划如下：
Plan hash value: 668798141
------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 256 | 9039 (1)| 00:01:49 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT | SYS_TEMP_0FD9FD2FB_8ABCBB03 | | | | |
| 3 | NESTED LOOPS | | 1 | 76 | 4 (0)| 00:00:01 |
| 4 | NESTED LOOPS | | 1 | 76 | 4 (0)| 00:00:01 |
| 5 | NESTED LOOPS | | 1 | 52 | 3 (0)| 00:00:01 |
| 6 | NESTED LOOPS | | 1 | 41 | 2 (0)| 00:00:01 |
|* 7 | TABLE ACCESS BY INDEX ROWID | K_ID_STAFF | 1 | 21 | 1 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | IDX_K_ID_STAFF_N4 | 1 | | 1 (0)| 00:00:01 |
|* 9 | TABLE ACCESS BY INDEX ROWID | K_ID_GRID_BUSS | 1 | 20 | 1 (0)| 00:00:01 |
|* 10 | INDEX RANGE SCAN | IDX_BUSS_NO | 2 | | 1 (0)| 00:00:01 |
|* 11 | TABLE ACCESS BY INDEX ROWID | K_ID_GR_TG_RELA | 1 | 11 | 1 (0)| 00:00:01 |
|* 12 | INDEX RANGE SCAN | IDX_GR_TG_GR_ID | 9 | | 1 (0)| 00:00:01 |
|* 13 | INDEX RANGE SCAN | IDX_G_TG_TG_ID | 1 | | 1 (0)| 00:00:01 |
|* 14 | TABLE ACCESS BY INDEX ROWID | G_TG | 1 | 24 | 1 (0)| 00:00:01 |
|* 15 | HASH JOIN OUTER | | 8 | 256 | 9035 (1)| 00:01:49 |
| 16 | INLIST ITERATOR | | | | | |
|* 17 | INDEX RANGE SCAN | K_AMR_CONFIG_IDX_CODE | 8 | 56 | 1 (0)| 00:00:01 |
|* 18 | VIEW | | 5 | 125 | 9034 (1)| 00:01:49 |
| 19 | UNION-ALL | | | | | |
|* 20 | TABLE ACCESS BY INDEX ROWID | K_AMR_CONFIG | 1 | 19 | 1 (0)| 00:00:01 |
|* 21 | INDEX RANGE SCAN | K_AMR_CONFIG_TAG_NO | 2 | | 1 (0)| 00:00:01 |
| 22 | SORT GROUP BY | | 1 | 68 | 4 (25)| 00:00:01 |
| 23 | NESTED LOOPS | | 1 | 68 | 3 (0)| 00:00:01 |
| 24 | NESTED LOOPS | | 16114 | 68 | 3 (0)| 00:00:01 |
| 25 | VIEW | | 1 | 28 | 2 (0)| 00:00:01 |
| 26 | TABLE ACCESS FULL | SYS_TEMP_0FD9FD2FB_8ABCBB03 | 1 | 28 | 2 (0)| 00:00:01 |
|* 27 | INDEX RANGE SCAN | IDX_GDS_NO | 16114 | | 1 (0)| 00:00:01 |
|* 28 | TABLE ACCESS BY INDEX ROWID | K_AMR_EXCEP_FLOW | 1 | 40 | 1 (0)| 00:00:01 |
| 29 | HASH GROUP BY | | 1 | 31 | 332 (2)| 00:00:04 |
| 30 | VIEW | VW_DAG_1 | 1 | 31 | 331 (1)| 00:00:04 |
| 31 | HASH GROUP BY | | 1 | 33 | 331 (1)| 00:00:04 |
|* 32 | HASH JOIN | | 1 | 33 | 330 (1)| 00:00:04 |
| 33 | VIEW | | 1 | 28 | 2 (0)| 00:00:01 |
| 34 | TABLE ACCESS FULL | SYS_TEMP_0FD9FD2FB_8ABCBB03 | 1 | 28 | 2 (0)| 00:00:01 |
| 35 | VIEW | | 47 | 235 | 328 (1)| 00:00:04 |
|* 36 | FILTER | | | | | |
| 37 | HASH GROUP BY | | 47 | 6345 | 328 (1)| 00:00:04 |
| 38 | VIEW | VW_DAG_0 | 929 | 122K| 328 (1)| 00:00:04 |
| 39 | HASH GROUP BY | | 929 | 55740 | 328 (1)| 00:00:04 |
|* 40 | TABLE ACCESS BY INDEX ROWID| K_AMR_GBTD_FLOW | 929 | 55740 | 327 (1)| 00:00:04 |
|* 41 | INDEX RANGE SCAN | IDX_MAKE_TIME | 23042 | | 132 (0)| 00:00:02 |
| 42 | HASH GROUP BY | | 1 | 150 | 8037 (1)| 00:01:37 |
| 43 | VIEW | VW_DAG_3 | 1 | 150 | 8036 (1)| 00:01:37 |
| 44 | HASH GROUP BY | | 1 | 75 | 8036 (1)| 00:01:37 |
|* 45 | HASH JOIN | | 1 | 75 | 8035 (1)| 00:01:37 |
| 46 | VIEW | | 1 | 28 | 2 (0)| 00:00:01 |
| 47 | TABLE ACCESS FULL | SYS_TEMP_0FD9FD2FB_8ABCBB03 | 1 | 28 | 2 (0)| 00:00:01 |
| 48 | VIEW | | 267 | 12549 | 8033 (1)| 00:01:37 |
|* 49 | FILTER | | | | | |
| 50 | HASH GROUP BY | | 267 | 47259 | 8033 (1)| 00:01:37 |
| 51 | VIEW | VW_DAG_2 | 5333 | 921K| 8033 (1)| 00:01:37 |
| 52 | HASH GROUP BY | | 5333 | 572K| 8033 (1)| 00:01:37 |
|* 53 | TABLE ACCESS FULL | K_AMR_LBTZ_FLOW | 5333 | 572K| 8032 (1)| 00:01:37 |
| 54 | HASH GROUP BY | | 1 | 143 | 330 (1)| 00:00:04 |
| 55 | VIEW | VW_DAG_4 | 1 | 143 | 329 (1)| 00:00:04 |
| 56 | HASH GROUP BY | | 1 | 76 | 329 (1)| 00:00:04 |
| 57 | NESTED LOOPS | | 1 | 76 | 328 (0)| 00:00:04 |
| 58 | VIEW | | 1 | 16 | 2 (0)| 00:00:01 |
| 59 | TABLE ACCESS FULL | SYS_TEMP_0FD9FD2FB_8ABCBB03 | 1 | 28 | 2 (0)| 00:00:01 |
|* 60 | TABLE ACCESS BY INDEX ROWID | K_AMR_GBTD_FLOW | 1 | 60 | 326 (0)| 00:00:04 |
|* 61 | INDEX RANGE SCAN | IDX_MAKE_TIME | 23042 | | 132 (0)| 00:00:02 |
| 62 | HASH GROUP BY | | 1 | 143 | 332 (2)| 00:00:04 |
| 63 | VIEW | VW_DAG_6 | 1 | 143 | 331 (1)| 00:00:04 |
| 64 | HASH GROUP BY | | 1 | 67 | 331 (1)| 00:00:04 |
|* 65 | HASH JOIN | | 1 | 67 | 330 (1)| 00:00:04 |
| 66 | VIEW | | 1 | 28 | 2 (0)| 00:00:01 |
| 67 | TABLE ACCESS FULL | SYS_TEMP_0FD9FD2FB_8ABCBB03 | 1 | 28 | 2 (0)| 00:00:01 |
| 68 | VIEW | | 1 | 39 | 328 (1)| 00:00:04 |
|* 69 | FILTER | | | | | |
| 70 | HASH GROUP BY | | 1 | 169 | 328 (1)| 00:00:04 |
| 71 | VIEW | VW_DAG_5 | 19 | 3211 | 328 (1)| 00:00:04 |
| 72 | HASH GROUP BY | | 19 | 1786 | 328 (1)| 00:00:04 |
|* 73 | TABLE ACCESS BY INDEX ROWID| K_AMR_GBTD_FLOW | 19 | 1786 | 327 (1)| 00:00:04 |
|* 74 | INDEX RANGE SCAN | IDX_MAKE_TIME | 23042 | | 132 (0)| 00:00:02 |
------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
7 - filter("A"."STAFF_NO"<>'无' AND "A"."DATA_OPER_TYPE"<>'D' AND "A"."STAFF_NO" IS NOT NULL)
8 - access("A"."AUTH_USER_NO"='P68120521')
filter("A"."AUTH_USER_NO"<>'无')
9 - filter("B"."DATA_OPER_TYPE"<>'D')
10 - access("B"."BUSS_NO"="A"."STAFF_NO")
filter("B"."BUSS_NO"<>'无')
11 - filter("R"."DATA_OPER_TYPE"<>'D')
12 - access("B"."GR_ID"="R"."GR_ID")
filter("R"."GR_ID" IS NOT NULL)
13 - access("R"."TG_ID"="T"."TG_ID")
14 - filter("T"."PUB_PRIV_FLAG"='01' AND "T"."RUN_STATUS_CODE"='01')
15 - access("AA"."IDX_CODE"(+)="K"."IDX_CODE")
17 - access("K"."IDX_CODE"='PD0002' OR "K"."IDX_CODE"='PD0004' OR "K"."IDX_CODE"='PD0006' OR
"K"."IDX_CODE"='PD0008' OR "K"."IDX_CODE"='PD0009' OR "K"."IDX_CODE"='PD0012' OR "K"."IDX_CODE"='PD0013' OR
"K"."IDX_CODE"='PD0014')
18 - filter("AA"."IDX_CODE"(+)='PD0002' OR "AA"."IDX_CODE"(+)='PD0004' OR "AA"."IDX_CODE"(+)='PD0006' OR
"AA"."IDX_CODE"(+)='PD0008' OR "AA"."IDX_CODE"(+)='PD0009' OR "AA"."IDX_CODE"(+)='PD0012' OR
"AA"."IDX_CODE"(+)='PD0013' OR "AA"."IDX_CODE"(+)='PD0014')
20 - filter("C"."TAG_TYPE"='02')
21 - access("C"."TAG_NO"=:B1)
27 - access("F1"."ORG_NO"="F"."GDS_NO")
filter("F"."GDS_NO" IS NOT NULL)
28 - filter("F"."TG_ID" IS NOT NULL AND ("F"."TAG_NO"='TagPBDDY' OR "F"."TAG_NO"='TagPBGZ' OR
"F"."TAG_NO"='TagPBYZBPH' OR "F"."TAG_NO"='TagPBZZ') AND
SUBSTR(REPLACE("FIRST_TIME",'-',''),1,6)=TO_CHAR(SYSDATE@!,'yyyymm') AND "F1"."TG_ID"=TO_NUMBER("F"."TG_ID"))
32 - access("F1"."TG_ID"=TO_NUMBER("FF"."TG_ID") AND "F1"."ORG_NO"="FF"."GDS_NO")
36 - filter(COUNT("ITEM_1")>=2)
40 - filter("F"."GDS_NO" IS NOT NULL AND "F"."TG_ID" IS NOT NULL AND "F"."TAG_NO"='TagPBGZTD')
41 - access(SUBSTR(REPLACE("MAKE_TIME",'-',''),1,6)=TO_CHAR(SYSDATE@!,'yyyymm'))
45 - access("F1"."TG_ID"=TO_NUMBER("FF"."TG_ID") AND "F1"."ORG_NO"="FF"."GDS_NO")
49 - filter(COUNT("ITEM_1")>=3)
53 - filter("F"."GDS_NO" IS NOT NULL AND "F"."TG_ID" IS NOT NULL AND
SUBSTR(REPLACE("FIRST_TIME",'-',''),1,6)=TO_CHAR(SYSDATE@!,'yyyymm') AND "F"."TAG_NO"='TagLBTZ')
60 - filter("F"."GDS_NO" IS NOT NULL AND "F"."TG_ID" IS NOT NULL AND "F"."TAG_NO"='TagZBBS' AND
"F1"."TG_ID"=TO_NUMBER("F"."TG_ID"))
61 - access(SUBSTR(REPLACE("MAKE_TIME",'-',''),1,6)=TO_CHAR(SYSDATE@!,'yyyymm'))
65 - access("F1"."TG_ID"=TO_NUMBER("FF"."TG_ID") AND "F1"."ORG_NO"="FF"."GDS_NO")
69 - filter(COUNT("ITEM_1")>=2)
73 - filter("F"."GDS_NO" IS NOT NULL AND "F"."TG_ID" IS NOT NULL AND "F"."TAG_NO"='TagZBBS')
74 - access(SUBSTR(REPLACE("MAKE_TIME",'-',''),1,6)=TO_CHAR(SYSDATE@!,'yyyymm'))
3秒内出结果，业务正常。
该sql还存在优化空间，K_AMR_LBTZ_FLOW表也可以加上同样的函数索引。
create index cpss.idx_MAKE_TIME on cpss.K_AMR_LBTZ_FLOW(substr(replace(FIRST_TIME, '-', ''), 1, 6),TAG_NO);
将K_AMR_GBTD_FLOW表重复的查询同样用with改写
针对SELECT FF.GDS_NO,
F1.AUTH_USER_NO,
COUNT(DISTINCT FF.TG_ID) COUNT,
'PD0009' IDX_CODE
FROM (SELECT F.GDS_NO, F.TG_ID
FROM K_AMR_GBTD_FLOW F
WHERE F.TG_ID IS NOT NULL
AND F.GDS_NO IS NOT NULL
AND F.TAG_NO = 'TagPBGZTD'
AND SUBSTR(REPLACE(MAKE_TIME, '-', ''), 1, 6) =
TO_CHAR(SYSDATE, 'yyyymm')
GROUP BY F.GDS_NO, F.TG_ID
HAVING COUNT(DISTINCT F.MSG_ID) >= 2) FF,
F1
WHERE F1.TG_ID = FF.TG_ID
AND F1.ORG_NO = FF.GDS_NO
GROUP BY F1.AUTH_USER_NO, FF.GDS_NO
这条sql语句，我们可以在索引上加上更多的列，减少回表操作(但是维护索引中的各个字段，将会增加其他dml操作的时间)
create index cpss.idx_MAKE_TIME on cpss.K_AMR_GBTD_FLOW(substr(replace(MAKE_TIME, '-', ''), 1, 6),TAG_NO,GDS_NO,TG_ID,MSG_ID);