热门搜索
分布式
数据库一体机
云数据库
建设银行
保险核心
密集交易
登录
注册
SQL优化案例——谓词下推
技术分享/
文章详情
/
SQL优化案例——谓词下推
PYZ
2024/12/06
491
2
0
谓词推入是查询优化的一种策略，它将SQL语句中的过滤条件（即WHERE子句中的条件）推入到视图中，提前过滤数据减少数据处理量，降低SQL所需的I/O和CPU开销，从而提高查询性能。达梦中会通过参数FILTER_PUSH_DOWN来控制谓词下推策略，通常情况下CBO优化器会根据代价和索引条件自动完成谓词推入，前段时间遇到的一个案例，稍有不同的是需要经过两次谓词推入。
问题SQL（简化后）
WITH START_DATE AS
(SELECT '2024-01-01' AS INIT_DATE FROM DUAL)
,
ACC AS
(SELECT '1' AS STOCK_ACCOUNT FROM DUAL
UNION
SELECT '2' AS STOCK_ACCOUNT FROM DUAL
UNION
SELECT '3' AS STOCK_ACCOUNT FROM DUAL
UNION
SELECT '4' AS STOCK_ACCOUNT FROM DUAL
UNION
SELECT '5' AS STOCK_ACCOUNT FROM DUAL
)
,
DETAIL AS
( SELECT A.*
FROM HIS.DETAIL A,
ACC
WHERE A.TRAD_DATE>=(SELECT * FROM START_DATE)
AND A.SEC_ACCT=ACC.STOCK_ACCOUNT
AND IFNULL(CUST_CAPI_SETL_BIZ_TYPE_DESC,'0') NOT LIKE '%A%'
AND IFNULL(CUST_CAPI_SETL_BIZ_TYPE_DESC,'0') NOT LIKE '%B%'
AND IFNULL(CUST_CAPI_SETL_BIZ_TYPE_DESC,'0') NOT LIKE '%C%'
AND IFNULL(SUMR_INFO,'0') NOT LIKE '%A%'
AND IFNULL(SUMR_INFO,'0') NOT LIKE '%B%'
AND IFNULL(SUMR_INFO,'0') NOT LIKE '%C%'
)
,
PHONE AS
(SELECT DISTINCT REG_MOB AS MOB FROM DETAIL WHERE LENGTH(REG_MOB)>6
UNION
SELECT DISTINCT ACTUL_USE_MOB AS MOB FROM DETAIL WHERE LENGTH(ACTUL_USE_MOB)>6
UNION
SELECT DISTINCT MOB FROM DETAIL WHERE LENGTH(MOB)>6
)
SELECT
DISTINCT A.OAP_ACCT_NBR AS A,
A.SEC_ACCT AS B,
A.CUST_NAME AS C,
NVL(P1.MOB,NVL(P2.MOB,P3.MOB)) AS D,
'A' AS 类型
FROM HIS.DETAIL A
LEFT JOIN PHONE P1
ON A.REG_MOB=P1.MOB
LEFT JOIN PHONE P2
ON A.MOB=P2.MOB
LEFT JOIN PHONE P3
ON A.ACTUL_USE_MOB=P3.MOB
WHERE A.TRAD_DATE>=(SELECT * FROM START_DATE)
AND (P1.MOB IS NOT NULL
OR P2.MOB IS NOT NULL
OR P3.MOB IS NOT NULL)
AND IFNULL(CUST_CAPI_SETL_BIZ_TYPE_DESC,'0') NOT LIKE '%A%'
AND IFNULL(CUST_CAPI_SETL_BIZ_TYPE_DESC,'0') NOT LIKE '%B%'
AND IFNULL(CUST_CAPI_SETL_BIZ_TYPE_DESC,'0') NOT LIKE '%C%'
AND IFNULL(SUMR_INFO,'0') NOT LIKE '%A%'
AND IFNULL(SUMR_INFO,'0') NOT LIKE '%B%'
AND IFNULL(SUMR_INFO,'0') NOT LIKE '%C%'
;
执行计划
1 #NSET2: [63148, 1, 11049] 2 #PIPE2: [63148, 1, 11049] 3 #PIPE2: [63129, 1, 11049] 4 #PRJT2: [63122, 1, 11049]; exp_num(5), is_atom(FALSE) 5 #DISTINCT: [63122, 1, 11049] 6 #PRJT2: [63058, 3, 11049]; exp_num(5), is_atom(FALSE) 7 #UNION FOR OR2: [63058, 3, 11049]; key_num(4), outer_join(-) 8 #UNION FOR OR2: [38778, 2, 11049]; key_num(4), outer_join(-) 9 #HASH RIGHT JOIN2: [14501, 1, 11049]; key_num(1), ret_null(0), KEY(P3.MOB=A.ACTUL_USE_MOB) 10 #HEAP TABLE SCAN: [1, 1, 3535]; table_no(0) 11 #HASH RIGHT JOIN2: [14499, 1, 7514]; key_num(1), ret_null(0), KEY(P2.MOB=A.MOB) 12 #HEAP TABLE SCAN: [1, 1, 3535]; table_no(0) 13 #HASH2 INNER JOIN: [14497, 1, 3979]; KEY_NUM(1); KEY(P1.MOB=A.REG_MOB) KEY_NULL_EQU(0) 14 #SLCT2: [1, 1, 3535]; NOT(P1.MOB IS NULL) 15 #HEAP TABLE SCAN: [1, 1, 3535]; table_no(0) 16 #PARALLEL: [12134, 19886755, 444]; scan_type(FULL), key_num(0, 0, 0), simple(0) 17 #SLCT2: [12134, 19886755, 444]; (A.TRAD_DATE >= '2024-01-01' AND NOT(var1 LIKE '%A%') AND NOT(var1 LIKE '%B%') AND NOT(var1 LIKE '%C%')... ) 18 #CSCN2: [12134, 59074854, 444]; INDEX33564250(DETAIL as A) 19 #HASH LEFT JOIN2: [24276, 1, 11049]; key_num(1), partition_keys_num(0), ret_null(0), mix(0) KEY(A.ACTUL_USE_MOB=P3.MOB) 20 #SLCT2: [24274, 1, 7514]; A.MOB = P2.MOB 21 #NEST LOOP INNER JOIN2: [24274, 1, 7514]; [with var] 22 #SLCT2: [1, 1, 3535]; NOT(P2.MOB IS NULL) 23 #HEAP TABLE SCAN: [1, 1, 3535]; table_no(0) 24 #HASH RIGHT JOIN2: [12136, 1, 3979]; key_num(1), ret_null(0), KEY(P1.MOB=A.REG_MOB) 25 #HEAP TABLE SCAN: [1, 1, 3535]; table_no(0) 26 #PARALLEL: [12134, 1, 444]; scan_type(FULL), key_num(0, 0, 0), simple(0) 27 #SLCT2: [12134, 1, 444]; (A.TRAD_DATE >= '2024-01-01' AND NOT(var1 LIKE '%A%') AND NOT(var1 LIKE '%B%') AND NOT(var1 LIKE '%C%')... ) 28 #CSCN2: [12134, 59074854, 444]; INDEX33564250(DETAIL as A) 29 #HEAP TABLE SCAN: [1, 1, 3535]; table_no(0) 30 #SLCT2: [24278, 1, 11049]; A.ACTUL_USE_MOB = P3.MOB 31 #NEST LOOP INNER JOIN2: [24278, 1, 11049]; [with var] 32 #SLCT2: [1, 1, 3535]; NOT(P3.MOB IS NULL) 33 #HEAP TABLE SCAN: [1, 1, 3535]; table_no(0) 34 #HASH RIGHT JOIN2: [12138, 4, 7514]; key_num(1), ret_null(0), KEY(P2.MOB=A.MOB) 35 #HEAP TABLE SCAN: [1, 1, 3535]; table_no(0) 36 #HASH RIGHT JOIN2: [12136, 4, 3979]; key_num(1), ret_null(0), KEY(P1.MOB=A.REG_MOB) 37 #HEAP TABLE SCAN: [1, 1, 3535]; table_no(0) 38 #PARALLEL: [12134, 4, 444]; scan_type(FULL), key_num(0, 0, 0), simple(0) 39 #SLCT2: [12134, 4, 444]; (A.TRAD_DATE >= '2024-01-01' AND NOT(var1 LIKE '%A%') AND NOT(var1 LIKE '%B%') AND NOT(var1 LIKE '%C%') ... ) 40 #CSCN2: [12134, 59074854, 444]; INDEX33564250(DETAIL as A) 41 #HEAP TABLE: [7, 1, 3535]; table_no(0) full(0), mpp_full(0) autoid(1), sites(-) 42 #PRJT2: [7, 1, 3535]; exp_num(1), is_atom(FALSE) 43 #DISTINCT: [7, 1, 3535] 44 #UNION ALL: [6, 3, 3535] 45 #PRJT2: [4, 2, 3535]; exp_num(1), is_atom(FALSE) 46 #UNION ALL: [4, 2, 3535] 47 #PRJT2: [2, 1, 3535]; exp_num(1), is_atom(FALSE) 48 #DISTINCT: [2, 1, 3535] 49 #SLCT2: [1, 1, 3535]; exp11 > 6 50 #HEAP TABLE SCAN: [1, 4, 3535]; table_no(1) 51 #PRJT2: [2, 1, 3535]; exp_num(1), is_atom(FALSE) 52 #DISTINCT: [2, 1, 3535] 53 #SLCT2: [1, 1, 3535]; exp11 > 6 54 #HEAP TABLE SCAN: [1, 4, 3535]; table_no(1) 55 #PRJT2: [1, 1, 3535]; exp_num(1), is_atom(FALSE) 56 #SLCT2: [1, 1, 3535]; exp11 > 6 57 #HEAP TABLE SCAN: [1, 4, 3535]; table_no(1) 58 #HEAP TABLE: [173584, 5, 3535]; table_no(1) full(0), mpp_full(0) autoid(0), sites(-) 59 #PRJT2: [173584, 5, 3535]; exp_num(3), is_atom(FALSE) 60 #HASH2 INNER JOIN: [173584, 5, 3535]; KEY_NUM(1); KEY(ACC.STOCK_ACCOUNT=A.SEC_ACCT) KEY_NULL_EQU(0) 61 #PRJT2: [18, 1, 1]; exp_num(1), is_atom(FALSE) 62 #DISTINCT: [18, 1, 1] 63 #UNION ALL: [17, 21, 1] 64 #PRJT2: [16, 20, 1]; exp_num(1), is_atom(FALSE) 65 #UNION ALL: [16, 20, 1] 66 #PRJT2: [15, 19, 1]; exp_num(1), is_atom(FALSE) 67 #UNION ALL: [15, 19, 1] 68 #PRJT2: [14, 18, 1]; exp_num(1), is_atom(FALSE) 69 #UNION ALL: [14, 18, 1] 70 #PRJT2: [13, 17, 1]; exp_num(1), is_atom(FALSE) 71 #UNION ALL: [13, 17, 1] 72 #PRJT2: [12, 16, 1]; exp_num(1), is_atom(FALSE) 73 #UNION ALL: [12, 16, 1] 74 #PRJT2: [11, 15, 1]; exp_num(1), is_atom(FALSE) 75 #UNION ALL: [11, 15, 1] 76 #PRJT2: [11, 14, 1]; exp_num(1), is_atom(FALSE) 77 #UNION ALL: [11, 14, 1] 78 #PRJT2: [10, 13, 1]; exp_num(1), is_atom(FALSE) 79 #UNION ALL: [10, 13, 1] 80 #PRJT2: [9, 12, 1]; exp_num(1), is_atom(FALSE) 81 #UNION ALL: [9, 12, 1] 82 #PRJT2: [8, 11, 1]; exp_num(1), is_atom(FALSE) 83 #UNION ALL: [8, 11, 1] 84 #PRJT2: [7, 10, 1]; exp_num(1), is_atom(FALSE) 85 #UNION ALL: [7, 10, 1] 86 #PRJT2: [6, 9, 1]; exp_num(1), is_atom(FALSE) 87 #UNION ALL: [6, 9, 1] 88 #PRJT2: [5, 8, 1]; exp_num(1), is_atom(FALSE) 89 #UNION ALL: [5, 8, 1] 90 #PRJT2: [5, 7, 1]; exp_num(1), is_atom(FALSE) 91 #UNION ALL: [5, 7, 1] 92 #PRJT2: [4, 6, 1]; exp_num(1), is_atom(FALSE) 93 #UNION ALL: [4, 6, 1] 94 #PRJT2: [3, 5, 1]; exp_num(1), is_atom(FALSE) 95 #UNION ALL: [3, 5, 1] 96 #PRJT2: [2, 4, 1]; exp_num(1), is_atom(FALSE) 97 #UNION ALL: [2, 4, 1] 98 #PRJT2: [1, 3, 1]; exp_num(1), is_atom(FALSE) 99 #UNION ALL: [1, 3, 1] 100 #PRJT2: [1, 2, 1]; exp_num(1), is_atom(FALSE) 101 #UNION ALL: [1, 2, 1] 102 #PRJT2: [1, 1, 1]; exp_num(1), is_atom(FALSE) 103 #CSCN2: [1, 1, 1]; SYSINDEXSYSDUAL2(SYSDUAL2 as DUAL) 104 #PRJT2: [1, 1, 1]; exp_num(1), is_atom(FALSE) 105 #CSCN2: [1, 1, 1]; SYSINDEXSYSDUAL2(SYSDUAL2 as DUAL) 106 #PRJT2: [1, 1, 1]; exp_num(1), is_atom(FALSE) 107 #CSCN2: [1, 1, 1]; SYSINDEXSYSDUAL2(SYSDUAL2 as DUAL) 108 #PRJT2: [1, 1, 1]; exp_num(1), is_atom(FALSE) 109 #CSCN2: [1, 1, 1]; SYSINDEXSYSDUAL2(SYSDUAL2 as DUAL) 110 #PRJT2: [1, 1, 1]; exp_num(1), is_atom(FALSE) 111 #CSCN2: [1, 1, 1]; SYSINDEXSYSDUAL2(SYSDUAL2 as DUAL) 112 #PRJT2: [1, 1, 1]; exp_num(1), is_atom(FALSE) 113 #CSCN2: [1, 1, 1]; SYSINDEXSYSDUAL2(SYSDUAL2 as DUAL) 114 #PRJT2: [1, 1, 1]; exp_num(1), is_atom(FALSE) 115 #CSCN2: [1, 1, 1]; SYSINDEXSYSDUAL2(SYSDUAL2 as DUAL) 116 #PRJT2: [1, 1, 1]; exp_num(1), is_atom(FALSE) 117 #CSCN2: [1, 1, 1]; SYSINDEXSYSDUAL2(SYSDUAL2 as DUAL) 118 #PRJT2: [1, 1, 1]; exp_num(1), is_atom(FALSE) 119 #CSCN2: [1, 1, 1]; SYSINDEXSYSDUAL2(SYSDUAL2 as DUAL) 120 #PRJT2: [1, 1, 1]; exp_num(1), is_atom(FALSE) 121 #CSCN2: [1, 1, 1]; SYSINDEXSYSDUAL2(SYSDUAL2 as DUAL) 122 #PRJT2: [1, 1, 1]; exp_num(1), is_atom(FALSE) 123 #CSCN2: [1, 1, 1]; SYSINDEXSYSDUAL2(SYSDUAL2 as DUAL) 124 #PRJT2: [1, 1, 1]; exp_num(1), is_atom(FALSE) 125 #CSCN2: [1, 1, 1]; SYSINDEXSYSDUAL2(SYSDUAL2 as DUAL) 126 #PRJT2: [1, 1, 1]; exp_num(1), is_atom(FALSE) 127 #CSCN2: [1, 1, 1]; SYSINDEXSYSDUAL2(SYSDUAL2 as DUAL) 128 #PRJT2: [1, 1, 1]; exp_num(1), is_atom(FALSE) 129 #CSCN2: [1, 1, 1]; SYSINDEXSYSDUAL2(SYSDUAL2 as DUAL) 130 #PRJT2: [1, 1, 1]; exp_num(1), is_atom(FALSE) 131 #CSCN2: [1, 1, 1]; SYSINDEXSYSDUAL2(SYSDUAL2 as DUAL) 132 #PRJT2: [1, 1, 1]; exp_num(1), is_atom(FALSE) 133 #CSCN2: [1, 1, 1]; SYSINDEXSYSDUAL2(SYSDUAL2 as DUAL) 134 #PRJT2: [1, 1, 1]; exp_num(1), is_atom(FALSE) 135 #CSCN2: [1, 1, 1]; SYSINDEXSYSDUAL2(SYSDUAL2 as DUAL) 136 #PRJT2: [1, 1, 1]; exp_num(1), is_atom(FALSE) 137 #CSCN2: [1, 1, 1]; SYSINDEXSYSDUAL2(SYSDUAL2 as DUAL) 138 #PRJT2: [1, 1, 1]; exp_num(1), is_atom(FALSE) 139 #CSCN2: [1, 1, 1]; SYSINDEXSYSDUAL2(SYSDUAL2 as DUAL) 140 #PRJT2: [1, 1, 1]; exp_num(1), is_atom(FALSE) 141 #CSCN2: [1, 1, 1]; SYSINDEXSYSDUAL2(SYSDUAL2 as DUAL) 142 #PRJT2: [1, 1, 1]; exp_num(1), is_atom(FALSE) 143 #CSCN2: [1, 1, 1]; SYSINDEXSYSDUAL2(SYSDUAL2 as DUAL) 144 #PARALLEL: [1, 4, 3534]; scan_type(FULL), key_num(0, 0, 0), simple(0) 145 #SLCT2: [166132, 14796390, 3534]; (NOT(var1 LIKE '%A%') AND NOT(var1 LIKE '%B%') AND NOT(var1 LIKE '%C%') ... ) 146 #BLKUP2: [166132, 25841695, 3534]; IDX_20241125(A) 147 #SSEK2: [166132, 25841695, 3534]; scan_type(ASC), S0042_TRAD_DATE(DETAIL as A), scan_range['2024-01-01',max]
SQL分析
SQL使用with CTE写法，其中主表DETAIL有5000w以上的数据量，SQL逻辑是先通过start_date和acc的传参，带入a.trad_date和a.SEC_ACCT谓词条件进行过滤，获取with部分detail结果集，调用detail结果集获取符合条件的phone结果集，再调用phone的过滤结果MOB与主表his.detail进行关联查询获取到最终结果，逻辑中使用了两次谓词传递。见图一和图二。
图一：第一层通过a.trad_date和a.SEC_ACCT过滤
图二：第二层通过phone的MOB谓词传递对主表his.detail进行关联查询
通过执行计划分析，第一层a.SEC_ACCT谓词条件过滤性较好但字段上没有索引，虽然a.trad_date字段上存在索引但过滤性较差并且需要大量数据的回表，通过索引回表效率低；第一层的结果集传递到第二层，检索出phone的结果集也较少，通过phone的MOB条件推入，也能对主表his.detail起到很好的过滤效果，但主查询的三个关联字段上都不存在索引，因此执行计划只能进行全表扫描后过滤，无法进行高效的谓词推入，而全表需要扫描>5000w的数据量，SQL代价基本消耗在一次低效的索引回表和三次全表扫描，整体SQL耗时大于30分钟，见图三和图四。
图三：第一层的索引+回表
图四：第二层的三次全表扫描
优化思路
（1）针对第一层索引效率低的问题，创建以a.SEC_ACCT字段为前导列的组合索引(SEC_ACCT,TRAD_DATE)，先将SEC_ACCT通过索引进行谓词下推，创建后执行计划如下。
图五：第一层创建索引后的执行计划
（2）第二层的全表扫描，通过对主表his.detail的关联字段REG_MOB、MOB、ACTUL_USE_MOB分别创建索引，让phone的MOB结果能够下推到主表当中，以小结果集驱动大结果集形成嵌套循环连接，执行计划如下。
图六：第二层创建索引后的执行计划
具体创建索引语句如下：
--22年9月月度版已支持并行创建索引，提高索引创建效率
CREATE INDEX IDX_20241125 ON HIS.DETAIL(SEC_ACCT ,TRAD_DATE ) PARALLEL 32;
CREATE INDEX IDX_HIS_S0042_N_20241126_01 ON HIS.DETAIL(REG_MOB,TRAD_DATE) PARALLEL 32;
CREATE INDEX IDX_HIS_S0042_N_20241126_02 ON HIS.DETAIL(MOB,TRAD_DATE) PARALLEL 32;
CREATE INDEX IDX_HIS_S0042_N_20241126_03 ON HIS.DETAIL(ACTUL_USE_MOB,TRAD_DATE) PARALLEL 32;
问题验证
优化后逻辑读13890，耗时384ms，整体执行计划如下：
1 #NSET2: [50, 5->20, 11049]
2 #PIPE2: [50, 5->20, 11049]
3 #PIPE2: [31, 5->20, 11049]
4 #PRJT2: [24, 5->20, 11049]; exp_num(5), is_atom(FALSE)
5 #DISTINCT: [24, 5->20, 11049], MEM_USED(7KB), DISK_USED(0KB)
6 #PRJT2: [23, 7->1135, 11049]; exp_num(5), is_atom(FALSE)
7 #UNION FOR OR2: [23, 7->1135, 11049]; key_num(4)
8 #UNION FOR OR2: [13, 6->1080, 11049]; key_num(4)
9 #HASH RIGHT JOIN2: [4, 5->1080, 11049]; key_num(1); col_num(10); MEM_USED(16046KB), DISK_USED(0KB) KEY(P3.MOB=A.ACTUL_USE_MOB)
10 #HEAP TABLE SCAN: [1, 1->12, 3535]; table_no(0),
11 #HASH RIGHT JOIN2: [3, 5->1080, 7514]; key_num(1); col_num(9); MEM_USED(16046KB), DISK_USED(0KB) KEY(P2.MOB=A.MOB)
12 #HEAP TABLE SCAN: [1, 1->12, 3535]; table_no(0),
13 #NEST LOOP INDEX JOIN2: [1, 5->1080, 3979]
14 #SLCT2: [1, 1->12, 3535]; NOT(P1.MOB IS NULL)
15 #HEAP TABLE SCAN: [1, 1->12, 3535]; table_no(0),
16 #PARALLEL: [1, 5->1080, 444]; scan_type(FULL)
17 #SLCT2: [1, 5->1080, 444]; (A.TRAD_DATE >= '2024-01-01' AND NOT(var1 LIKE '%A%') AND NOT(var1 LIKE '%F%') AND NOT(var1 LIKE '%E%') AND NOT(var1 LIKE '%D%') AND NOT(var1 LIKE '%C%') AND NOT(var1 LIKE '%E%') AND NOT(var1 LIKE '%B%') AND NOT(var2 LIKE '%A%') AND NOT(var2 LIKE '%F%') AND NOT(var2 LIKE '%E%') AND ... )
18 #BLKUP2: [1, 5->1344, 96]; IDX_HIS_S0042_N_20241126_01(DETAIL)
19 #SSEK2: [1, 5->1344, 96]; scan_type(ASC), IDX_HIS_S0042_N_20241126_01(DETAIL), scan_range[(P1.MOB,min),(P1.MOB,max))
20 #HASH LEFT JOIN2: [7, 1->1080, 11049]; key_num(1); col_num(10); partition_keys_num(0); mix(0); MEM_USED(12718KB), DISK_USED(0KB) KEY(A.ACTUL_USE_MOB=P3.MOB)
21 #SLCT2: [5, 1->1080, 7514]; A.MOB = P2.MOB
22 #NEST LOOP INNER JOIN2: [5, 1->1080, 7514]; [with var]
23 #SLCT2: [1, 1->12, 3535]; NOT(P2.MOB IS NULL)
24 #HEAP TABLE SCAN: [1, 1->12, 3535]; table_no(0),
25 #HASH RIGHT JOIN2: [1, 1->1080, 3979]; key_num(1); col_num(8); MEM_USED(192552KB), DISK_USED(0KB) KEY(P1.MOB=A.REG_MOB)
26 #HEAP TABLE SCAN: [1, 1->144, 3535]; table_no(0),
27 #PARALLEL: [1, 1->1080, 444]; scan_type(FULL)
28 #SLCT2: [1, 1->1080, 444]; (NOT(var1 LIKE '%A%') AND NOT(var1 LIKE '%F%') AND NOT(var1 LIKE '%E%') AND NOT(var1 LIKE '%D%') AND NOT(var1 LIKE '%C%') AND NOT(var1 LIKE '%E%') AND NOT(var1 LIKE '%B%') AND NOT(var2 LIKE '%A%') AND NOT(var2 LIKE '%F%') AND NOT(var2 LIKE '%E%') AND NOT(var2 LIKE '%D%') AND ... )
29 #BLKUP2: [1, 3->1083, 444]; IDX_HIS_S0042_N_20241126_02(DETAIL)
30 #SSEK2: [1, 3->1083, 444]; scan_type(ASC), IDX_HIS_S0042_N_20241126_02(DETAIL), scan_range[(var71,'2024-01-01'),(var71,max))
31 #HEAP TABLE SCAN: [1, 1->12, 3535]; table_no(0),
32 #SLCT2: [9, 1->181, 11049]; A.ACTUL_USE_MOB = P3.MOB
33 #NEST LOOP INNER JOIN2: [9, 1->181, 11049]; [with var]
34 #SLCT2: [1, 1->12, 3535]; NOT(P3.MOB IS NULL)
35 #HEAP TABLE SCAN: [1, 1->12, 3535]; table_no(0),
36 #HASH RIGHT JOIN2: [3, 4->181, 7514]; key_num(1); col_num(9); MEM_USED(192552KB), DISK_USED(0KB) KEY(P2.MOB=A.MOB)
37 #HEAP TABLE SCAN: [1, 1->144, 3535]; table_no(0),
38 #HASH RIGHT JOIN2: [1, 4->181, 3979]; key_num(1); col_num(8); MEM_USED(192552KB), DISK_USED(0KB) KEY(P1.MOB=A.REG_MOB)
39 #HEAP TABLE SCAN: [1, 1->144, 3535]; table_no(0),
40 #PARALLEL: [1, 4->181, 444]; scan_type(FULL)
41 #SLCT2: [1, 4->181, 444]; (NOT(var1 LIKE '%A%') AND NOT(var1 LIKE '%F%') AND NOT(var1 LIKE '%E%') AND NOT(var1 LIKE '%D%') AND NOT(var1 LIKE '%C%') AND NOT(var1 LIKE '%E%') AND NOT(var1 LIKE '%B%') AND NOT(var2 LIKE '%A%') AND NOT(var2 LIKE '%F%') AND NOT(var2 LIKE '%E%') AND NOT(var2 LIKE '%D%') AND ... )
42 #BLKUP2: [1, 8->183, 444]; IDX_HIS_S0042_N_20241126_03(DETAIL)
43 #SSEK2: [1, 8->183, 444]; scan_type(ASC), IDX_HIS_S0042_N_20241126_03(DETAIL), scan_range[(var72,'2024-01-01'),(var72,max))
44 #HEAP TABLE: [7, 1, 3535]; table_no(0) full(FALSE), mpp_full(0) autoid(TRUE) sites(-)
45 #PRJT2: [7, 1->12, 3535]; exp_num(1), is_atom(FALSE)
46 #DISTINCT: [7, 1->12, 3535], MEM_USED(7KB), DISK_USED(0KB)
47 #UNION ALL: [6, 3->727, 3535]
48 #PRJT2: [4, 2->15, 3535]; exp_num(1), is_atom(FALSE)
49 #UNION ALL: [4, 2->15, 3535]
50 #PRJT2: [2, 1->12, 3535]; exp_num(1), is_atom(FALSE)
51 #DISTINCT: [2, 1->12, 3535], MEM_USED(7KB), DISK_USED(0KB)
52 #SLCT2: [1, 1->712, 3535]; exp11 > 6
53 #HEAP TABLE SCAN: [1, 4->1507, 3535]; table_no(1),
54 #PRJT2: [2, 1->3, 3535]; exp_num(1), is_atom(FALSE)
55 #DISTINCT: [2, 1->3, 3535], MEM_USED(6KB), DISK_USED(0KB)
56 #SLCT2: [1, 1->107, 3535]; exp11 > 6
57 #HEAP TABLE SCAN: [1, 4->1507, 3535]; table_no(1),
58 #PRJT2: [1, 1->712, 3535]; exp_num(1), is_atom(FALSE)
59 #SLCT2: [1, 1->712, 3535]; exp11 > 6
60 #HEAP TABLE SCAN: [1, 4->1507, 3535]; table_no(1),
61 #HEAP TABLE: [18, 4, 3535]; table_no(1) full(FALSE), mpp_full(0) autoid(FALSE) sites(-)
62 #PRJT2: [18, 4->1507, 3535]; exp_num(3), is_atom(FALSE)
63 #NEST LOOP INDEX JOIN2: [18, 4->1507, 3535]
64 #PRJT2: [18, 1->21, 1]; exp_num(1), is_atom(FALSE)
65 #DISTINCT: [18, 1->21, 1], MEM_USED(7KB), DISK_USED(0KB)
66 #UNION ALL: [17, 21->21, 1]
67 #PRJT2: [16, 20->20, 1]; exp_num(1), is_atom(FALSE)
68 #UNION ALL: [16, 20->20, 1]
69 #PRJT2: [15, 19->19, 1]; exp_num(1), is_atom(FALSE)
70 #UNION ALL: [15, 19->19, 1]
71 #PRJT2: [14, 18->18, 1]; exp_num(1), is_atom(FALSE)
72 #UNION ALL: [14, 18->18, 1]
73 #PRJT2: [13, 17->17, 1]; exp_num(1), is_atom(FALSE)
74 #UNION ALL: [13, 17->17, 1]
75 #PRJT2: [12, 16->16, 1]; exp_num(1), is_atom(FALSE)
76 #UNION ALL: [12, 16->16, 1]
77 #PRJT2: [11, 15->15, 1]; exp_num(1), is_atom(FALSE)
78 #UNION ALL: [11, 15->15, 1]
79 #PRJT2: [11, 14->14, 1]; exp_num(1), is_atom(FALSE)
80 #UNION ALL: [11, 14->14, 1]
81 #PRJT2: [10, 13->13, 1]; exp_num(1), is_atom(FALSE)
82 #UNION ALL: [10, 13->13, 1]
83 #PRJT2: [9, 12->12, 1]; exp_num(1), is_atom(FALSE)
84 #UNION ALL: [9, 12->12, 1]
85 #PRJT2: [8, 11->11, 1]; exp_num(1), is_atom(FALSE)
86 #UNION ALL: [8, 11->11, 1]
87 #PRJT2: [7, 10->10, 1]; exp_num(1), is_atom(FALSE)
88 #UNION ALL: [7, 10->10, 1]
89 #PRJT2: [6, 9->9, 1]; exp_num(1), is_atom(FALSE)
90 #UNION ALL: [6, 9->9, 1]
91 #PRJT2: [5, 8->8, 1]; exp_num(1), is_atom(FALSE)
92 #UNION ALL: [5, 8->8, 1]
93 #PRJT2: [5, 7->7, 1]; exp_num(1), is_atom(FALSE)
94 #UNION ALL: [5, 7->7, 1]
95 #PRJT2: [4, 6->6, 1]; exp_num(1), is_atom(FALSE)
96 #UNION ALL: [4, 6->6, 1]
97 #PRJT2: [3, 5->5, 1]; exp_num(1), is_atom(FALSE)
98 #UNION ALL: [3, 5->5, 1]
99 #PRJT2: [2, 4->4, 1]; exp_num(1), is_atom(FALSE)
100 #UNION ALL: [2, 4->4, 1]
101 #PRJT2: [1, 3->3, 1]; exp_num(1), is_atom(FALSE)
102 #UNION ALL: [1, 3->3, 1]
103 #PRJT2: [1, 2->2, 1]; exp_num(1), is_atom(FALSE)
104 #UNION ALL: [1, 2->2, 1]
105 #PRJT2: [1, 1->1, 1]; exp_num(1), is_atom(FALSE)
106 #CSCN2: [1, 1->1, 1]; SYSINDEXSYSDUAL2(SYSDUAL2)
107 #PRJT2: [1, 1->1, 1]; exp_num(1), is_atom(FALSE)
108 #CSCN2: [1, 1->1, 1]; SYSINDEXSYSDUAL2(SYSDUAL2)
109 #PRJT2: [1, 1->1, 1]; exp_num(1), is_atom(FALSE)
110 #CSCN2: [1, 1->1, 1]; SYSINDEXSYSDUAL2(SYSDUAL2)
111 #PRJT2: [1, 1->1, 1]; exp_num(1), is_atom(FALSE)
112 #CSCN2: [1, 1->1, 1]; SYSINDEXSYSDUAL2(SYSDUAL2)
113 #PRJT2: [1, 1->1, 1]; exp_num(1), is_atom(FALSE)
114 #CSCN2: [1, 1->1, 1]; SYSINDEXSYSDUAL2(SYSDUAL2)
115 #PRJT2: [1, 1->1, 1]; exp_num(1), is_atom(FALSE)
116 #CSCN2: [1, 1->1, 1]; SYSINDEXSYSDUAL2(SYSDUAL2)
117 #PRJT2: [1, 1->1, 1]; exp_num(1), is_atom(FALSE)
118 #CSCN2: [1, 1->1, 1]; SYSINDEXSYSDUAL2(SYSDUAL2)
119 #PRJT2: [1, 1->1, 1]; exp_num(1), is_atom(FALSE)
120 #CSCN2: [1, 1->1, 1]; SYSINDEXSYSDUAL2(SYSDUAL2)
121 #PRJT2: [1, 1->1, 1]; exp_num(1), is_atom(FALSE)
122 #CSCN2: [1, 1->1, 1]; SYSINDEXSYSDUAL2(SYSDUAL2)
123 #PRJT2: [1, 1->1, 1]; exp_num(1), is_atom(FALSE)
124 #CSCN2: [1, 1->1, 1]; SYSINDEXSYSDUAL2(SYSDUAL2)
125 #PRJT2: [1, 1->1, 1]; exp_num(1), is_atom(FALSE)
126 #CSCN2: [1, 1->1, 1]; SYSINDEXSYSDUAL2(SYSDUAL2)
127 #PRJT2: [1, 1->1, 1]; exp_num(1), is_atom(FALSE)
128 #CSCN2: [1, 1->1, 1]; SYSINDEXSYSDUAL2(SYSDUAL2)
129 #PRJT2: [1, 1->1, 1]; exp_num(1), is_atom(FALSE)
130 #CSCN2: [1, 1->1, 1]; SYSINDEXSYSDUAL2(SYSDUAL2)
131 #PRJT2: [1, 1->1, 1]; exp_num(1), is_atom(FALSE)
132 #CSCN2: [1, 1->1, 1]; SYSINDEXSYSDUAL2(SYSDUAL2)
133 #PRJT2: [1, 1->1, 1]; exp_num(1), is_atom(FALSE)
134 #CSCN2: [1, 1->1, 1]; SYSINDEXSYSDUAL2(SYSDUAL2)
135 #PRJT2: [1, 1->1, 1]; exp_num(1), is_atom(FALSE)
136 #CSCN2: [1, 1->1, 1]; SYSINDEXSYSDUAL2(SYSDUAL2)
137 #PRJT2: [1, 1->1, 1]; exp_num(1), is_atom(FALSE)
138 #CSCN2: [1, 1->1, 1]; SYSINDEXSYSDUAL2(SYSDUAL2)
139 #PRJT2: [1, 1->1, 1]; exp_num(1), is_atom(FALSE)
140 #CSCN2: [1, 1->1, 1]; SYSINDEXSYSDUAL2(SYSDUAL2)
141 #PRJT2: [1, 1->1, 1]; exp_num(1), is_atom(FALSE)
142 #CSCN2: [1, 1->1, 1]; SYSINDEXSYSDUAL2(SYSDUAL2)
143 #PRJT2: [1, 1->1, 1]; exp_num(1), is_atom(FALSE)
144 #CSCN2: [1, 1->1, 1]; SYSINDEXSYSDUAL2(SYSDUAL2)
145 #PRJT2: [1, 1->1, 1]; exp_num(1), is_atom(FALSE)
146 #CSCN2: [1, 1->1, 1]; SYSINDEXSYSDUAL2(SYSDUAL2)
147 #PARALLEL: [1, 4->1507, 3534]; scan_type(FULL)
148 #SLCT2: [1, 4->1507, 3534]; (A.TRAD_DATE >= '2024-01-01' AND NOT(var1 LIKE '%A%') AND NOT(var1 LIKE '%F%') AND NOT(var1 LIKE '%E%') AND NOT(var1 LIKE '%D%') AND NOT(var1 LIKE '%C%') AND NOT(var1 LIKE '%E%') AND NOT(var1 LIKE '%B%') AND NOT(var2 LIKE '%A%') AND NOT(var2 LIKE '%F%') AND NOT(var2 LIKE '%E%') AND ... )
149 #BLKUP2: [1, 4->1625, 96]; IDX_20241125(DETAIL)
150 #SSEK2: [1, 4->1625, 96]; scan_type(ASC), IDX_20241125(DETAIL), scan_range[(ACC.STOCK_ACCOUNT,min),(ACC.STOCK_ACCOUNT,max))
Statistics
-----------------------------------------------------------------
0 data pages changed
0 undo pages changed
13890 logical reads
830 physical reads
0 redo size
1920 bytes sent to client
4621 bytes received from client
1 roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
20 rows processed
114 io wait time(ms)
384 exec time(ms)
已用时间: 383.414(毫秒). 执行号:816338502.
评论
后发表回复
作者
文章
阅读量
获赞
扫一扫
联系客服
联系客服
鄂Copyright © 达梦国产化应用创新实验室
达梦在线服务平台社区用户管理规范
鄂ICP备18017926号-1
达梦在线服务平台社区用户管理规范
鄂ICP备18017926号-1