DM8:oracle迁移到DM发现报表查询慢的问题
环境介绍
从oracle迁移到DM后，报表查询有1-2秒变为3-5秒出结果，个别的报表查询时间在150秒难以接受，对报表的慢SQL分析，发现其中有START WITH 遍历、递归、层次查询，针对层次查询进行优化；
1 SQL语句
SELECT
N.GEOCODE ,
L.L_AJBH ,
N.N_A_CODE AS CODE_BZ,
N.N_R_CODE AS CODE_ZZ
FROM
LAJBL L,
LANJN N
WHERE
L.AJBH_CODE = SUBSTR(N.AJBH_CODE, 1, 15)
AND
(
N.AJBH_STATES = '1'
OR N.AJBH_STATES IS NULL
)
AND
(
L.AJBH_STATES = '1'
OR L.AJBH_STATES IS NULL
)
AND N.AJBH_CODE IN
(
SELECT DISTINCT
(N.AJBH_CODE)
FROM
LAJBL L,
LANJN N
WHERE
L.AJBH_CODE = SUBSTR(N.AJBH_CODE, 1, 15)
AND N.N_AJASS NOT LIKE '1%'
START WITH N.N_AJASS = '777'
AND L.L_AJASS <> '07' CONNECT BY NOCYCLE PRIOR N.AJBH_CODE = SUBSTR(N.AJBH_CODE, 1, LENGTH(N.AJBH_CODE) - 6)
AND
(
L.AJBH_STATES = '1'
OR L.AJBH_STATES IS NULL
)
AND
(
N.AJBH_STATES = '1'
OR N.AJBH_STATES IS NULL
)
)
2 执行计划
1 #NSET2: [82654, 1149, 732] --结果集(result set)收集，一般是查询计划的顶层节点
2 #PRJT2: [82654, 1149, 732]; exp_num(19), is_atom(FALSE) --关系的―投影‖(project)运算，用于选择表达式项的计算
3 #UNION FOR OR2: [82654, 1149, 732]; key_num(2), outer_join(-)--OR 过滤的 UNION 计算
4 #UNION FOR OR2: [61983, 4, 732]; key_num(2), outer_join(-)--OR 过滤的 UNION 计算
5 #UNION FOR OR2: [41321, 3, 732]; key_num(2), outer_join(-)--OR 过滤的 UNION 计算
6 #HASH LEFT SEMI JOIN2: [20660, 1, 732]; KEY_NUM(1); KEY(n.JGSY_CODE=DMTEMPVIEW_889234212.colname) KEY_NULL_EQU(0)--HASH 左半连接
7 #NEST LOOP INDEX JOIN2: [1, 1, 732] --索引内连接
8 #BLKUP2: [1, 1, 156]; IDX_LOCAL_01(l)--定位查找
9 #SSEK2: [1, 1, 156]; scan_type(ASC), IDX_LOCAL_01(LOCAL as l), scan_range[(NULL,min,min),(NULL,max,max))--二级索引数据定位
10 #BLKUP2: [1, 1, 48]; IDX_LO_NEXT01(n)--定位查找
11 #SSEK2: [1, 1, 48]; scan_type(ASC), IDX_LO_NEXT01(LO_NEXT as n), scan_range[(l.JGSY_CODE,NULL),(l.JGSY_CODE,NULL)]--二级索引数据定位
12 #PRJT2: [20659, 1145, 312]; exp_num(1), is_atom(FALSE) --关系的―投影‖(project)运算，用于选择表达式项的计算
13 #DISTINCT: [20659, 1145, 312]--去重
14 #UNION FOR OR2: [20630, 80681, 312]; key_num(2), outer_join(-)--OR 过滤的 UNION 计算
15 #SLCT2: [10308, 40340, 312]; n.N_CLASS < '1'--关系的―选择‖（select）运算，用于查询条件的过滤
16 #HIERARCHICAL QUERY: [10281, 806815, 312]; KEY_NUM(0);--层次查询
17 #SLCT2: [3, 702, 312]; l.L_CLASS <> '07'--关系的―选择‖（select）运算，用于查询条件的过滤
18 #HASH2 INNER JOIN: [3, 702, 312]; RKEY_UNIQUE KEY_NUM(1); KEY(exp11=l.JGSY_CODE) KEY_NULL_EQU(0)--HASH 内连接
19 #BLKUP2: [1, 702, 156]; IDX_LONEXT_NCLASS(n)--定位查找
20 #SSEK2: [1, 702, 156]; scan_type(ASC), IDX_LONEXT_NCLASS(LO_NEXT as n), scan_range['700','700']--二级索引数据定位
21 #SSCN: [1, 9164, 156]; IDX_LOCAL_01(LOCAL as l)--直接使用二级索引进行扫描
22 #UNION FOR OR2: [14, 1149, 312]; key_num(2), outer_join(-)
23 #UNION FOR OR2: [2, 4, 312]; key_num(2), outer_join(-)
24 #UNION FOR OR2: [1, 2, 312]; key_num(2), outer_join(-)
25 #SLCT2: [1, 1, 312]; (var2 = exp11 AND l.JGSY_CODE = exp11)
26 #NEST LOOP INDEX JOIN2: [1, 1, 312]
27 #SSEK2: [1, 1, 156]; scan_type(ASC), IDX_LOCAL_01(LOCAL as l), scan_range[(NULL,min,min),(NULL,max,max))
28 #SSEK2: [1, 1, 144]; scan_type(ASC), IDX_LO_NEXT_01(LO_NEXT as n), scan_range[(NULL,min,min),(NULL,max,max))
29 #SLCT2: [1, 1, 312]; l.JGSY_STATES = '1'
30 #NEST LOOP INDEX JOIN2: [1, 1, 312]
31 #SLCT2: [1, 1, 156]; var2 = exp11
32 #SSEK2: [1, 1, 156]; scan_type(ASC), IDX_LO_NEXT_01(LO_NEXT as n), scan_range[(NULL,min,min),(NULL,max,max))
33 #BLKUP2: [1, 1, 48]; INDEX33555682(l)
34 #SSEK2: [1, 1, 48]; scan_type(ASC), INDEX33555682(LOCAL as l), scan_range[exp11,exp11]
35 #SLCT2: [1, 2, 312]; var2 = exp11
36 #NEST LOOP INDEX JOIN2: [1, 2, 312]
37 #SSEK2: [1, 1, 156]; scan_type(ASC), IDX_LOCAL_01(LOCAL as l), scan_range[(NULL,min,min),(NULL,max,max))
38 #BLKUP2: [1, 2, 48]; IDX_LO_NEXT01(n)
39 #SSEK2: [1, 2, 48]; scan_type(ASC), IDX_LO_NEXT01(LO_NEXT as n), scan_range[(l.JGSY_CODE,'1'),(l.JGSY_CODE,'1')]
40 #HASH2 INNER JOIN: [11, 1145, 312]; RKEY_UNIQUE KEY_NUM(1); KEY(exp11=l.JGSY_CODE) KEY_NULL_EQU(0)
41 #SLCT2: [8, 1145, 156]; var2 = exp11
42 #SSEK2: [8, 45808, 156]; scan_type(ASC), IDX_LO_NEXT_01(LO_NEXT as n), scan_range[('1',min,min),('1',max,max))
43 #SSEK2: [1, 7606, 156]; scan_type(ASC), IDX_LOCAL_01(LOCAL as l), scan_range[('1',min,min),('1',max,max))
44 #SLCT2: [10308, 40340, 312]; n.N_CLASS >= '2'
45 #HIERARCHICAL QUERY: [10281, 806815, 312]; KEY_NUM(0);
46 #SLCT2: [3, 702, 312]; l.L_CLASS <> '07'
47 #HASH2 INNER JOIN: [3, 702, 312]; RKEY_UNIQUE KEY_NUM(1); KEY(exp11=l.JGSY_CODE) KEY_NULL_EQU(0)
48 #BLKUP2: [1, 702, 156]; IDX_LONEXT_NCLASS(n)
49 #SSEK2: [1, 702, 156]; scan_type(ASC), IDX_LONEXT_NCLASS(LO_NEXT as n), scan_range['700','700']
50 #SSCN: [1, 9164, 156]; IDX_LOCAL_01(LOCAL as l)
51 #UNION FOR OR2: [14, 1149, 312]; key_num(2), outer_join(-)
52 #UNION FOR OR2: [2, 4, 312]; key_num(2), outer_join(-)
53 #UNION FOR OR2: [1, 2, 312]; key_num(2), outer_join(-)
54 #SLCT2: [1, 1, 312]; (var2 = exp11 AND l.JGSY_CODE = exp11)
55 #NEST LOOP INDEX JOIN2: [1, 1, 312]
56 #SSEK2: [1, 1, 156]; scan_type(ASC), IDX_LOCAL_01(LOCAL as l), scan_range[(NULL,min,min),(NULL,max,max))
57 #SSEK2: [1, 1, 144]; scan_type(ASC), IDX_LO_NEXT_01(LO_NEXT as n), scan_range[(NULL,min,min),(NULL,max,max))
58 #SLCT2: [1, 1, 312]; l.JGSY_STATES = '1'
59 #NEST LOOP INDEX JOIN2: [1, 1, 312]
60 #SLCT2: [1, 1, 156]; var2 = exp11
61 #SSEK2: [1, 1, 156]; scan_type(ASC), IDX_LO_NEXT_01(LO_NEXT as n), scan_range[(NULL,min,min),(NULL,max,max))
62 #BLKUP2: [1, 1, 48]; INDEX33555682(l)
63 #SSEK2: [1, 1, 48]; scan_type(ASC), INDEX33555682(LOCAL as l), scan_range[exp11,exp11]
64 #SLCT2: [1, 2, 312]; var2 = exp11
65 #NEST LOOP INDEX JOIN2: [1, 2, 312]
66 #SSEK2: [1, 1, 156]; scan_type(ASC), IDX_LOCAL_01(LOCAL as l), scan_range[(NULL,min,min),(NULL,max,max))
67 #BLKUP2: [1, 2, 48]; IDX_LO_NEXT01(n)
68 #SSEK2: [1, 2, 48]; scan_type(ASC), IDX_LO_NEXT01(LO_NEXT as n), scan_range[(l.JGSY_CODE,'1'),(l.JGSY_CODE,'1')]
69 #HASH2 INNER JOIN: [11, 1145, 312]; RKEY_UNIQUE KEY_NUM(1); KEY(exp11=l.JGSY_CODE) KEY_NULL_EQU(0)
70 #SLCT2: [8, 1145, 156]; var2 = exp11
71 #SSEK2: [8, 45808, 156]; scan_type(ASC), IDX_LO_NEXT_01(LO_NEXT as n), scan_range[('1',min,min),('1',max,max))
72 #SSEK2: [1, 7606, 156]; scan_type(ASC), IDX_LOCAL_01(LOCAL as l), scan_range[('1',min,min),('1',max,max))
73 #HASH LEFT SEMI JOIN2: [20660, 2, 732]; KEY_NUM(1); KEY(n.JGSY_CODE=DMTEMPVIEW_889234214.colname) KEY_NULL_EQU(0)
74 #NEST LOOP INDEX JOIN2: [1, 2, 732]
75 #BLKUP2: [1, 1, 156]; IDX_LOCAL_01(l)
76 #SSEK2: [1, 1, 156]; scan_type(ASC), IDX_LOCAL_01(LOCAL as l), scan_range[(NULL,min,min),(NULL,max,max))
77 #BLKUP2: [1, 2, 48]; IDX_LO_NEXT01(n)
78 #SSEK2: [1, 2, 48]; scan_type(ASC), IDX_LO_NEXT01(LO_NEXT as n), scan_range[(l.JGSY_CODE,'1'),(l.JGSY_CODE,'1')]
79 #PRJT2: [20659, 1145, 312]; exp_num(1), is_atom(FALSE)
80 #DISTINCT: [20659, 1145, 312]
81 #UNION FOR OR2: [20630, 80681, 312]; key_num(2), outer_join(-)
82 #SLCT2: [10308, 40340, 312]; n.N_CLASS < '1'
83 #HIERARCHICAL QUERY: [10281, 806815, 312]; KEY_NUM(0);
84 #SLCT2: [3, 702, 312]; l.L_CLASS <> '07'
85 #HASH2 INNER JOIN: [3, 702, 312]; RKEY_UNIQUE KEY_NUM(1); KEY(exp11=l.JGSY_CODE) KEY_NULL_EQU(0)
86 #BLKUP2: [1, 702, 156]; IDX_LONEXT_NCLASS(n)
87 #SSEK2: [1, 702, 156]; scan_type(ASC), IDX_LONEXT_NCLASS(LO_NEXT as n), scan_range['700','700']
88 #SSCN: [1, 9164, 156]; IDX_LOCAL_01(LOCAL as l)
89 #UNION FOR OR2: [14, 1149, 312]; key_num(2), outer_join(-)
90 #UNION FOR OR2: [2, 4, 312]; key_num(2), outer_join(-)
91 #UNION FOR OR2: [1, 2, 312]; key_num(2), outer_join(-)
92 #SLCT2: [1, 1, 312]; (var3 = exp11 AND l.JGSY_CODE = exp11)
93 #NEST LOOP INDEX JOIN2: [1, 1, 312]
94 #SSEK2: [1, 1, 156]; scan_type(ASC), IDX_LOCAL_01(LOCAL as l), scan_range[(NULL,min,min),(NULL,max,max))
95 #SSEK2: [1, 1, 144]; scan_type(ASC), IDX_LO_NEXT_01(LO_NEXT as n), scan_range[(NULL,min,min),(NULL,max,max))
96 #SLCT2: [1, 1, 312]; l.JGSY_STATES = '1'
97 #NEST LOOP INDEX JOIN2: [1, 1, 312]
98 #SLCT2: [1, 1, 156]; var3 = exp11
99 #SSEK2: [1, 1, 156]; scan_type(ASC), IDX_LO_NEXT_01(LO_NEXT as n), scan_range[(NULL,min,min),(NULL,max,max))
100 #BLKUP2: [1, 1, 48]; INDEX33555682(l)
101 #SSEK2: [1, 1, 48]; scan_type(ASC), INDEX33555682(LOCAL as l), scan_range[exp11,exp11]
102 #SLCT2: [1, 2, 312]; var3 = exp11
103 #NEST LOOP INDEX JOIN2: [1, 2, 312]
104 #SSEK2: [1, 1, 156]; scan_type(ASC), IDX_LOCAL_01(LOCAL as l), scan_range[(NULL,min,min),(NULL,max,max))
105 #BLKUP2: [1, 2, 48]; IDX_LO_NEXT01(n)
106 #SSEK2: [1, 2, 48]; scan_type(ASC), IDX_LO_NEXT01(LO_NEXT as n), scan_range[(l.JGSY_CODE,'1'),(l.JGSY_CODE,'1')]
107 #HASH2 INNER JOIN: [11, 1145, 312]; RKEY_UNIQUE KEY_NUM(1); KEY(exp11=l.JGSY_CODE) KEY_NULL_EQU(0)
108 #SLCT2: [8, 1145, 156]; var3 = exp11
109 #SSEK2: [8, 45808, 156]; scan_type(ASC), IDX_LO_NEXT_01(LO_NEXT as n), scan_range[('1',min,min),('1',max,max))
110 #SSEK2: [1, 7606, 156]; scan_type(ASC), IDX_LOCAL_01(LOCAL as l), scan_range[('1',min,min),('1',max,max))
111 #SLCT2: [10308, 40340, 312]; n.N_CLASS >= '2'
112 #HIERARCHICAL QUERY: [10281, 806815, 312]; KEY_NUM(0);
113 #SLCT2: [3, 702, 312]; l.L_CLASS <> '07'
114 #HASH2 INNER JOIN: [3, 702, 312]; RKEY_UNIQUE KEY_NUM(1); KEY(exp11=l.JGSY_CODE) KEY_NULL_EQU(0)
115 #BLKUP2: [1, 702, 156]; IDX_LONEXT_NCLASS(n)
116 #SSEK2: [1, 702, 156]; scan_type(ASC), IDX_LONEXT_NCLASS(LO_NEXT as n), scan_range['700','700']
117 #SSCN: [1, 9164, 156]; IDX_LOCAL_01(LOCAL as l)
118 #UNION FOR OR2: [14, 1149, 312]; key_num(2), outer_join(-)
119 #UNION FOR OR2: [2, 4, 312]; key_num(2), outer_join(-)
120 #UNION FOR OR2: [1, 2, 312]; key_num(2), outer_join(-)
121 #SLCT2: [1, 1, 312]; (var3 = exp11 AND l.JGSY_CODE = exp11)
122 #NEST LOOP INDEX JOIN2: [1, 1, 312]
123 #SSEK2: [1, 1, 156]; scan_type(ASC), IDX_LOCAL_01(LOCAL as l), scan_range[(NULL,min,min),(NULL,max,max))
124 #SSEK2: [1, 1, 144]; scan_type(ASC), IDX_LO_NEXT_01(LO_NEXT as n), scan_range[(NULL,min,min),(NULL,max,max))
125 #SLCT2: [1, 1, 312]; l.JGSY_STATES = '1'
126 #NEST LOOP INDEX JOIN2: [1, 1, 312]
127 #SLCT2: [1, 1, 156]; var3 = exp11
128 #SSEK2: [1, 1, 156]; scan_type(ASC), IDX_LO_NEXT_01(LO_NEXT as n), scan_range[(NULL,min,min),(NULL,max,max))
129 #BLKUP2: [1, 1, 48]; INDEX33555682(l)
130 #SSEK2: [1, 1, 48]; scan_type(ASC), INDEX33555682(LOCAL as l), scan_range[exp11,exp11]
131 #SLCT2: [1, 2, 312]; var3 = exp11
132 #NEST LOOP INDEX JOIN2: [1, 2, 312]
133 #SSEK2: [1, 1, 156]; scan_type(ASC), IDX_LOCAL_01(LOCAL as l), scan_range[(NULL,min,min),(NULL,max,max))
134 #BLKUP2: [1, 2, 48]; IDX_LO_NEXT01(n)
135 #SSEK2: [1, 2, 48]; scan_type(ASC), IDX_LO_NEXT01(LO_NEXT as n), scan_range[(l.JGSY_CODE,'1'),(l.JGSY_CODE,'1')]
136 #HASH2 INNER JOIN: [11, 1145, 312]; RKEY_UNIQUE KEY_NUM(1); KEY(exp11=l.JGSY_CODE) KEY_NULL_EQU(0)
137 #SLCT2: [8, 1145, 156]; var3 = exp11
138 #SSEK2: [8, 45808, 156]; scan_type(ASC), IDX_LO_NEXT_01(LO_NEXT as n), scan_range[('1',min,min),('1',max,max))
139 #SSEK2: [1, 7606, 156]; scan_type(ASC), IDX_LOCAL_01(LOCAL as l), scan_range[('1',min,min),('1',max,max))
140 #SLCT2: [20660, 1, 1044]; l.JGSY_STATES = '1'
141 #NEST LOOP INDEX JOIN2: [20660, 1, 1044]
142 #HASH2 INNER JOIN: [20660, 1, 888]; LKEY_UNIQUE KEY_NUM(1); KEY(n.JGSY_CODE=DMTEMPVIEW_889234216.colname) KEY_NULL_EQU(0)
143 #BLKUP2: [1, 1, 576]; IDX_LO_NEXT_01(n)
144 #SSEK2: [1, 1, 576]; scan_type(ASC), IDX_LO_NEXT_01(LO_NEXT as n), scan_range[(NULL,min,min),(NULL,max,max))
145 #PRJT2: [20659, 1145, 312]; exp_num(1), is_atom(FALSE)
146 #DISTINCT: [20659, 1145, 312]
147 #UNION FOR OR2: [20630, 80681, 312]; key_num(2), outer_join(-)
148 #SLCT2: [10308, 40340, 312]; n.N_CLASS < '1'
149 #HIERARCHICAL QUERY: [10281, 806815, 312]; KEY_NUM(0);
150 #SLCT2: [3, 702, 312]; l.L_CLASS <> '07'
151 #HASH2 INNER JOIN: [3, 702, 312]; RKEY_UNIQUE KEY_NUM(1); KEY(exp11=l.JGSY_CODE) KEY_NULL_EQU(0)
152 #BLKUP2: [1, 702, 156]; IDX_LONEXT_NCLASS(n)
153 #SSEK2: [1, 702, 156]; scan_type(ASC), IDX_LONEXT_NCLASS(LO_NEXT as n), scan_range['700','700']
154 #SSCN: [1, 9164, 156]; IDX_LOCAL_01(LOCAL as l)
155 #UNION FOR OR2: [14, 1149, 312]; key_num(2), outer_join(-)
156 #UNION FOR OR2: [2, 4, 312]; key_num(2), outer_join(-)
157 #UNION FOR OR2: [1, 2, 312]; key_num(2), outer_join(-)
158 #SLCT2: [1, 1, 312]; (var4 = exp11 AND l.JGSY_CODE = exp11)
159 #NEST LOOP INDEX JOIN2: [1, 1, 312]
160 #SSEK2: [1, 1, 156]; scan_type(ASC), IDX_LOCAL_01(LOCAL as l), scan_range[(NULL,min,min),(NULL,max,max))
161 #SSEK2: [1, 1, 144]; scan_type(ASC), IDX_LO_NEXT_01(LO_NEXT as n), scan_range[(NULL,min,min),(NULL,max,max))
162 #SLCT2: [1, 1, 312]; l.JGSY_STATES = '1'
163 #NEST LOOP INDEX JOIN2: [1, 1, 312]
164 #SLCT2: [1, 1, 156]; var4 = exp11
165 #SSEK2: [1, 1, 156]; scan_type(ASC), IDX_LO_NEXT_01(LO_NEXT as n), scan_range[(NULL,min,min),(NULL,max,max))
166 #BLKUP2: [1, 1, 48]; INDEX33555682(l)
167 #SSEK2: [1, 1, 48]; scan_type(ASC), INDEX33555682(LOCAL as l), scan_range[exp11,exp11]
168 #SLCT2: [1, 2, 312]; var4 = exp11
169 #NEST LOOP INDEX JOIN2: [1, 2, 312]
170 #SSEK2: [1, 1, 156]; scan_type(ASC), IDX_LOCAL_01(LOCAL as l), scan_range[(NULL,min,min),(NULL,max,max))
171 #BLKUP2: [1, 2, 48]; IDX_LO_NEXT01(n)
172 #SSEK2: [1, 2, 48]; scan_type(ASC), IDX_LO_NEXT01(LO_NEXT as n), scan_range[(l.JGSY_CODE,'1'),(l.JGSY_CODE,'1')]
173 #HASH2 INNER JOIN: [11, 1145, 312]; RKEY_UNIQUE KEY_NUM(1); KEY(exp11=l.JGSY_CODE) KEY_NULL_EQU(0)
174 #SLCT2: [8, 1145, 156]; var4 = exp11
175 #SSEK2: [8, 45808, 156]; scan_type(ASC), IDX_LO_NEXT_01(LO_NEXT as n), scan_range[('1',min,min),('1',max,max))
176 #SSEK2: [1, 7606, 156]; scan_type(ASC), IDX_LOCAL_01(LOCAL as l), scan_range[('1',min,min),('1',max,max))
177 #SLCT2: [10308, 40340, 312]; n.N_CLASS >= '2'
178 #HIERARCHICAL QUERY: [10281, 806815, 312]; KEY_NUM(0);
179 #SLCT2: [3, 702, 312]; l.L_CLASS <> '07'
180 #HASH2 INNER JOIN: [3, 702, 312]; RKEY_UNIQUE KEY_NUM(1); KEY(exp11=l.JGSY_CODE) KEY_NULL_EQU(0)
181 #BLKUP2: [1, 702, 156]; IDX_LONEXT_NCLASS(n)
182 #SSEK2: [1, 702, 156]; scan_type(ASC), IDX_LONEXT_NCLASS(LO_NEXT as n), scan_range['700','700']
183 #SSCN: [1, 9164, 156]; IDX_LOCAL_01(LOCAL as l)
184 #UNION FOR OR2: [14, 1149, 312]; key_num(2), outer_join(-)
185 #UNION FOR OR2: [2, 4, 312]; key_num(2), outer_join(-)
186 #UNION FOR OR2: [1, 2, 312]; key_num(2), outer_join(-)
187 #SLCT2: [1, 1, 312]; (var4 = exp11 AND l.JGSY_CODE = exp11)
188 #NEST LOOP INDEX JOIN2: [1, 1, 312]
189 #SSEK2: [1, 1, 156]; scan_type(ASC), IDX_LOCAL_01(LOCAL as l), scan_range[(NULL,min,min),(NULL,max,max))
190 #SSEK2: [1, 1, 144]; scan_type(ASC), IDX_LO_NEXT_01(LO_NEXT as n), scan_range[(NULL,min,min),(NULL,max,max))
191 #SLCT2: [1, 1, 312]; l.JGSY_STATES = '1'
192 #NEST LOOP INDEX JOIN2: [1, 1, 312]
193 #SLCT2: [1, 1, 156]; var4 = exp11
194 #SSEK2: [1, 1, 156]; scan_type(ASC), IDX_LO_NEXT_01(LO_NEXT as n), scan_range[(NULL,min,min),(NULL,max,max))
195 #BLKUP2: [1, 1, 48]; INDEX33555682(l)
196 #SSEK2: [1, 1, 48]; scan_type(ASC), INDEX33555682(LOCAL as l), scan_range[exp11,exp11]
197 #SLCT2: [1, 2, 312]; var4 = exp11
198 #NEST LOOP INDEX JOIN2: [1, 2, 312]
199 #SSEK2: [1, 1, 156]; scan_type(ASC), IDX_LOCAL_01(LOCAL as l), scan_range[(NULL,min,min),(NULL,max,max))
200 #BLKUP2: [1, 2, 48]; IDX_LO_NEXT01(n)
201 #SSEK2: [1, 2, 48]; scan_type(ASC), IDX_LO_NEXT01(LO_NEXT as n), scan_range[(l.JGSY_CODE,'1'),(l.JGSY_CODE,'1')]
202 #HASH2 INNER JOIN: [11, 1145, 312]; RKEY_UNIQUE KEY_NUM(1); KEY(exp11=l.JGSY_CODE) KEY_NULL_EQU(0)
203 #SLCT2: [8, 1145, 156]; var4 = exp11
204 #SSEK2: [8, 45808, 156]; scan_type(ASC), IDX_LO_NEXT_01(LO_NEXT as n), scan_range[('1',min,min),('1',max,max))
205 #SSEK2: [1, 7606, 156]; scan_type(ASC), IDX_LOCAL_01(LOCAL as l), scan_range[('1',min,min),('1',max,max))
206 #BLKUP2: [1, 1, 48]; INDEX33555682(l)
207 #SSEK2: [1, 1, 48]; scan_type(ASC), INDEX33555682(LOCAL as l), scan_range[exp11,exp11]
208 #HASH2 INNER JOIN: [20670, 1145, 1044]; RKEY_UNIQUE KEY_NUM(1); KEY(exp11=l.JGSY_CODE) KEY_NULL_EQU(0)
209 #SLCT2: [20667, 1145, 888]; n.JGSY_STATES = '1'
210 #NEST LOOP INDEX JOIN2: [20667, 1145, 888]
211 #PRJT2: [20659, 1145, 312]; exp_num(1), is_atom(FALSE)
212 #DISTINCT: [20659, 1145, 312]
213 #UNION FOR OR2: [20630, 80681, 312]; key_num(2), outer_join(-)
214 #SLCT2: [10308, 40340, 312]; < '1'
215 #HIERARCHICAL QUERY: [10281, 806815, 312]; KEY_NUM(0);
216 #SLCT2: [3, 702, 312]; l.L_CLASS <> '07'
217 #HASH2 INNER JOIN: [3, 702, 312]; RKEY_UNIQUE KEY_NUM(1); KEY(exp11=l.JGSY_CODE) KEY_NULL_EQU(0)
218 #BLKUP2: [1, 702, 156]; IDX_LONEXT_NCLASS(n)
219 #SSEK2: [1, 702, 156]; scan_type(ASC), IDX_LONEXT_NCLASS(LO_NEXT as n), scan_range['700','700']
220 #SSCN: [1, 9164, 156]; IDX_LOCAL_01(LOCAL as l)
221 #UNION FOR OR2: [14, 1149, 312]; key_num(2), outer_join(-)
222 #UNION FOR OR2: [2, 4, 312]; key_num(2), outer_join(-)
223 #UNION FOR OR2: [1, 2, 312]; key_num(2), outer_join(-)
224 #SLCT2: [1, 1, 312]; (var5 = exp11 AND l.JGSY_CODE = exp11)
225 #NEST LOOP INDEX JOIN2: [1, 1, 312]
226 #SSEK2: [1, 1, 156]; scan_type(ASC), IDX_LOCAL_01(LOCAL as l), scan_range[(NULL,min,min),(NULL,max,max))
227 #SSEK2: [1, 1, 144]; scan_type(ASC), IDX_LO_NEXT_01(LO_NEXT as n), scan_range[(NULL,min,min),(NULL,max,max))
228 #SLCT2: [1, 1, 312]; l.JGSY_STATES = '1'
229 #NEST LOOP INDEX JOIN2: [1, 1, 312]
230 #SLCT2: [1, 1, 156]; var5 = exp11
231 #SSEK2: [1, 1, 156]; scan_type(ASC), IDX_LO_NEXT_01(LO_NEXT as n), scan_range[(NULL,min,min),(NULL,max,max))
232 #BLKUP2: [1, 1, 48]; INDEX33555682(l)
233 #SSEK2: [1, 1, 48]; scan_type(ASC), INDEX33555682(LOCAL as l), scan_range[exp11,exp11]
234 #SLCT2: [1, 2, 312]; var5 = exp11
235 #NEST LOOP INDEX JOIN2: [1, 2, 312]
236 #SSEK2: [1, 1, 156]; scan_type(ASC), IDX_LOCAL_01(LOCAL as l), scan_range[(NULL,min,min),(NULL,max,max))
237 #BLKUP2: [1, 2, 48]; IDX_LO_NEXT01(n)
238 #SSEK2: [1, 2, 48]; scan_type(ASC), IDX_LO_NEXT01(LO_NEXT as n), scan_range[(l.JGSY_CODE,'1'),(l.JGSY_CODE,'1')]
239 #HASH2 INNER JOIN: [11, 1145, 312]; RKEY_UNIQUE KEY_NUM(1); KEY(exp11=l.JGSY_CODE) KEY_NULL_EQU(0)
240 #SLCT2: [8, 1145, 156]; var5 = exp11
241 #SSEK2: [8, 45808, 156]; scan_type(ASC), IDX_LO_NEXT_01(LO_NEXT as n), scan_range[('1',min,min),('1',max,max))
242 #SSEK2: [1, 7606, 156]; scan_type(ASC), IDX_LOCAL_01(LOCAL as l), scan_range[('1',min,min),('1',max,max))
243 #SLCT2: [10308, 40340, 312]; n.N_CLASS >= '2'
244 #HIERARCHICAL QUERY: [10281, 806815, 312]; KEY_NUM(0);
245 #SLCT2: [3, 702, 312]; l.L_CLASS <> '07'
246 #HASH2 INNER JOIN: [3, 702, 312]; RKEY_UNIQUE KEY_NUM(1); KEY(exp11=l.JGSY_CODE) KEY_NULL_EQU(0)
247 #BLKUP2: [1, 702, 156]; IDX_LONEXT_NCLASS(n)
248 #SSEK2: [1, 702, 156]; scan_type(ASC), IDX_LONEXT_NCLASS(LO_NEXT as n), scan_range['700','700']
249 #SSCN: [1, 9164, 156]; IDX_LOCAL_01(LOCAL as l)
250 #UNION FOR OR2: [14, 1149, 312]; key_num(2), outer_join(-)
251 #UNION FOR OR2: [2, 4, 312]; key_num(2), outer_join(-)
252 #UNION FOR OR2: [1, 2, 312]; key_num(2), outer_join(-)
253 #SLCT2: [1, 1, 312]; (var5 = exp11 AND l.JGSY_CODE = exp11)
254 #NEST LOOP INDEX JOIN2: [1, 1, 312]
255 #SSEK2: [1, 1, 156]; scan_type(ASC), IDX_LOCAL_01(LOCAL as l), scan_range[(NULL,min,min),(NULL,max,max))
256 #SSEK2: [1, 1, 144]; scan_type(ASC), IDX_LO_NEXT_01(LO_NEXT as n), scan_range[(NULL,min,min),(NULL,max,max))
257 #SLCT2: [1, 1, 312]; l.JGSY_STATES = '1'
258 #NEST LOOP INDEX JOIN2: [1, 1, 312]
259 #SLCT2: [1, 1, 156]; var5 = exp11
260 #SSEK2: [1, 1, 156]; scan_type(ASC), IDX_LO_NEXT_01(LO_NEXT as n), scan_range[(NULL,min,min),(NULL,max,max))
261 #BLKUP2: [1, 1, 48]; INDEX33555682(l)
262 #SSEK2: [1, 1, 48]; scan_type(ASC), INDEX33555682(LOCAL as l), scan_range[exp11,exp11]
263 #SLCT2: [1, 2, 312]; var5 = exp11
264 #NEST LOOP INDEX JOIN2: [1, 2, 312]
265 #SSEK2: [1, 1, 156]; scan_type(ASC), IDX_LOCAL_01(LOCAL as l), scan_range[(NULL,min,min),(NULL,max,max))
266 #BLKUP2: [1, 2, 48]; IDX_LO_NEXT01(n)
267 #SSEK2: [1, 2, 48]; scan_type(ASC), IDX_LO_NEXT01(LO_NEXT as n), scan_range[(l.JGSY_CODE,'1'),(l.JGSY_CODE,'1')]
268 #HASH2 INNER JOIN: [11, 1145, 312]; RKEY_UNIQUE KEY_NUM(1); KEY(exp11=l.JGSY_CODE) KEY_NULL_EQU(0)
269 #SLCT2: [8, 1145, 156]; var5 = exp11
270 #SSEK2: [8, 45808, 156]; scan_type(ASC), IDX_LO_NEXT_01(LO_NEXT as n), scan_range[('1',min,min),('1',max,max))
271 #SSEK2: [1, 7606, 156]; scan_type(ASC), IDX_LOCAL_01(LOCAL as l), scan_range[('1',min,min),('1',max,max))
272 #BLKUP2: [7, 1, 48]; INDEX33555681(n)
273 #SSEK2: [7, 1, 48]; scan_type(ASC), INDEX33555681(LO_NEXT as n), scan_range[DMTEMPVIEW_889234218.colname,DMTEMPVIEW_889234218.colname]
274 #SLCT2: [1, 7606, 156]; l.JGSY_STATES = '1'
275 #CSCN2: [1, 9164, 156]; INDEX33555532(LOCAL as l)
3 问题定位
3.1 SQL
START WITH N.N_AJASS = '777'
AND L.L_AJASS <> '07'
CONNECT BY NOCYCLE PRIOR N.AJBH_CODE = SUBSTR(N.AJBH_CODE, 1, LENGTH(N.AJBH_CODE) - 6)
3.2 执行计划
#HIERARCHICAL QUERY: [10281, 806815, 312]; KEY_NUM(0);--层次查询
4 解决方案
4.1 通过hint 方法测试sql
测试查询耗时在1秒以内
-- SELECT /*+ CNNTB_OPT_FLAG(31) */
SELECT
N.GEOCODE ,
L.L_AJBH ,
N.N_A_CODE AS CODE_BZ,
N.N_R_CODE AS CODE_ZZ
FROM
LAJBL L,
LANJN N
WHERE
L.AJBH_CODE = SUBSTR(N.AJBH_CODE, 1, 15)
AND
(
N.AJBH_STATES = '1'
OR N.AJBH_STATES IS NULL
)
AND
(
L.AJBH_STATES = '1'
OR L.AJBH_STATES IS NULL
)
AND N.AJBH_CODE IN
(
SELECT /*+ CNNTB_OPT_FLAG(31) */ DISTINCT
(N.AJBH_CODE)
FROM
LAJBL L,
LANJN N
WHERE
L.AJBH_CODE = SUBSTR(N.AJBH_CODE, 1, 15)
AND N.N_AJASS NOT LIKE '1%'
START WITH N.N_AJASS = '777'
AND L.L_AJASS <> '07' CONNECT BY NOCYCLE PRIOR N.AJBH_CODE = SUBSTR(N.AJBH_CODE, 1, LENGTH(N.AJBH_CODE) - 6)
AND
(
L.AJBH_STATES = '1'
OR L.AJBH_STATES IS NULL
)
AND
(
N.AJBH_STATES = '1'
OR N.AJBH_STATES IS NULL
)
)
4.2 修改dm.ini参数–问题解决
经过优化DM.INI
SP_SET_PARA_VALUE(1,'CNNTB_OPT_FLAG',31);
清空缓存：
CALL SP_CLEAR_PLAN_CACHE();
4.3 参数介绍：CNNTB_OPT_FLAG
794
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
