1
drop database if exists yy;
2
drop table if exists t1;
3
drop table if exists t2;
4
drop table if exists t4;
5
drop table if exists t5;
6
drop table if exists t6;
7
drop table if exists t11;
8
drop table if exists t12;
9
drop table if exists t13;
10
drop table if exists t15;
13
create table t1(c1 int(11) , c2 int(11) , c3 int(11), primary key (c2, c3)) partition by range columns(c2, c3) (partition p0 values less than (1,1), partition p1 values less than (100,100));
14
create table t2(c1 int(11) not null, c2 int(11) not null, c3 int(11) not null, primary key (c1, c2, c3)) partition by hash(c1) partitions 2;
15
create table t11(c1 int primary key, c2 int) partition by hash (c1) partitions 5;
16
create table t12(c1 int primary key, c2 int, c3 varchar(32)) partition by hash (c1) partitions 3;
17
create table t13(c1 int primary key, c2 int, c3 varchar(32)) partition by hash (c1) partitions 2;
18
create index idx_t11_c2 on t11(c2) LOCAL;
19
create table t15(c1 int, c2 int, c3 int, primary key(c2,c3)) partition by key(c2, c3) partitions 3;
20
create index idx_t15_c2 on t15(c2) LOCAL;
21
create index idx_t15_c3 on t15(c3) LOCAL;
22
explain select /*+use_nl(t1 t2)*/ * from t1 left join t2 on t1.c2= t2.c2 and t1.c3= t2.c3;
24
=======================================================================
25
|ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)|
26
-----------------------------------------------------------------------
27
|0 |PX COORDINATOR | |1 |8 |
28
|1 |└─EXCHANGE OUT DISTR |:EX10001|1 |7 |
29
|2 | └─NESTED-LOOP OUTER JOIN | |1 |5 |
30
|3 | ├─PX PARTITION ITERATOR | |1 |5 |
31
|4 | │ └─TABLE FULL SCAN |t1 |1 |5 |
32
|5 | └─MATERIAL | |1 |6 |
33
|6 | └─EXCHANGE IN DISTR | |1 |6 |
34
|7 | └─EXCHANGE OUT DISTR (PKEY)|:EX10000|1 |6 |
35
|8 | └─PX PARTITION ITERATOR | |1 |5 |
36
|9 | └─TABLE FULL SCAN |t2 |1 |5 |
37
=======================================================================
39
-------------------------------------
40
0 - output([INTERNAL_FUNCTION(t1.c1, t1.c2, t1.c3, t2.c1, t2.c2, t2.c3)]), filter(nil), rowset=16
41
1 - output([INTERNAL_FUNCTION(t1.c1, t1.c2, t1.c3, t2.c1, t2.c2, t2.c3)]), filter(nil), rowset=16
43
2 - output([t1.c2], [t2.c2], [t1.c3], [t2.c3], [t1.c1], [t2.c1]), filter(nil), rowset=16
44
conds([t1.c2 = t2.c2], [t1.c3 = t2.c3]), nl_params_(nil), use_batch=false
45
3 - output([t1.c2], [t1.c3], [t1.c1]), filter(nil), rowset=16
46
affinitize, force partition granule, asc
47
4 - output([t1.c2], [t1.c3], [t1.c1]), filter(nil), rowset=16
48
access([t1.c2], [t1.c3], [t1.c1]), partitions(p[0-1])
49
is_index_back=false, is_global_index=false,
50
range_key([t1.c2], [t1.c3]), range(MIN,MIN ; MAX,MAX)always true
51
5 - output([t2.c2], [t2.c3], [t2.c1]), filter(nil), rowset=16
52
6 - output([t2.c2], [t2.c3], [t2.c1]), filter(nil), rowset=16
53
7 - output([t2.c2], [t2.c3], [t2.c1]), filter(nil), rowset=16
54
(#keys=2, [t2.c2], [t2.c3]), dop=1
55
8 - output([t2.c1], [t2.c2], [t2.c3]), filter(nil), rowset=16
56
force partition granule
57
9 - output([t2.c1], [t2.c2], [t2.c3]), filter(nil), rowset=16
58
access([t2.c1], [t2.c2], [t2.c3]), partitions(p[0-1])
59
is_index_back=false, is_global_index=false,
60
range_key([t2.c1], [t2.c2], [t2.c3]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true
62
create table t1 (c1 int(11) , c2 int(11) , c3 int(11), primary key (c2, c3)) partition by key(c2) partitions 2;
63
explain select /*+use_nl(t1 t2)*/ * from t1 left join t2 on t1.c2= t2.c2 and t1.c3= t2.c3;
65
=======================================================================
66
|ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)|
67
-----------------------------------------------------------------------
68
|0 |PX COORDINATOR | |1 |8 |
69
|1 |└─EXCHANGE OUT DISTR |:EX10001|1 |7 |
70
|2 | └─NESTED-LOOP OUTER JOIN | |1 |5 |
71
|3 | ├─PX PARTITION ITERATOR | |1 |5 |
72
|4 | │ └─TABLE FULL SCAN |t1 |1 |5 |
73
|5 | └─MATERIAL | |1 |6 |
74
|6 | └─EXCHANGE IN DISTR | |1 |6 |
75
|7 | └─EXCHANGE OUT DISTR (PKEY)|:EX10000|1 |6 |
76
|8 | └─PX PARTITION ITERATOR | |1 |5 |
77
|9 | └─TABLE FULL SCAN |t2 |1 |5 |
78
=======================================================================
80
-------------------------------------
81
0 - output([INTERNAL_FUNCTION(t1.c1, t1.c2, t1.c3, t2.c1, t2.c2, t2.c3)]), filter(nil), rowset=16
82
1 - output([INTERNAL_FUNCTION(t1.c1, t1.c2, t1.c3, t2.c1, t2.c2, t2.c3)]), filter(nil), rowset=16
84
2 - output([t1.c2], [t2.c2], [t1.c3], [t2.c3], [t1.c1], [t2.c1]), filter(nil), rowset=16
85
conds([t1.c2 = t2.c2], [t1.c3 = t2.c3]), nl_params_(nil), use_batch=false
86
3 - output([t1.c2], [t1.c3], [t1.c1]), filter(nil), rowset=16
87
affinitize, force partition granule
88
4 - output([t1.c2], [t1.c3], [t1.c1]), filter(nil), rowset=16
89
access([t1.c2], [t1.c3], [t1.c1]), partitions(p[0-1])
90
is_index_back=false, is_global_index=false,
91
range_key([t1.c2], [t1.c3]), range(MIN,MIN ; MAX,MAX)always true
92
5 - output([t2.c2], [t2.c3], [t2.c1]), filter(nil), rowset=16
93
6 - output([t2.c2], [t2.c3], [t2.c1]), filter(nil), rowset=16
94
7 - output([t2.c2], [t2.c3], [t2.c1]), filter(nil), rowset=16
95
(#keys=1, [t2.c2]), dop=1
96
8 - output([t2.c1], [t2.c2], [t2.c3]), filter(nil), rowset=16
97
force partition granule
98
9 - output([t2.c1], [t2.c2], [t2.c3]), filter(nil), rowset=16
99
access([t2.c1], [t2.c2], [t2.c3]), partitions(p[0-1])
100
is_index_back=false, is_global_index=false,
101
range_key([t2.c1], [t2.c2], [t2.c3]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true
103
create table t1 (c1 int(11) , c2 int(11) , c3 int(11), c4 date, primary key (c2, c3)) partition by range columns(c2)
104
(partition p0 values less than (1), partition p1 values less than (100));
105
explain select * from t1 join t2 on t1.c2= t2.c2;
107
=====================================================================
108
|ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)|
109
---------------------------------------------------------------------
110
|0 |PX COORDINATOR | |1 |14 |
111
|1 |└─EXCHANGE OUT DISTR |:EX10001|1 |13 |
112
|2 | └─HASH JOIN | |1 |11 |
113
|3 | ├─EXCHANGE IN DISTR | |1 |6 |
114
|4 | │ └─EXCHANGE OUT DISTR (PKEY)|:EX10000|1 |6 |
115
|5 | │ └─PX PARTITION ITERATOR | |1 |5 |
116
|6 | │ └─TABLE FULL SCAN |t2 |1 |5 |
117
|7 | └─PX PARTITION ITERATOR | |1 |5 |
118
|8 | └─TABLE FULL SCAN |t1 |1 |5 |
119
=====================================================================
121
-------------------------------------
122
0 - output([INTERNAL_FUNCTION(t1.c1, t1.c2, t1.c3, t1.c4, t2.c1, t2.c2, t2.c3)]), filter(nil), rowset=16
123
1 - output([INTERNAL_FUNCTION(t1.c1, t1.c2, t1.c3, t1.c4, t2.c1, t2.c2, t2.c3)]), filter(nil), rowset=16
125
2 - output([t1.c2], [t2.c2], [t2.c1], [t2.c3], [t1.c3], [t1.c1], [t1.c4]), filter(nil), rowset=16
126
equal_conds([t1.c2 = t2.c2]), other_conds(nil)
127
3 - output([t2.c2], [t2.c1], [t2.c3]), filter(nil), rowset=16
128
4 - output([t2.c2], [t2.c1], [t2.c3]), filter(nil), rowset=16
129
(#keys=1, [t2.c2]), dop=1
130
5 - output([t2.c1], [t2.c2], [t2.c3]), filter(nil), rowset=16
131
force partition granule
132
6 - output([t2.c1], [t2.c2], [t2.c3]), filter(nil), rowset=16
133
access([t2.c1], [t2.c2], [t2.c3]), partitions(p[0-1])
134
is_index_back=false, is_global_index=false,
135
range_key([t2.c1], [t2.c2], [t2.c3]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true
136
7 - output([t1.c2], [t1.c3], [t1.c1], [t1.c4]), filter(nil), rowset=16
137
affinitize, force partition granule, asc
138
8 - output([t1.c2], [t1.c3], [t1.c1], [t1.c4]), filter(nil), rowset=16
139
access([t1.c2], [t1.c3], [t1.c1], [t1.c4]), partitions(p[0-1])
140
is_index_back=false, is_global_index=false,
141
range_key([t1.c2], [t1.c3]), range(MIN,MIN ; MAX,MAX)always true
142
explain select /*+use_nl(t1 t2)*/ * from t1 join t2 on t1.c3= t2.c3;
144
=================================================================
145
|ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)|
146
-----------------------------------------------------------------
147
|0 |NESTED-LOOP JOIN | |1 |7 |
148
|1 |├─PX COORDINATOR | |1 |7 |
149
|2 |│ └─EXCHANGE OUT DISTR |:EX10000|1 |6 |
150
|3 |│ └─PX PARTITION ITERATOR | |1 |5 |
151
|4 |│ └─TABLE FULL SCAN |t1 |1 |5 |
152
|5 |└─MATERIAL | |1 |6 |
153
|6 | └─PX COORDINATOR | |1 |6 |
154
|7 | └─EXCHANGE OUT DISTR |:EX20000|1 |6 |
155
|8 | └─PX PARTITION ITERATOR| |1 |5 |
156
|9 | └─TABLE FULL SCAN |t2 |1 |5 |
157
=================================================================
159
-------------------------------------
160
0 - output([t1.c1], [t1.c2], [t1.c3], [t1.c4], [t2.c1], [t2.c2], [t2.c3]), filter(nil), rowset=16
161
conds([t1.c3 = t2.c3]), nl_params_(nil), use_batch=false
162
1 - output([t1.c1], [t1.c2], [t1.c3], [t1.c4]), filter(nil), rowset=16
163
2 - output([t1.c1], [t1.c2], [t1.c3], [t1.c4]), filter(nil), rowset=16
165
3 - output([t1.c2], [t1.c3], [t1.c1], [t1.c4]), filter(nil), rowset=16
166
force partition granule, asc
167
4 - output([t1.c2], [t1.c3], [t1.c1], [t1.c4]), filter(nil), rowset=16
168
access([t1.c2], [t1.c3], [t1.c1], [t1.c4]), partitions(p[0-1])
169
is_index_back=false, is_global_index=false,
170
range_key([t1.c2], [t1.c3]), range(MIN,MIN ; MAX,MAX)always true
171
5 - output([t2.c1], [t2.c2], [t2.c3]), filter(nil), rowset=16
172
6 - output([t2.c1], [t2.c2], [t2.c3]), filter(nil), rowset=16
173
7 - output([t2.c1], [t2.c2], [t2.c3]), filter(nil), rowset=16
175
8 - output([t2.c1], [t2.c2], [t2.c3]), filter(nil), rowset=16
176
force partition granule
177
9 - output([t2.c1], [t2.c2], [t2.c3]), filter(nil), rowset=16
178
access([t2.c1], [t2.c2], [t2.c3]), partitions(p[0-1])
179
is_index_back=false, is_global_index=false,
180
range_key([t2.c1], [t2.c2], [t2.c3]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true
181
explain select /*+use_bnl(t1 t2)*/ * from t1 join t2 on t1.c3= t2.c3;
183
===============================================================
184
|ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)|
185
---------------------------------------------------------------
186
|0 |HASH JOIN | |1 |13 |
187
|1 |├─PX COORDINATOR | |1 |6 |
188
|2 |│ └─EXCHANGE OUT DISTR |:EX10000|1 |6 |
189
|3 |│ └─PX PARTITION ITERATOR| |1 |5 |
190
|4 |│ └─TABLE FULL SCAN |t2 |1 |5 |
191
|5 |└─PX COORDINATOR | |1 |7 |
192
|6 | └─EXCHANGE OUT DISTR |:EX20000|1 |6 |
193
|7 | └─PX PARTITION ITERATOR| |1 |5 |
194
|8 | └─TABLE FULL SCAN |t1 |1 |5 |
195
===============================================================
197
-------------------------------------
198
0 - output([t1.c1], [t1.c2], [t1.c3], [t1.c4], [t2.c1], [t2.c2], [t2.c3]), filter(nil), rowset=16
199
equal_conds([t1.c3 = t2.c3]), other_conds(nil)
200
1 - output([t2.c1], [t2.c2], [t2.c3]), filter(nil), rowset=16
201
2 - output([t2.c1], [t2.c2], [t2.c3]), filter(nil), rowset=16
203
3 - output([t2.c1], [t2.c2], [t2.c3]), filter(nil), rowset=16
204
force partition granule
205
4 - output([t2.c1], [t2.c2], [t2.c3]), filter(nil), rowset=16
206
access([t2.c1], [t2.c2], [t2.c3]), partitions(p[0-1])
207
is_index_back=false, is_global_index=false,
208
range_key([t2.c1], [t2.c2], [t2.c3]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true
209
5 - output([t1.c1], [t1.c2], [t1.c3], [t1.c4]), filter(nil), rowset=16
210
6 - output([t1.c1], [t1.c2], [t1.c3], [t1.c4]), filter(nil), rowset=16
212
7 - output([t1.c2], [t1.c3], [t1.c1], [t1.c4]), filter(nil), rowset=16
213
force partition granule, asc
214
8 - output([t1.c2], [t1.c3], [t1.c1], [t1.c4]), filter(nil), rowset=16
215
access([t1.c2], [t1.c3], [t1.c1], [t1.c4]), partitions(p[0-1])
216
is_index_back=false, is_global_index=false,
217
range_key([t1.c2], [t1.c3]), range(MIN,MIN ; MAX,MAX)always true
218
explain select /*+use_nl(t1 t2)*/ * from t1 left join t2 on t1.c2= t2.c2;
220
=======================================================================
221
|ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)|
222
-----------------------------------------------------------------------
223
|0 |PX COORDINATOR | |1 |8 |
224
|1 |└─EXCHANGE OUT DISTR |:EX10001|1 |7 |
225
|2 | └─NESTED-LOOP OUTER JOIN | |1 |5 |
226
|3 | ├─PX PARTITION ITERATOR | |1 |5 |
227
|4 | │ └─TABLE FULL SCAN |t1 |1 |5 |
228
|5 | └─MATERIAL | |1 |6 |
229
|6 | └─EXCHANGE IN DISTR | |1 |6 |
230
|7 | └─EXCHANGE OUT DISTR (PKEY)|:EX10000|1 |6 |
231
|8 | └─PX PARTITION ITERATOR | |1 |5 |
232
|9 | └─TABLE FULL SCAN |t2 |1 |5 |
233
=======================================================================
235
-------------------------------------
236
0 - output([INTERNAL_FUNCTION(t1.c1, t1.c2, t1.c3, t1.c4, t2.c1, t2.c2, t2.c3)]), filter(nil), rowset=16
237
1 - output([INTERNAL_FUNCTION(t1.c1, t1.c2, t1.c3, t1.c4, t2.c1, t2.c2, t2.c3)]), filter(nil), rowset=16
239
2 - output([t1.c2], [t2.c2], [t1.c3], [t1.c1], [t1.c4], [t2.c1], [t2.c3]), filter(nil), rowset=16
240
conds([t1.c2 = t2.c2]), nl_params_(nil), use_batch=false
241
3 - output([t1.c2], [t1.c3], [t1.c1], [t1.c4]), filter(nil), rowset=16
242
affinitize, force partition granule, asc
243
4 - output([t1.c2], [t1.c3], [t1.c1], [t1.c4]), filter(nil), rowset=16
244
access([t1.c2], [t1.c3], [t1.c1], [t1.c4]), partitions(p[0-1])
245
is_index_back=false, is_global_index=false,
246
range_key([t1.c2], [t1.c3]), range(MIN,MIN ; MAX,MAX)always true
247
5 - output([t2.c2], [t2.c1], [t2.c3]), filter(nil), rowset=16
248
6 - output([t2.c2], [t2.c1], [t2.c3]), filter(nil), rowset=16
249
7 - output([t2.c2], [t2.c1], [t2.c3]), filter(nil), rowset=16
250
(#keys=1, [t2.c2]), dop=1
251
8 - output([t2.c1], [t2.c2], [t2.c3]), filter(nil), rowset=16
252
force partition granule
253
9 - output([t2.c1], [t2.c2], [t2.c3]), filter(nil), rowset=16
254
access([t2.c1], [t2.c2], [t2.c3]), partitions(p[0-1])
255
is_index_back=false, is_global_index=false,
256
range_key([t2.c1], [t2.c2], [t2.c3]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true
257
explain select /*+use_nl(t1 t2)*/ * from t1 left join t2 on t1.c1= t2.c1;
259
=====================================================================
260
|ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)|
261
---------------------------------------------------------------------
262
|0 |PX COORDINATOR | |1 |29 |
263
|1 |└─EXCHANGE OUT DISTR |:EX10001|1 |28 |
264
|2 | └─NESTED-LOOP OUTER JOIN | |1 |25 |
265
|3 | ├─EXCHANGE IN DISTR | |1 |7 |
266
|4 | │ └─EXCHANGE OUT DISTR (PKEY)|:EX10000|1 |6 |
267
|5 | │ └─PX PARTITION ITERATOR | |1 |5 |
268
|6 | │ └─TABLE FULL SCAN |t1 |1 |5 |
269
|7 | └─PX PARTITION ITERATOR | |1 |36 |
270
|8 | └─TABLE RANGE SCAN |t2 |1 |36 |
271
=====================================================================
273
-------------------------------------
274
0 - output([INTERNAL_FUNCTION(t1.c1, t1.c2, t1.c3, t1.c4, t2.c1, t2.c2, t2.c3)]), filter(nil), rowset=16
275
1 - output([INTERNAL_FUNCTION(t1.c1, t1.c2, t1.c3, t1.c4, t2.c1, t2.c2, t2.c3)]), filter(nil), rowset=16
277
2 - output([t1.c1], [t1.c2], [t1.c3], [t1.c4], [t2.c1], [t2.c2], [t2.c3]), filter(nil), rowset=16
278
conds(nil), nl_params_([t1.c1(:0)]), use_batch=false
279
3 - output([PARTITION_ID], [t1.c1], [t1.c2], [t1.c3], [t1.c4]), filter(nil), rowset=16
280
4 - output([PARTITION_ID], [t1.c1], [t1.c2], [t1.c3], [t1.c4]), filter(nil), rowset=16
281
(#keys=1, [t1.c1]), dop=1
282
5 - output([t1.c2], [t1.c3], [t1.c1], [t1.c4]), filter(nil), rowset=16
283
force partition granule, asc
284
6 - output([t1.c2], [t1.c3], [t1.c1], [t1.c4]), filter(nil), rowset=16
285
access([t1.c2], [t1.c3], [t1.c1], [t1.c4]), partitions(p[0-1])
286
is_index_back=false, is_global_index=false,
287
range_key([t1.c2], [t1.c3]), range(MIN,MIN ; MAX,MAX)always true
288
7 - output([t2.c1], [t2.c2], [t2.c3]), filter(nil), rowset=16
289
affinitize, force partition granule
290
8 - output([t2.c1], [t2.c2], [t2.c3]), filter(nil), rowset=16
291
access([t2.c1], [t2.c2], [t2.c3]), partitions(p[0-1])
292
is_index_back=false, is_global_index=false,
293
range_key([t2.c1], [t2.c2], [t2.c3]), range(MIN ; MAX),
294
range_cond([:0 = t2.c1])
295
explain select /*+use_nl(t1 t2)*/ * from t1 left join t2 on t1.c2= t2.c2 and t1.c3= t2.c3;
297
=======================================================================
298
|ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)|
299
-----------------------------------------------------------------------
300
|0 |PX COORDINATOR | |1 |8 |
301
|1 |└─EXCHANGE OUT DISTR |:EX10001|1 |7 |
302
|2 | └─NESTED-LOOP OUTER JOIN | |1 |5 |
303
|3 | ├─PX PARTITION ITERATOR | |1 |5 |
304
|4 | │ └─TABLE FULL SCAN |t1 |1 |5 |
305
|5 | └─MATERIAL | |1 |6 |
306
|6 | └─EXCHANGE IN DISTR | |1 |6 |
307
|7 | └─EXCHANGE OUT DISTR (PKEY)|:EX10000|1 |6 |
308
|8 | └─PX PARTITION ITERATOR | |1 |5 |
309
|9 | └─TABLE FULL SCAN |t2 |1 |5 |
310
=======================================================================
312
-------------------------------------
313
0 - output([INTERNAL_FUNCTION(t1.c1, t1.c2, t1.c3, t1.c4, t2.c1, t2.c2, t2.c3)]), filter(nil), rowset=16
314
1 - output([INTERNAL_FUNCTION(t1.c1, t1.c2, t1.c3, t1.c4, t2.c1, t2.c2, t2.c3)]), filter(nil), rowset=16
316
2 - output([t1.c2], [t2.c2], [t1.c3], [t2.c3], [t1.c1], [t1.c4], [t2.c1]), filter(nil), rowset=16
317
conds([t1.c2 = t2.c2], [t1.c3 = t2.c3]), nl_params_(nil), use_batch=false
318
3 - output([t1.c2], [t1.c3], [t1.c1], [t1.c4]), filter(nil), rowset=16
319
affinitize, force partition granule, asc
320
4 - output([t1.c2], [t1.c3], [t1.c1], [t1.c4]), filter(nil), rowset=16
321
access([t1.c2], [t1.c3], [t1.c1], [t1.c4]), partitions(p[0-1])
322
is_index_back=false, is_global_index=false,
323
range_key([t1.c2], [t1.c3]), range(MIN,MIN ; MAX,MAX)always true
324
5 - output([t2.c2], [t2.c3], [t2.c1]), filter(nil), rowset=16
325
6 - output([t2.c2], [t2.c3], [t2.c1]), filter(nil), rowset=16
326
7 - output([t2.c2], [t2.c3], [t2.c1]), filter(nil), rowset=16
327
(#keys=1, [t2.c2]), dop=1
328
8 - output([t2.c1], [t2.c2], [t2.c3]), filter(nil), rowset=16
329
force partition granule
330
9 - output([t2.c1], [t2.c2], [t2.c3]), filter(nil), rowset=16
331
access([t2.c1], [t2.c2], [t2.c3]), partitions(p[0-1])
332
is_index_back=false, is_global_index=false,
333
range_key([t2.c1], [t2.c2], [t2.c3]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true
334
explain select /*+use_nl(t1 t2)*/ * from t1 left join t2 on t1.c1= t2.c1 and t1.c3= t2.c3;
336
=====================================================================
337
|ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)|
338
---------------------------------------------------------------------
339
|0 |PX COORDINATOR | |1 |29 |
340
|1 |└─EXCHANGE OUT DISTR |:EX10001|1 |28 |
341
|2 | └─NESTED-LOOP OUTER JOIN | |1 |25 |
342
|3 | ├─EXCHANGE IN DISTR | |1 |7 |
343
|4 | │ └─EXCHANGE OUT DISTR (PKEY)|:EX10000|1 |6 |
344
|5 | │ └─PX PARTITION ITERATOR | |1 |5 |
345
|6 | │ └─TABLE FULL SCAN |t1 |1 |5 |
346
|7 | └─PX PARTITION ITERATOR | |1 |36 |
347
|8 | └─TABLE RANGE SCAN |t2 |1 |36 |
348
=====================================================================
350
-------------------------------------
351
0 - output([INTERNAL_FUNCTION(t1.c1, t1.c2, t1.c3, t1.c4, t2.c1, t2.c2, t2.c3)]), filter(nil), rowset=16
352
1 - output([INTERNAL_FUNCTION(t1.c1, t1.c2, t1.c3, t1.c4, t2.c1, t2.c2, t2.c3)]), filter(nil), rowset=16
354
2 - output([t1.c1], [t1.c3], [t1.c2], [t1.c4], [t2.c1], [t2.c2], [t2.c3]), filter(nil), rowset=16
355
conds(nil), nl_params_([t1.c1(:0)], [t1.c3(:1)]), use_batch=false
356
3 - output([PARTITION_ID], [t1.c1], [t1.c3], [t1.c2], [t1.c4]), filter(nil), rowset=16
357
4 - output([PARTITION_ID], [t1.c1], [t1.c3], [t1.c2], [t1.c4]), filter(nil), rowset=16
358
(#keys=1, [t1.c1]), dop=1
359
5 - output([t1.c2], [t1.c3], [t1.c1], [t1.c4]), filter(nil), rowset=16
360
force partition granule, asc
361
6 - output([t1.c2], [t1.c3], [t1.c1], [t1.c4]), filter(nil), rowset=16
362
access([t1.c2], [t1.c3], [t1.c1], [t1.c4]), partitions(p[0-1])
363
is_index_back=false, is_global_index=false,
364
range_key([t1.c2], [t1.c3]), range(MIN,MIN ; MAX,MAX)always true
365
7 - output([t2.c1], [t2.c2], [t2.c3]), filter(nil), rowset=16
366
affinitize, force partition granule
367
8 - output([t2.c1], [t2.c2], [t2.c3]), filter([:1 = t2.c3]), rowset=16
368
access([t2.c1], [t2.c2], [t2.c3]), partitions(p[0-1])
369
is_index_back=false, is_global_index=false, filter_before_indexback[false],
370
range_key([t2.c1], [t2.c2], [t2.c3]), range(MIN ; MAX),
371
range_cond([:0 = t2.c1])
372
explain select /*+use_nl(t1 t2)*/ * from t1 join t2 on t1.c2= t2.c2;
374
=======================================================================
375
|ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)|
376
-----------------------------------------------------------------------
377
|0 |PX COORDINATOR | |1 |8 |
378
|1 |└─EXCHANGE OUT DISTR |:EX10001|1 |7 |
379
|2 | └─NESTED-LOOP JOIN | |1 |5 |
380
|3 | ├─PX PARTITION ITERATOR | |1 |5 |
381
|4 | │ └─TABLE FULL SCAN |t1 |1 |5 |
382
|5 | └─MATERIAL | |1 |6 |
383
|6 | └─EXCHANGE IN DISTR | |1 |6 |
384
|7 | └─EXCHANGE OUT DISTR (PKEY)|:EX10000|1 |6 |
385
|8 | └─PX PARTITION ITERATOR | |1 |5 |
386
|9 | └─TABLE FULL SCAN |t2 |1 |5 |
387
=======================================================================
389
-------------------------------------
390
0 - output([INTERNAL_FUNCTION(t1.c1, t1.c2, t1.c3, t1.c4, t2.c1, t2.c2, t2.c3)]), filter(nil), rowset=16
391
1 - output([INTERNAL_FUNCTION(t1.c1, t1.c2, t1.c3, t1.c4, t2.c1, t2.c2, t2.c3)]), filter(nil), rowset=16
393
2 - output([t1.c2], [t2.c2], [t1.c3], [t1.c1], [t1.c4], [t2.c1], [t2.c3]), filter(nil), rowset=16
394
conds([t1.c2 = t2.c2]), nl_params_(nil), use_batch=false
395
3 - output([t1.c2], [t1.c3], [t1.c1], [t1.c4]), filter(nil), rowset=16
396
affinitize, force partition granule, asc
397
4 - output([t1.c2], [t1.c3], [t1.c1], [t1.c4]), filter(nil), rowset=16
398
access([t1.c2], [t1.c3], [t1.c1], [t1.c4]), partitions(p[0-1])
399
is_index_back=false, is_global_index=false,
400
range_key([t1.c2], [t1.c3]), range(MIN,MIN ; MAX,MAX)always true
401
5 - output([t2.c2], [t2.c1], [t2.c3]), filter(nil), rowset=16
402
6 - output([t2.c2], [t2.c1], [t2.c3]), filter(nil), rowset=16
403
7 - output([t2.c2], [t2.c1], [t2.c3]), filter(nil), rowset=16
404
(#keys=1, [t2.c2]), dop=1
405
8 - output([t2.c1], [t2.c2], [t2.c3]), filter(nil), rowset=16
406
force partition granule
407
9 - output([t2.c1], [t2.c2], [t2.c3]), filter(nil), rowset=16
408
access([t2.c1], [t2.c2], [t2.c3]), partitions(p[0-1])
409
is_index_back=false, is_global_index=false,
410
range_key([t2.c1], [t2.c2], [t2.c3]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true
411
explain select /*+use_nl(t1 t2)*/ * from t1 join t2 on t1.c1= t2.c1;
413
=======================================================================
414
|ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)|
415
-----------------------------------------------------------------------
416
|0 |PX COORDINATOR | |1 |8 |
417
|1 |└─EXCHANGE OUT DISTR |:EX10001|1 |7 |
418
|2 | └─NESTED-LOOP JOIN | |1 |5 |
419
|3 | ├─PX PARTITION ITERATOR | |1 |5 |
420
|4 | │ └─TABLE FULL SCAN |t2 |1 |5 |
421
|5 | └─MATERIAL | |1 |7 |
422
|6 | └─EXCHANGE IN DISTR | |1 |7 |
423
|7 | └─EXCHANGE OUT DISTR (PKEY)|:EX10000|1 |6 |
424
|8 | └─PX PARTITION ITERATOR | |1 |5 |
425
|9 | └─TABLE FULL SCAN |t1 |1 |5 |
426
=======================================================================
428
-------------------------------------
429
0 - output([INTERNAL_FUNCTION(t1.c1, t1.c2, t1.c3, t1.c4, t2.c1, t2.c2, t2.c3)]), filter(nil), rowset=16
430
1 - output([INTERNAL_FUNCTION(t1.c1, t1.c2, t1.c3, t1.c4, t2.c1, t2.c2, t2.c3)]), filter(nil), rowset=16
432
2 - output([t1.c1], [t2.c1], [t2.c2], [t2.c3], [t1.c2], [t1.c3], [t1.c4]), filter(nil), rowset=16
433
conds([t1.c1 = t2.c1]), nl_params_(nil), use_batch=false
434
3 - output([t2.c1], [t2.c2], [t2.c3]), filter(nil), rowset=16
435
affinitize, force partition granule
436
4 - output([t2.c1], [t2.c2], [t2.c3]), filter(nil), rowset=16
437
access([t2.c1], [t2.c2], [t2.c3]), partitions(p[0-1])
438
is_index_back=false, is_global_index=false,
439
range_key([t2.c1], [t2.c2], [t2.c3]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true
440
5 - output([t1.c1], [t1.c2], [t1.c3], [t1.c4]), filter(nil), rowset=16
441
6 - output([t1.c1], [t1.c2], [t1.c3], [t1.c4]), filter(nil), rowset=16
442
7 - output([t1.c1], [t1.c2], [t1.c3], [t1.c4]), filter(nil), rowset=16
443
(#keys=1, [t1.c1]), dop=1
444
8 - output([t1.c2], [t1.c3], [t1.c1], [t1.c4]), filter(nil), rowset=16
445
force partition granule, asc
446
9 - output([t1.c2], [t1.c3], [t1.c1], [t1.c4]), filter(nil), rowset=16
447
access([t1.c2], [t1.c3], [t1.c1], [t1.c4]), partitions(p[0-1])
448
is_index_back=false, is_global_index=false,
449
range_key([t1.c2], [t1.c3]), range(MIN,MIN ; MAX,MAX)always true
450
explain select /*+use_nl(t1 t2)*/ * from t1 join t2 on t1.c2= t2.c2 and t1.c3= t2.c3;
452
=======================================================================
453
|ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)|
454
-----------------------------------------------------------------------
455
|0 |PX COORDINATOR | |1 |8 |
456
|1 |└─EXCHANGE OUT DISTR |:EX10001|1 |7 |
457
|2 | └─NESTED-LOOP JOIN | |1 |5 |
458
|3 | ├─PX PARTITION ITERATOR | |1 |5 |
459
|4 | │ └─TABLE FULL SCAN |t1 |1 |5 |
460
|5 | └─MATERIAL | |1 |6 |
461
|6 | └─EXCHANGE IN DISTR | |1 |6 |
462
|7 | └─EXCHANGE OUT DISTR (PKEY)|:EX10000|1 |6 |
463
|8 | └─PX PARTITION ITERATOR | |1 |5 |
464
|9 | └─TABLE FULL SCAN |t2 |1 |5 |
465
=======================================================================
467
-------------------------------------
468
0 - output([INTERNAL_FUNCTION(t1.c1, t1.c2, t1.c3, t1.c4, t2.c1, t2.c2, t2.c3)]), filter(nil), rowset=16
469
1 - output([INTERNAL_FUNCTION(t1.c1, t1.c2, t1.c3, t1.c4, t2.c1, t2.c2, t2.c3)]), filter(nil), rowset=16
471
2 - output([t1.c2], [t2.c2], [t1.c3], [t2.c3], [t1.c1], [t1.c4], [t2.c1]), filter(nil), rowset=16
472
conds([t1.c2 = t2.c2], [t1.c3 = t2.c3]), nl_params_(nil), use_batch=false
473
3 - output([t1.c2], [t1.c3], [t1.c1], [t1.c4]), filter(nil), rowset=16
474
affinitize, force partition granule, asc
475
4 - output([t1.c2], [t1.c3], [t1.c1], [t1.c4]), filter(nil), rowset=16
476
access([t1.c2], [t1.c3], [t1.c1], [t1.c4]), partitions(p[0-1])
477
is_index_back=false, is_global_index=false,
478
range_key([t1.c2], [t1.c3]), range(MIN,MIN ; MAX,MAX)always true
479
5 - output([t2.c2], [t2.c3], [t2.c1]), filter(nil), rowset=16
480
6 - output([t2.c2], [t2.c3], [t2.c1]), filter(nil), rowset=16
481
7 - output([t2.c2], [t2.c3], [t2.c1]), filter(nil), rowset=16
482
(#keys=1, [t2.c2]), dop=1
483
8 - output([t2.c1], [t2.c2], [t2.c3]), filter(nil), rowset=16
484
force partition granule
485
9 - output([t2.c1], [t2.c2], [t2.c3]), filter(nil), rowset=16
486
access([t2.c1], [t2.c2], [t2.c3]), partitions(p[0-1])
487
is_index_back=false, is_global_index=false,
488
range_key([t2.c1], [t2.c2], [t2.c3]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true
489
explain select c1 from t11 where c2 not in (select c2 from t11);
491
===============================================================
492
|ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)|
493
---------------------------------------------------------------
494
|0 |HASH RIGHT ANTI JOIN NA | |1 |23 |
495
|1 |├─PX COORDINATOR | |1 |12 |
496
|2 |│ └─EXCHANGE OUT DISTR |:EX10000|1 |11 |
497
|3 |│ └─PX PARTITION ITERATOR| |1 |11 |
498
|4 |│ └─TABLE FULL SCAN |t11 |1 |11 |
499
|5 |└─PX COORDINATOR | |1 |12 |
500
|6 | └─EXCHANGE OUT DISTR |:EX20000|1 |12 |
501
|7 | └─PX PARTITION ITERATOR| |1 |11 |
502
|8 | └─TABLE FULL SCAN |t11 |1 |11 |
503
===============================================================
505
-------------------------------------
506
0 - output([t11.c1]), filter(nil), rowset=16
507
equal_conds([t11.c2 = t11.c2]), other_conds(nil)
508
1 - output([t11.c2]), filter(nil), rowset=16
509
2 - output([t11.c2]), filter(nil), rowset=16
511
3 - output([t11.c2]), filter(nil), rowset=16
512
force partition granule
513
4 - output([t11.c2]), filter(nil), rowset=16
514
access([t11.c2]), partitions(p[0-4])
515
is_index_back=false, is_global_index=false,
516
range_key([t11.c1]), range(MIN ; MAX)always true
517
5 - output([t11.c1], [t11.c2]), filter(nil), rowset=16
518
6 - output([t11.c1], [t11.c2]), filter(nil), rowset=16
520
7 - output([t11.c1], [t11.c2]), filter(nil), rowset=16
521
force partition granule
522
8 - output([t11.c1], [t11.c2]), filter(nil), rowset=16
523
access([t11.c1], [t11.c2]), partitions(p[0-4])
524
is_index_back=false, is_global_index=false,
525
range_key([t11.c1]), range(MIN ; MAX)always true
526
explain select c1 from t12 where c1 not in (select c1 from t11 where c2 not in (select c2 from t11));
528
===========================================================================
529
|ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)|
530
---------------------------------------------------------------------------
531
|0 |PX COORDINATOR | |1 |30 |
532
|1 |└─EXCHANGE OUT DISTR |:EX10003|1 |30 |
533
|2 | └─HASH RIGHT ANTI JOIN | |1 |30 |
534
|3 | ├─EXCHANGE IN DISTR | |1 |23 |
535
|4 | │ └─EXCHANGE OUT DISTR (PKEY) |:EX10002|1 |23 |
536
|5 | │ └─SUBPLAN SCAN |VIEW2 |1 |23 |
537
|6 | │ └─HASH RIGHT ANTI JOIN NA | |1 |23 |
538
|7 | │ ├─EXCHANGE IN DISTR | |1 |12 |
539
|8 | │ │ └─EXCHANGE OUT DISTR |:EX10000|1 |11 |
540
|9 | │ │ └─PX PARTITION ITERATOR| |1 |11 |
541
|10| │ │ └─TABLE FULL SCAN |t11 |1 |11 |
542
|11| │ └─EXCHANGE IN DISTR | |1 |12 |
543
|12| │ └─EXCHANGE OUT DISTR |:EX10001|1 |12 |
544
|13| │ └─PX PARTITION ITERATOR| |1 |11 |
545
|14| │ └─TABLE FULL SCAN |t11 |1 |11 |
546
|15| └─PX PARTITION ITERATOR | |1 |7 |
547
|16| └─TABLE FULL SCAN |t12 |1 |7 |
548
===========================================================================
550
-------------------------------------
551
0 - output([INTERNAL_FUNCTION(t12.c1)]), filter(nil), rowset=16
552
1 - output([INTERNAL_FUNCTION(t12.c1)]), filter(nil), rowset=16
554
2 - output([t12.c1]), filter(nil), rowset=16
555
equal_conds([t12.c1 = VIEW2.c1]), other_conds(nil)
556
3 - output([VIEW2.c1]), filter(nil), rowset=16
557
4 - output([VIEW2.c1]), filter(nil), rowset=16
558
(#keys=1, [VIEW2.c1]), is_single, dop=1
559
5 - output([VIEW2.c1]), filter(nil), rowset=16
561
6 - output([t11.c1]), filter(nil), rowset=16
562
equal_conds([t11.c2 = t11.c2]), other_conds(nil)
563
7 - output([t11.c2]), filter(nil), rowset=16
564
8 - output([t11.c2]), filter(nil), rowset=16
566
9 - output([t11.c2]), filter(nil), rowset=16
567
force partition granule
568
10 - output([t11.c2]), filter(nil), rowset=16
569
access([t11.c2]), partitions(p[0-4])
570
is_index_back=false, is_global_index=false,
571
range_key([t11.c1]), range(MIN ; MAX)always true
572
11 - output([t11.c1], [t11.c2]), filter(nil), rowset=16
573
12 - output([t11.c1], [t11.c2]), filter(nil), rowset=16
575
13 - output([t11.c1], [t11.c2]), filter(nil), rowset=16
576
force partition granule
577
14 - output([t11.c1], [t11.c2]), filter(nil), rowset=16
578
access([t11.c1], [t11.c2]), partitions(p[0-4])
579
is_index_back=false, is_global_index=false,
580
range_key([t11.c1]), range(MIN ; MAX)always true
581
15 - output([t12.c1]), filter(nil), rowset=16
582
affinitize, force partition granule
583
16 - output([t12.c1]), filter(nil), rowset=16
584
access([t12.c1]), partitions(p[0-2])
585
is_index_back=false, is_global_index=false,
586
range_key([t12.c1]), range(MIN ; MAX)always true
587
explain select * from (select /*+use_nl(t11 t12)*/ t12.c1 from t11,t12 where t11.c1=t12.c1 limit 1) v order by v.c1;
589
=============================================================================
590
|ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)|
591
-----------------------------------------------------------------------------
593
|1 |└─SUBPLAN SCAN |v |1 |27 |
594
|2 | └─LIMIT | |1 |27 |
595
|3 | └─PX COORDINATOR | |1 |27 |
596
|4 | └─EXCHANGE OUT DISTR |:EX10001|1 |26 |
597
|5 | └─LIMIT | |1 |26 |
598
|6 | └─NESTED-LOOP JOIN | |1 |26 |
599
|7 | ├─EXCHANGE IN DISTR | |1 |7 |
600
|8 | │ └─EXCHANGE OUT DISTR (PKEY)|:EX10000|1 |7 |
601
|9 | │ └─PX PARTITION ITERATOR | |1 |7 |
602
|10| │ └─TABLE FULL SCAN |t12 |1 |7 |
603
|11| └─PX PARTITION ITERATOR | |1 |90 |
604
|12| └─TABLE GET |t11 |1 |90 |
605
=============================================================================
607
-------------------------------------
608
0 - output([v.c1]), filter(nil), rowset=16
609
sort_keys([v.c1, ASC]), local merge sort
610
1 - output([v.c1]), filter(nil), rowset=16
612
2 - output([t12.c1]), filter(nil), rowset=16
613
limit(1), offset(nil)
614
3 - output([t12.c1]), filter(nil), rowset=16
615
4 - output([t12.c1]), filter(nil), rowset=16
617
5 - output([t12.c1]), filter(nil), rowset=16
618
limit(1), offset(nil)
619
6 - output([t12.c1]), filter(nil), rowset=16
620
conds(nil), nl_params_([t12.c1(:0)]), use_batch=false
621
7 - output([t12.c1], [PARTITION_ID]), filter(nil), rowset=16
622
8 - output([t12.c1], [PARTITION_ID]), filter(nil), rowset=16
623
(#keys=1, [t12.c1]), dop=1
624
9 - output([t12.c1]), filter(nil), rowset=16
625
force partition granule
626
10 - output([t12.c1]), filter(nil), rowset=16
627
access([t12.c1]), partitions(p[0-2])
628
is_index_back=false, is_global_index=false,
629
range_key([t12.c1]), range(MIN ; MAX)always true
630
11 - output(nil), filter(nil), rowset=16
631
affinitize, force partition granule
632
12 - output(nil), filter(nil), rowset=16
633
access(nil), partitions(p[0-4])
634
is_index_back=false, is_global_index=false,
635
range_key([t11.c1]), range(MIN ; MAX),
636
range_cond([t11.c1 = :0])
637
explain select count(c1) from t11 group by c1 having c1 in (select t12.c1 from t12 where t11.c1=t12.c2);
639
=====================================================================
640
|ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)|
641
---------------------------------------------------------------------
642
|0 |PX COORDINATOR | |1 |19 |
643
|1 |└─EXCHANGE OUT DISTR |:EX10001|1 |19 |
644
|2 | └─HASH JOIN | |1 |18 |
645
|3 | ├─EXCHANGE IN DISTR | |1 |12 |
646
|4 | │ └─EXCHANGE OUT DISTR (PKEY)|:EX10000|1 |11 |
647
|5 | │ └─PX PARTITION ITERATOR | |1 |11 |
648
|6 | │ └─TABLE FULL SCAN |t11 |1 |11 |
649
|7 | └─PX PARTITION ITERATOR | |1 |7 |
650
|8 | └─TABLE FULL SCAN |t12 |1 |7 |
651
=====================================================================
653
-------------------------------------
654
0 - output([INTERNAL_FUNCTION(CASE WHEN t11.c1 IS NOT NULL THEN 1 ELSE 0 END)]), filter(nil), rowset=16
655
1 - output([INTERNAL_FUNCTION(CASE WHEN t11.c1 IS NOT NULL THEN 1 ELSE 0 END)]), filter(nil), rowset=16
657
2 - output([t11.c1]), filter(nil), rowset=16
658
equal_conds([t11.c1 = t12.c2]), other_conds(nil)
659
3 - output([t11.c1]), filter(nil), rowset=16
660
4 - output([t11.c1]), filter(nil), rowset=16
661
(#keys=1, [t11.c1]), dop=1
662
5 - output([t11.c1]), filter(nil), rowset=16
663
force partition granule
664
6 - output([t11.c1]), filter(nil), rowset=16
665
access([t11.c1]), partitions(p[0-4])
666
is_index_back=false, is_global_index=false,
667
range_key([t11.c1]), range(MIN ; MAX)always true
668
7 - output([t12.c2]), filter(nil), rowset=16
669
affinitize, force partition granule
670
8 - output([t12.c2]), filter([t12.c2 = t12.c1]), rowset=16
671
access([t12.c1], [t12.c2]), partitions(p[0-2])
672
is_index_back=false, is_global_index=false, filter_before_indexback[false],
673
range_key([t12.c1]), range(MIN ; MAX)always true
674
explain select * from t11 where c1 in (select c1 from t12 where (1 = 0 or c1 > 5));
676
=====================================================================
677
|ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)|
678
---------------------------------------------------------------------
679
|0 |PX COORDINATOR | |1 |19 |
680
|1 |└─EXCHANGE OUT DISTR |:EX10001|1 |19 |
681
|2 | └─HASH JOIN | |1 |18 |
682
|3 | ├─EXCHANGE IN DISTR | |1 |7 |
683
|4 | │ └─EXCHANGE OUT DISTR (PKEY)|:EX10000|1 |7 |
684
|5 | │ └─PX PARTITION ITERATOR | |1 |7 |
685
|6 | │ └─TABLE RANGE SCAN |t12 |1 |7 |
686
|7 | └─PX PARTITION ITERATOR | |1 |11 |
687
|8 | └─TABLE RANGE SCAN |t11 |1 |11 |
688
=====================================================================
690
-------------------------------------
691
0 - output([INTERNAL_FUNCTION(t11.c1, t11.c2)]), filter(nil), rowset=16
692
1 - output([INTERNAL_FUNCTION(t11.c1, t11.c2)]), filter(nil), rowset=16
694
2 - output([t11.c1], [t11.c2]), filter(nil), rowset=16
695
equal_conds([t11.c1 = t12.c1]), other_conds(nil)
696
3 - output([t12.c1]), filter(nil), rowset=16
697
4 - output([t12.c1]), filter(nil), rowset=16
698
(#keys=1, [t12.c1]), dop=1
699
5 - output([t12.c1]), filter(nil), rowset=16
700
force partition granule
701
6 - output([t12.c1]), filter(nil), rowset=16
702
access([t12.c1]), partitions(p[0-2])
703
is_index_back=false, is_global_index=false,
704
range_key([t12.c1]), range(5 ; MAX),
705
range_cond([t12.c1 > 5])
706
7 - output([t11.c1], [t11.c2]), filter(nil), rowset=16
707
affinitize, force partition granule
708
8 - output([t11.c1], [t11.c2]), filter(nil), rowset=16
709
access([t11.c1], [t11.c2]), partitions(p[0-4])
710
is_index_back=false, is_global_index=false,
711
range_key([t11.c1]), range(5 ; MAX),
712
range_cond([t11.c1 > 5])
713
create table t4 (c1 int, c2 int, c3 date, c4 int, primary key(c1,c2,c3)) partition by hash(c1) subpartition by range columns(c3)
714
subpartition template( subpartition p_2016_11_02_00 values less than ('2016-11-02'),715
subpartition p_2016_11_03_00 values less than ('2016-11-03'),716
subpartition p_max_value values less than (MAXVALUE));
717
create table t5(c1 int, c2 int, c3 date, c4 int, primary key(c1,c2,c3)) partition by hash(c1) subpartition by range columns(c3)
718
subpartition template( subpartition p_2016_11_02_00 values less than ('2016-11-02'),719
subpartition p_2016_11_03_00 values less than ('2016-11-03'),720
subpartition p_max_value values less than (MAXVALUE));
721
create table t6(c1 int, c2 int, c3 date, c4 int, primary key(c1,c2,c3)) partition by hash(c1) subpartition by range columns(c3)
722
subpartition template( subpartition p_2016_11_02_00 values less than ('2016-11-02'),723
subpartition p_2016_11_03_00 values less than ('2016-11-03'),724
subpartition p_max_value values less than (MAXVALUE));
725
explain select /*+use_nl(t4 t5)*/ * from t4 left join t5 on t4.c2= t5.c2;
727
=================================================================
728
|ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)|
729
-----------------------------------------------------------------
730
|0 |NESTED-LOOP OUTER JOIN | |1 |9 |
731
|1 |├─PX COORDINATOR | |1 |9 |
732
|2 |│ └─EXCHANGE OUT DISTR |:EX10000|1 |8 |
733
|3 |│ └─PX PARTITION ITERATOR | |1 |7 |
734
|4 |│ └─TABLE FULL SCAN |t4 |1 |7 |
735
|5 |└─MATERIAL | |1 |9 |
736
|6 | └─PX COORDINATOR | |1 |9 |
737
|7 | └─EXCHANGE OUT DISTR |:EX20000|1 |8 |
738
|8 | └─PX PARTITION ITERATOR| |1 |7 |
739
|9 | └─TABLE FULL SCAN |t5 |1 |7 |
740
=================================================================
742
-------------------------------------
743
0 - output([t4.c1], [t4.c2], [t4.c3], [t4.c4], [t5.c1], [t5.c2], [t5.c3], [t5.c4]), filter(nil), rowset=16
744
conds([t4.c2 = t5.c2]), nl_params_(nil), use_batch=false
745
1 - output([t4.c1], [t4.c2], [t4.c3], [t4.c4]), filter(nil), rowset=16
746
2 - output([t4.c1], [t4.c2], [t4.c3], [t4.c4]), filter(nil), rowset=16
748
3 - output([t4.c1], [t4.c2], [t4.c3], [t4.c4]), filter(nil), rowset=16
749
force partition granule
750
4 - output([t4.c1], [t4.c2], [t4.c3], [t4.c4]), filter(nil), rowset=16
751
access([t4.c1], [t4.c2], [t4.c3], [t4.c4]), partitions(p0sp[0-2])
752
is_index_back=false, is_global_index=false,
753
range_key([t4.c1], [t4.c2], [t4.c3]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true
754
5 - output([t5.c1], [t5.c2], [t5.c3], [t5.c4]), filter(nil), rowset=16
755
6 - output([t5.c1], [t5.c2], [t5.c3], [t5.c4]), filter(nil), rowset=16
756
7 - output([t5.c1], [t5.c2], [t5.c3], [t5.c4]), filter(nil), rowset=16
758
8 - output([t5.c1], [t5.c2], [t5.c3], [t5.c4]), filter(nil), rowset=16
759
force partition granule
760
9 - output([t5.c1], [t5.c2], [t5.c3], [t5.c4]), filter(nil), rowset=16
761
access([t5.c1], [t5.c2], [t5.c3], [t5.c4]), partitions(p0sp[0-2])
762
is_index_back=false, is_global_index=false,
763
range_key([t5.c1], [t5.c2], [t5.c3]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true
764
explain select /*+use_nl(t4 t5)*/ * from t4 left join t5 on t4.c1= t5.c1;
766
======================================================================
767
|ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)|
768
----------------------------------------------------------------------
769
|0 |PX COORDINATOR | |1 |58 |
770
|1 |└─EXCHANGE OUT DISTR |:EX10000|1 |56 |
771
|2 | └─NESTED-LOOP OUTER JOIN | |1 |54 |
772
|3 | ├─PX PARTITION ITERATOR | |1 |7 |
773
|4 | │ └─TABLE FULL SCAN |t4 |1 |7 |
774
|5 | └─DISTRIBUTED TABLE RANGE SCAN|t5 |1 |46 |
775
======================================================================
777
-------------------------------------
778
0 - output([INTERNAL_FUNCTION(t4.c1, t4.c2, t4.c3, t4.c4, t5.c1, t5.c2, t5.c3, t5.c4)]), filter(nil), rowset=16
779
1 - output([INTERNAL_FUNCTION(t4.c1, t4.c2, t4.c3, t4.c4, t5.c1, t5.c2, t5.c3, t5.c4)]), filter(nil), rowset=16
781
2 - output([t4.c1], [t4.c2], [t4.c3], [t4.c4], [t5.c1], [t5.c2], [t5.c3], [t5.c4]), filter(nil), rowset=16
782
conds(nil), nl_params_([t4.c1(:0)]), use_batch=true
783
3 - output([t4.c1], [t4.c2], [t4.c3], [t4.c4]), filter(nil), rowset=16
784
force partition granule
785
4 - output([t4.c1], [t4.c2], [t4.c3], [t4.c4]), filter(nil), rowset=16
786
access([t4.c1], [t4.c2], [t4.c3], [t4.c4]), partitions(p0sp[0-2])
787
is_index_back=false, is_global_index=false,
788
range_key([t4.c1], [t4.c2], [t4.c3]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true
789
5 - output([t5.c1], [t5.c2], [t5.c3], [t5.c4]), filter(nil), rowset=16
790
access([GROUP_ID], [t5.c1], [t5.c2], [t5.c3], [t5.c4]), partitions(p0sp[0-2])
791
is_index_back=false, is_global_index=false,
792
range_key([t5.c1], [t5.c2], [t5.c3]), range(MIN ; MAX),
793
range_cond([:0 = t5.c1])
794
explain select /*+use_nl(t4 t5)*/ * from t4 join t5 on t4.c2= t5.c2 and t4.c3= t5.c3;
796
=============================================================
797
|ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)|
798
-------------------------------------------------------------
799
|0 |PX COORDINATOR | |1 |11 |
800
|1 |└─EXCHANGE OUT DISTR |:EX10000|1 |10 |
801
|2 | └─PX PARTITION ITERATOR| |1 |7 |
802
|3 | └─NESTED-LOOP JOIN | |1 |7 |
803
|4 | ├─TABLE FULL SCAN |t4 |1 |7 |
804
|5 | └─MATERIAL | |1 |7 |
805
|6 | └─TABLE FULL SCAN|t5 |1 |7 |
806
=============================================================
808
-------------------------------------
809
0 - output([INTERNAL_FUNCTION(t4.c1, t4.c2, t4.c3, t4.c4, t5.c1, t5.c2, t5.c3, t5.c4)]), filter(nil), rowset=16
810
1 - output([INTERNAL_FUNCTION(t4.c1, t4.c2, t4.c3, t4.c4, t5.c1, t5.c2, t5.c3, t5.c4)]), filter(nil), rowset=16
812
2 - output([t4.c2], [t5.c2], [t4.c3], [t5.c3], [t4.c1], [t4.c4], [t5.c1], [t5.c4]), filter(nil), rowset=16
813
partition wise, force partition granule
814
3 - output([t4.c2], [t5.c2], [t4.c3], [t5.c3], [t4.c1], [t4.c4], [t5.c1], [t5.c4]), filter(nil), rowset=16
815
conds([t4.c2 = t5.c2], [t4.c3 = t5.c3]), nl_params_(nil), use_batch=false
816
4 - output([t4.c1], [t4.c2], [t4.c3], [t4.c4]), filter(nil), rowset=16
817
access([t4.c1], [t4.c2], [t4.c3], [t4.c4]), partitions(p0sp[0-2])
818
is_index_back=false, is_global_index=false,
819
range_key([t4.c1], [t4.c2], [t4.c3]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true
820
5 - output([t5.c2], [t5.c3], [t5.c1], [t5.c4]), filter(nil), rowset=16
821
6 - output([t5.c1], [t5.c2], [t5.c3], [t5.c4]), filter(nil), rowset=16
822
access([t5.c1], [t5.c2], [t5.c3], [t5.c4]), partitions(p0sp[0-2])
823
is_index_back=false, is_global_index=false,
824
range_key([t5.c1], [t5.c2], [t5.c3]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true
825
explain select * from t4 left join t5 on t4.c1= t5.c1 and t4.c3 = t5.c3;
827
=============================================================
828
|ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)|
829
-------------------------------------------------------------
830
|0 |PX COORDINATOR | |1 |18 |
831
|1 |└─EXCHANGE OUT DISTR |:EX10000|1 |16 |
832
|2 | └─PX PARTITION ITERATOR| |1 |14 |
833
|3 | └─HASH OUTER JOIN | |1 |14 |
834
|4 | ├─TABLE FULL SCAN |t4 |1 |7 |
835
|5 | └─TABLE FULL SCAN |t5 |1 |7 |
836
=============================================================
838
-------------------------------------
839
0 - output([INTERNAL_FUNCTION(t4.c1, t4.c2, t4.c3, t4.c4, t5.c1, t5.c2, t5.c3, t5.c4)]), filter(nil), rowset=16
840
1 - output([INTERNAL_FUNCTION(t4.c1, t4.c2, t4.c3, t4.c4, t5.c1, t5.c2, t5.c3, t5.c4)]), filter(nil), rowset=16
842
2 - output([t4.c1], [t5.c1], [t4.c3], [t5.c3], [t4.c2], [t4.c4], [t5.c2], [t5.c4]), filter(nil), rowset=16
843
partition wise, force partition granule
844
3 - output([t4.c1], [t5.c1], [t4.c3], [t5.c3], [t4.c2], [t4.c4], [t5.c2], [t5.c4]), filter(nil), rowset=16
845
equal_conds([t4.c1 = t5.c1], [t4.c3 = t5.c3]), other_conds(nil)
846
4 - output([t4.c1], [t4.c2], [t4.c3], [t4.c4]), filter(nil), rowset=16
847
access([t4.c1], [t4.c2], [t4.c3], [t4.c4]), partitions(p0sp[0-2])
848
is_index_back=false, is_global_index=false,
849
range_key([t4.c1], [t4.c2], [t4.c3]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true
850
5 - output([t5.c1], [t5.c2], [t5.c3], [t5.c4]), filter(nil), rowset=16
851
access([t5.c1], [t5.c2], [t5.c3], [t5.c4]), partitions(p0sp[0-2])
852
is_index_back=false, is_global_index=false,
853
range_key([t5.c1], [t5.c2], [t5.c3]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true
854
explain select /*+use_nl(t4 t5)*/ * from t4 left join t5 on t4.c1= t5.c2 and t4.c3 = t5.c3;
856
=================================================================
857
|ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)|
858
-----------------------------------------------------------------
859
|0 |PX COORDINATOR | |1 |11 |
860
|1 |└─EXCHANGE OUT DISTR |:EX10000|1 |10 |
861
|2 | └─PX PARTITION ITERATOR | |1 |7 |
862
|3 | └─NESTED-LOOP OUTER JOIN | |1 |7 |
863
|4 | ├─TABLE FULL SCAN |t4 |1 |7 |
864
|5 | └─MATERIAL | |1 |7 |
865
|6 | └─TABLE FULL SCAN |t5 |1 |7 |
866
=================================================================
868
-------------------------------------
869
0 - output([INTERNAL_FUNCTION(t4.c1, t4.c2, t4.c3, t4.c4, t5.c1, t5.c2, t5.c3, t5.c4)]), filter(nil), rowset=16
870
1 - output([INTERNAL_FUNCTION(t4.c1, t4.c2, t4.c3, t4.c4, t5.c1, t5.c2, t5.c3, t5.c4)]), filter(nil), rowset=16
872
2 - output([t4.c1], [t5.c2], [t4.c3], [t5.c3], [t4.c2], [t4.c4], [t5.c1], [t5.c4]), filter(nil), rowset=16
873
partition wise, force partition granule
874
3 - output([t4.c1], [t5.c2], [t4.c3], [t5.c3], [t4.c2], [t4.c4], [t5.c1], [t5.c4]), filter(nil), rowset=16
875
conds([t4.c1 = t5.c2], [t4.c3 = t5.c3]), nl_params_(nil), use_batch=false
876
4 - output([t4.c1], [t4.c2], [t4.c3], [t4.c4]), filter(nil), rowset=16
877
access([t4.c1], [t4.c2], [t4.c3], [t4.c4]), partitions(p0sp[0-2])
878
is_index_back=false, is_global_index=false,
879
range_key([t4.c1], [t4.c2], [t4.c3]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true
880
5 - output([t5.c2], [t5.c3], [t5.c1], [t5.c4]), filter(nil), rowset=16
881
6 - output([t5.c1], [t5.c2], [t5.c3], [t5.c4]), filter(nil), rowset=16
882
access([t5.c1], [t5.c2], [t5.c3], [t5.c4]), partitions(p0sp[0-2])
883
is_index_back=false, is_global_index=false,
884
range_key([t5.c1], [t5.c2], [t5.c3]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true
885
explain select /*+use_nl(t4 t1)*/ * from t4 left join t1 on t4.c1= t1.c1 and t4.c3 = t1.c4;
887
=======================================================================
888
|ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)|
889
-----------------------------------------------------------------------
890
|0 |PX COORDINATOR | |1 |11 |
891
|1 |└─EXCHANGE OUT DISTR |:EX10001|1 |10 |
892
|2 | └─NESTED-LOOP OUTER JOIN | |1 |7 |
893
|3 | ├─PX PARTITION ITERATOR | |1 |7 |
894
|4 | │ └─TABLE FULL SCAN |t4 |1 |7 |
895
|5 | └─MATERIAL | |1 |7 |
896
|6 | └─EXCHANGE IN DISTR | |1 |7 |
897
|7 | └─EXCHANGE OUT DISTR (PKEY)|:EX10000|1 |6 |
898
|8 | └─PX PARTITION ITERATOR | |1 |5 |
899
|9 | └─TABLE FULL SCAN |t1 |1 |5 |
900
=======================================================================
902
-------------------------------------
903
0 - output([INTERNAL_FUNCTION(t4.c1, t4.c2, t4.c3, t4.c4, t1.c1, t1.c2, t1.c3, t1.c4)]), filter(nil), rowset=16
904
1 - output([INTERNAL_FUNCTION(t4.c1, t4.c2, t4.c3, t4.c4, t1.c1, t1.c2, t1.c3, t1.c4)]), filter(nil), rowset=16
906
2 - output([t4.c1], [t1.c1], [t4.c3], [t1.c4], [t4.c2], [t4.c4], [t1.c2], [t1.c3]), filter(nil), rowset=16
907
conds([t4.c1 = t1.c1], [t4.c3 = t1.c4]), nl_params_(nil), use_batch=false
908
3 - output([t4.c1], [t4.c2], [t4.c3], [t4.c4]), filter(nil), rowset=16
909
affinitize, force partition granule
910
4 - output([t4.c1], [t4.c2], [t4.c3], [t4.c4]), filter(nil), rowset=16
911
access([t4.c1], [t4.c2], [t4.c3], [t4.c4]), partitions(p0sp[0-2])
912
is_index_back=false, is_global_index=false,
913
range_key([t4.c1], [t4.c2], [t4.c3]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true
914
5 - output([t1.c1], [t1.c4], [t1.c2], [t1.c3]), filter(nil), rowset=16
915
6 - output([t1.c1], [t1.c4], [t1.c2], [t1.c3]), filter(nil), rowset=16
916
7 - output([t1.c1], [t1.c4], [t1.c2], [t1.c3]), filter(nil), rowset=16
917
(#keys=1, [t1.c4]), dop=1
918
8 - output([t1.c2], [t1.c3], [t1.c1], [t1.c4]), filter(nil), rowset=16
919
force partition granule, asc
920
9 - output([t1.c2], [t1.c3], [t1.c1], [t1.c4]), filter(nil), rowset=16
921
access([t1.c2], [t1.c3], [t1.c1], [t1.c4]), partitions(p[0-1])
922
is_index_back=false, is_global_index=false,
923
range_key([t1.c2], [t1.c3]), range(MIN,MIN ; MAX,MAX)always true
924
explain select /*+use_nl(t5 t4)*/ * from t5 left join t4 on t4.c1= t5.c2 and t4.c3 = t5.c3;
926
=================================================================
927
|ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)|
928
-----------------------------------------------------------------
929
|0 |PX COORDINATOR | |1 |27 |
930
|1 |└─EXCHANGE OUT DISTR |:EX10000|1 |26 |
931
|2 | └─PX PARTITION ITERATOR | |1 |23 |
932
|3 | └─NESTED-LOOP OUTER JOIN | |1 |23 |
933
|4 | ├─TABLE FULL SCAN |t5 |1 |7 |
934
|5 | └─TABLE RANGE SCAN |t4 |1 |46 |
935
=================================================================
937
-------------------------------------
938
0 - output([INTERNAL_FUNCTION(t5.c1, t5.c2, t5.c3, t5.c4, t4.c1, t4.c2, t4.c3, t4.c4)]), filter(nil), rowset=16
939
1 - output([INTERNAL_FUNCTION(t5.c1, t5.c2, t5.c3, t5.c4, t4.c1, t4.c2, t4.c3, t4.c4)]), filter(nil), rowset=16
941
2 - output([t5.c2], [t5.c3], [t5.c1], [t5.c4], [t4.c1], [t4.c2], [t4.c3], [t4.c4]), filter(nil), rowset=16
942
partition wise, force partition granule
943
3 - output([t5.c2], [t5.c3], [t5.c1], [t5.c4], [t4.c1], [t4.c2], [t4.c3], [t4.c4]), filter(nil), rowset=16
944
conds(nil), nl_params_([t5.c2(:0)], [t5.c3(:1)]), use_batch=true
945
4 - output([t5.c1], [t5.c2], [t5.c3], [t5.c4]), filter(nil), rowset=16
946
access([t5.c1], [t5.c2], [t5.c3], [t5.c4]), partitions(p0sp[0-2])
947
is_index_back=false, is_global_index=false,
948
range_key([t5.c1], [t5.c2], [t5.c3]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true
949
5 - output([t4.c1], [t4.c2], [t4.c3], [t4.c4]), filter([t4.c3 = :1]), rowset=16
950
access([GROUP_ID], [t4.c1], [t4.c2], [t4.c3], [t4.c4]), partitions(p0sp[0-2])
951
is_index_back=false, is_global_index=false, filter_before_indexback[false],
952
range_key([t4.c1], [t4.c2], [t4.c3]), range(MIN ; MAX),
953
range_cond([t4.c1 = :0])
954
explain select /*+use_nl(t1 t4)*/ * from t1 left join t4 on t4.c1= t1.c1 and t4.c3 = t1.c4;
956
=====================================================================
957
|ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)|
958
---------------------------------------------------------------------
959
|0 |PX COORDINATOR | |1 |29 |
960
|1 |└─EXCHANGE OUT DISTR |:EX10001|1 |28 |
961
|2 | └─NESTED-LOOP OUTER JOIN | |1 |25 |
962
|3 | ├─EXCHANGE IN DISTR | |1 |7 |
963
|4 | │ └─EXCHANGE OUT DISTR (PKEY)|:EX10000|1 |6 |
964
|5 | │ └─PX PARTITION ITERATOR | |1 |5 |
965
|6 | │ └─TABLE FULL SCAN |t1 |1 |5 |
966
|7 | └─PX PARTITION ITERATOR | |1 |54 |
967
|8 | └─TABLE RANGE SCAN |t4 |1 |54 |
968
=====================================================================
970
-------------------------------------
971
0 - output([INTERNAL_FUNCTION(t1.c1, t1.c2, t1.c3, t1.c4, t4.c1, t4.c2, t4.c3, t4.c4)]), filter(nil), rowset=16
972
1 - output([INTERNAL_FUNCTION(t1.c1, t1.c2, t1.c3, t1.c4, t4.c1, t4.c2, t4.c3, t4.c4)]), filter(nil), rowset=16
974
2 - output([t1.c1], [t1.c4], [t1.c2], [t1.c3], [t4.c1], [t4.c2], [t4.c3], [t4.c4]), filter(nil), rowset=16
975
conds(nil), nl_params_([t1.c1(:0)], [t1.c4(:1)]), use_batch=false
976
3 - output([PARTITION_ID], [t1.c1], [t1.c4], [t1.c2], [t1.c3]), filter(nil), rowset=16
977
4 - output([PARTITION_ID], [t1.c1], [t1.c4], [t1.c2], [t1.c3]), filter(nil), rowset=16
978
(#keys=1, [t1.c4]), dop=1
979
5 - output([t1.c2], [t1.c3], [t1.c1], [t1.c4]), filter(nil), rowset=16
980
force partition granule, asc
981
6 - output([t1.c2], [t1.c3], [t1.c1], [t1.c4]), filter(nil), rowset=16
982
access([t1.c2], [t1.c3], [t1.c1], [t1.c4]), partitions(p[0-1])
983
is_index_back=false, is_global_index=false,
984
range_key([t1.c2], [t1.c3]), range(MIN,MIN ; MAX,MAX)always true
985
7 - output([t4.c1], [t4.c2], [t4.c3], [t4.c4]), filter(nil), rowset=16
986
affinitize, force partition granule
987
8 - output([t4.c1], [t4.c2], [t4.c3], [t4.c4]), filter([t4.c3 = :1]), rowset=16
988
access([t4.c1], [t4.c2], [t4.c3], [t4.c4]), partitions(p0sp[0-2])
989
is_index_back=false, is_global_index=false, filter_before_indexback[false],
990
range_key([t4.c1], [t4.c2], [t4.c3]), range(MIN ; MAX),
991
range_cond([t4.c1 = :0])
992
explain select /*+use_nl(t4 t5)*/ * from t4 left join t5 on t4.c1= t5.c1 and t4.c3 = t5.c3;
994
=================================================================
995
|ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)|
996
-----------------------------------------------------------------
997
|0 |PX COORDINATOR | |1 |27 |
998
|1 |└─EXCHANGE OUT DISTR |:EX10000|1 |26 |
999
|2 | └─PX PARTITION ITERATOR | |1 |23 |
1000
|3 | └─NESTED-LOOP OUTER JOIN | |1 |23 |
1001
|4 | ├─TABLE FULL SCAN |t4 |1 |7 |
1002
|5 | └─TABLE RANGE SCAN |t5 |1 |46 |
1003
=================================================================
1005
-------------------------------------
1006
0 - output([INTERNAL_FUNCTION(t4.c1, t4.c2, t4.c3, t4.c4, t5.c1, t5.c2, t5.c3, t5.c4)]), filter(nil), rowset=16
1007
1 - output([INTERNAL_FUNCTION(t4.c1, t4.c2, t4.c3, t4.c4, t5.c1, t5.c2, t5.c3, t5.c4)]), filter(nil), rowset=16
1009
2 - output([t4.c1], [t4.c3], [t4.c2], [t4.c4], [t5.c1], [t5.c2], [t5.c3], [t5.c4]), filter(nil), rowset=16
1010
partition wise, force partition granule
1011
3 - output([t4.c1], [t4.c3], [t4.c2], [t4.c4], [t5.c1], [t5.c2], [t5.c3], [t5.c4]), filter(nil), rowset=16
1012
conds(nil), nl_params_([t4.c1(:0)], [t4.c3(:1)]), use_batch=true
1013
4 - output([t4.c1], [t4.c2], [t4.c3], [t4.c4]), filter(nil), rowset=16
1014
access([t4.c1], [t4.c2], [t4.c3], [t4.c4]), partitions(p0sp[0-2])
1015
is_index_back=false, is_global_index=false,
1016
range_key([t4.c1], [t4.c2], [t4.c3]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true
1017
5 - output([t5.c1], [t5.c2], [t5.c3], [t5.c4]), filter([:1 = t5.c3]), rowset=16
1018
access([GROUP_ID], [t5.c1], [t5.c2], [t5.c3], [t5.c4]), partitions(p0sp[0-2])
1019
is_index_back=false, is_global_index=false, filter_before_indexback[false],
1020
range_key([t5.c1], [t5.c2], [t5.c3]), range(MIN ; MAX),
1021
range_cond([:0 = t5.c1])
1022
explain select * from (select c1, c3, sum(c2) as yyy, max(c4) from t4 group by c3 order by yyy limit 1) as a join t5 as b on a.c1 = b.c1 and a.c3 = b.c3 join t6 as c on b.c1=c.c1 and b.c3 = c.c3;
1024
================================================================================
1025
|ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)|
1026
--------------------------------------------------------------------------------
1027
|0 |PX COORDINATOR | |1 |32 |
1028
|1 |└─EXCHANGE OUT DISTR |:EX10002|1 |30 |
1029
|2 | └─HASH JOIN | |1 |26 |
1030
|3 | ├─PX PARTITION ITERATOR | |1 |7 |
1031
|4 | │ └─TABLE FULL SCAN |c |1 |7 |
1032
|5 | └─HASH JOIN | |1 |19 |
1033
|6 | ├─PX PARTITION ITERATOR | |1 |7 |
1034
|7 | │ └─TABLE FULL SCAN |b |1 |7 |
1035
|8 | └─EXCHANGE IN DISTR | |1 |12 |
1036
|9 | └─EXCHANGE OUT DISTR (PKEY) |:EX10001|1 |11 |
1037
|10| └─SUBPLAN SCAN |a |1 |10 |
1038
|11| └─LIMIT | |1 |10 |
1039
|12| └─EXCHANGE IN MERGE SORT DISTR| |1 |10 |
1040
|13| └─EXCHANGE OUT DISTR |:EX10000|1 |9 |
1041
|14| └─TOP-N SORT | |1 |7 |
1042
|15| └─PX PARTITION ITERATOR | |1 |7 |
1043
|16| └─HASH GROUP BY | |1 |7 |
1044
|17| └─TABLE FULL SCAN |t4 |1 |7 |
1045
================================================================================
1047
-------------------------------------
1048
0 - output([INTERNAL_FUNCTION(a.c1, a.c3, a.yyy, a.max(c4), b.c1, b.c2, b.c3, b.c4, c.c1, c.c2, c.c3, c.c4)]), filter(nil), rowset=16
1049
1 - output([INTERNAL_FUNCTION(a.c1, a.c3, a.yyy, a.max(c4), b.c1, b.c2, b.c3, b.c4, c.c1, c.c2, c.c3, c.c4)]), filter(nil), rowset=16
1051
2 - output([c.c1], [a.c1], [b.c3], [c.c3], [c.c2], [c.c4], [b.c1], [a.c3], [b.c2], [b.c4], [a.yyy], [a.max(c4)]), filter(nil), rowset=16
1052
equal_conds([c.c1 = a.c1], [b.c3 = c.c3]), other_conds(nil)
1053
3 - output([c.c1], [c.c2], [c.c3], [c.c4]), filter(nil), rowset=16
1054
affinitize, partition wise, force partition granule
1055
4 - output([c.c1], [c.c2], [c.c3], [c.c4]), filter(nil), rowset=16
1056
access([c.c1], [c.c2], [c.c3], [c.c4]), partitions(p0sp[0-2])
1057
is_index_back=false, is_global_index=false,
1058
range_key([c.c1], [c.c2], [c.c3]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true
1059
5 - output([a.c1], [b.c3], [b.c1], [a.c3], [b.c2], [b.c4], [a.yyy], [a.max(c4)]), filter(nil), rowset=16
1060
equal_conds([a.c1 = b.c1], [a.c3 = b.c3]), other_conds(nil)
1061
6 - output([b.c1], [b.c2], [b.c3], [b.c4]), filter(nil), rowset=16
1062
affinitize, partition wise, force partition granule
1063
7 - output([b.c1], [b.c2], [b.c3], [b.c4]), filter(nil), rowset=16
1064
access([b.c1], [b.c2], [b.c3], [b.c4]), partitions(p0sp[0-2])
1065
is_index_back=false, is_global_index=false,
1066
range_key([b.c1], [b.c2], [b.c3]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true
1067
8 - output([a.c1], [a.c3], [a.yyy], [a.max(c4)]), filter(nil), rowset=16
1068
9 - output([a.c1], [a.c3], [a.yyy], [a.max(c4)]), filter(nil), rowset=16
1069
(#keys=1, [a.c3]), is_single, dop=1
1070
10 - output([a.c1], [a.c3], [a.yyy], [a.max(c4)]), filter(nil), rowset=16
1071
access([a.c1], [a.c3], [a.yyy], [a.max(c4)])
1072
11 - output([t4.c1], [t4.c3], [T_FUN_SUM(t4.c2)], [T_FUN_MAX(t4.c4)]), filter(nil), rowset=16
1073
limit(1), offset(nil)
1074
12 - output([t4.c1], [t4.c3], [T_FUN_SUM(t4.c2)], [T_FUN_MAX(t4.c4)]), filter(nil), rowset=16
1075
sort_keys([T_FUN_SUM(t4.c2), ASC])
1076
13 - output([t4.c1], [t4.c3], [T_FUN_SUM(t4.c2)], [T_FUN_MAX(t4.c4)]), filter(nil), rowset=16
1078
14 - output([t4.c1], [t4.c3], [T_FUN_SUM(t4.c2)], [T_FUN_MAX(t4.c4)]), filter(nil), rowset=16
1079
sort_keys([T_FUN_SUM(t4.c2), ASC]), topn(1)
1080
15 - output([t4.c1], [t4.c3], [T_FUN_SUM(t4.c2)], [T_FUN_MAX(t4.c4)]), filter(nil), rowset=16
1081
partition wise, force partition granule
1082
16 - output([t4.c1], [t4.c3], [T_FUN_SUM(t4.c2)], [T_FUN_MAX(t4.c4)]), filter(nil), rowset=16
1083
group([t4.c3]), agg_func([T_FUN_SUM(t4.c2)], [T_FUN_MAX(t4.c4)])
1084
17 - output([t4.c1], [t4.c2], [t4.c3], [t4.c4]), filter(nil), rowset=16
1085
access([t4.c1], [t4.c2], [t4.c3], [t4.c4]), partitions(p0sp[0-2])
1086
is_index_back=false, is_global_index=false,
1087
range_key([t4.c1], [t4.c2], [t4.c3]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true
1088
explain select * from t5 as a join (select c1, c3, sum(c2) as yyy, max(c4) from t4 group by c3 order by yyy limit 1) as b on a.c1 = b.c1 and a.c3 = b.c3 join t6 as c on b.c1=c.c1 and b.c3 = c.c3;
1090
=====================================================================
1091
|ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)|
1092
---------------------------------------------------------------------
1093
|0 |HASH JOIN | |1 |27 |
1094
|1 |├─SUBPLAN SCAN |b |1 |10 |
1095
|2 |│ └─LIMIT | |1 |10 |
1096
|3 |│ └─PX COORDINATOR MERGE SORT | |1 |10 |
1097
|4 |│ └─EXCHANGE OUT DISTR |:EX10000|1 |9 |
1098
|5 |│ └─TOP-N SORT | |1 |7 |
1099
|6 |│ └─PX PARTITION ITERATOR| |1 |7 |
1100
|7 |│ └─HASH GROUP BY | |1 |7 |
1101
|8 |│ └─TABLE FULL SCAN |t4 |1 |7 |
1102
|9 |└─HASH JOIN | |1 |18 |
1103
|10| ├─PX COORDINATOR | |1 |9 |
1104
|11| │ └─EXCHANGE OUT DISTR |:EX20000|1 |8 |
1105
|12| │ └─PX PARTITION ITERATOR | |1 |7 |
1106
|13| │ └─TABLE FULL SCAN |a |1 |7 |
1107
|14| └─PX COORDINATOR | |1 |9 |
1108
|15| └─EXCHANGE OUT DISTR |:EX30000|1 |8 |
1109
|16| └─PX PARTITION ITERATOR | |1 |7 |
1110
|17| └─TABLE FULL SCAN |c |1 |7 |
1111
=====================================================================
1113
-------------------------------------
1114
0 - output([a.c1], [a.c2], [a.c3], [a.c4], [b.c1], [b.c3], [b.yyy], [b.max(c4)], [c.c1], [c.c2], [c.c3], [c.c4]), filter(nil), rowset=16
1115
equal_conds([b.c1 = c.c1], [b.c3 = c.c3], [a.c3 = b.c3]), other_conds(nil)
1116
1 - output([b.c1], [b.c3], [b.yyy], [b.max(c4)]), filter(nil), rowset=16
1117
access([b.c1], [b.c3], [b.yyy], [b.max(c4)])
1118
2 - output([t4.c1], [t4.c3], [T_FUN_SUM(t4.c2)], [T_FUN_MAX(t4.c4)]), filter(nil), rowset=16
1119
limit(1), offset(nil)
1120
3 - output([t4.c1], [t4.c3], [T_FUN_SUM(t4.c2)], [T_FUN_MAX(t4.c4)]), filter(nil), rowset=16
1121
sort_keys([T_FUN_SUM(t4.c2), ASC])
1122
4 - output([t4.c1], [t4.c3], [T_FUN_SUM(t4.c2)], [T_FUN_MAX(t4.c4)]), filter(nil), rowset=16
1124
5 - output([t4.c1], [t4.c3], [T_FUN_SUM(t4.c2)], [T_FUN_MAX(t4.c4)]), filter(nil), rowset=16
1125
sort_keys([T_FUN_SUM(t4.c2), ASC]), topn(1)
1126
6 - output([t4.c1], [t4.c3], [T_FUN_SUM(t4.c2)], [T_FUN_MAX(t4.c4)]), filter(nil), rowset=16
1127
partition wise, force partition granule
1128
7 - output([t4.c1], [t4.c3], [T_FUN_SUM(t4.c2)], [T_FUN_MAX(t4.c4)]), filter(nil), rowset=16
1129
group([t4.c3]), agg_func([T_FUN_SUM(t4.c2)], [T_FUN_MAX(t4.c4)])
1130
8 - output([t4.c1], [t4.c2], [t4.c3], [t4.c4]), filter(nil), rowset=16
1131
access([t4.c1], [t4.c2], [t4.c3], [t4.c4]), partitions(p0sp[0-2])
1132
is_index_back=false, is_global_index=false,
1133
range_key([t4.c1], [t4.c2], [t4.c3]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true
1134
9 - output([a.c1], [a.c2], [a.c3], [a.c4], [c.c1], [c.c2], [c.c3], [c.c4]), filter(nil), rowset=16
1135
equal_conds([c.c1 = a.c1]), other_conds(nil)
1136
10 - output([a.c1], [a.c2], [a.c3], [a.c4]), filter(nil), rowset=16
1137
11 - output([a.c1], [a.c2], [a.c3], [a.c4]), filter(nil), rowset=16
1139
12 - output([a.c1], [a.c2], [a.c3], [a.c4]), filter(nil), rowset=16
1140
force partition granule
1141
13 - output([a.c1], [a.c2], [a.c3], [a.c4]), filter(nil), rowset=16
1142
access([a.c1], [a.c2], [a.c3], [a.c4]), partitions(p0sp[0-2])
1143
is_index_back=false, is_global_index=false,
1144
range_key([a.c1], [a.c2], [a.c3]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true
1145
14 - output([c.c1], [c.c2], [c.c3], [c.c4]), filter(nil), rowset=16
1146
15 - output([c.c1], [c.c2], [c.c3], [c.c4]), filter(nil), rowset=16
1148
16 - output([c.c1], [c.c2], [c.c3], [c.c4]), filter(nil), rowset=16
1149
force partition granule
1150
17 - output([c.c1], [c.c2], [c.c3], [c.c4]), filter(nil), rowset=16
1151
access([c.c1], [c.c2], [c.c3], [c.c4]), partitions(p0sp[0-2])
1152
is_index_back=false, is_global_index=false,
1153
range_key([c.c1], [c.c2], [c.c3]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true
1154
explain select * from t5 as a join t6 as b on a.c1 = b.c1 and a.c3 = b.c3 join (select c1, c3, sum(c2) as yyy, max(c4) from t4 group by c3 order by yyy limit 1) as c on b.c1=c.c1 and b.c3 = c.c3;
1156
================================================================================
1157
|ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)|
1158
--------------------------------------------------------------------------------
1159
|0 |PX COORDINATOR | |1 |32 |
1160
|1 |└─EXCHANGE OUT DISTR |:EX10002|1 |30 |
1161
|2 | └─HASH JOIN | |1 |26 |
1162
|3 | ├─PX PARTITION ITERATOR | |1 |7 |
1163
|4 | │ └─TABLE FULL SCAN |a |1 |7 |
1164
|5 | └─HASH JOIN | |1 |19 |
1165
|6 | ├─PX PARTITION ITERATOR | |1 |7 |
1166
|7 | │ └─TABLE FULL SCAN |b |1 |7 |
1167
|8 | └─EXCHANGE IN DISTR | |1 |12 |
1168
|9 | └─EXCHANGE OUT DISTR (PKEY) |:EX10001|1 |11 |
1169
|10| └─SUBPLAN SCAN |c |1 |10 |
1170
|11| └─LIMIT | |1 |10 |
1171
|12| └─EXCHANGE IN MERGE SORT DISTR| |1 |10 |
1172
|13| └─EXCHANGE OUT DISTR |:EX10000|1 |9 |
1173
|14| └─TOP-N SORT | |1 |7 |
1174
|15| └─PX PARTITION ITERATOR | |1 |7 |
1175
|16| └─HASH GROUP BY | |1 |7 |
1176
|17| └─TABLE FULL SCAN |t4 |1 |7 |
1177
================================================================================
1179
-------------------------------------
1180
0 - output([INTERNAL_FUNCTION(a.c1, a.c2, a.c3, a.c4, b.c1, b.c2, b.c3, b.c4, c.c1, c.c3, c.yyy, c.max(c4))]), filter(nil), rowset=16
1181
1 - output([INTERNAL_FUNCTION(a.c1, a.c2, a.c3, a.c4, b.c1, b.c2, b.c3, b.c4, c.c1, c.c3, c.yyy, c.max(c4))]), filter(nil), rowset=16
1183
2 - output([a.c1], [b.c1], [a.c3], [b.c3], [a.c2], [a.c4], [c.c1], [c.c3], [b.c2], [b.c4], [c.yyy], [c.max(c4)]), filter(nil), rowset=16
1184
equal_conds([a.c1 = b.c1], [a.c3 = b.c3]), other_conds(nil)
1185
3 - output([a.c1], [a.c2], [a.c3], [a.c4]), filter(nil), rowset=16
1186
affinitize, partition wise, force partition granule
1187
4 - output([a.c1], [a.c2], [a.c3], [a.c4]), filter(nil), rowset=16
1188
access([a.c1], [a.c2], [a.c3], [a.c4]), partitions(p0sp[0-2])
1189
is_index_back=false, is_global_index=false,
1190
range_key([a.c1], [a.c2], [a.c3]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true
1191
5 - output([b.c1], [b.c3], [c.c1], [c.c3], [b.c2], [b.c4], [c.yyy], [c.max(c4)]), filter(nil), rowset=16
1192
equal_conds([b.c1 = c.c1], [b.c3 = c.c3]), other_conds(nil)
1193
6 - output([b.c1], [b.c2], [b.c3], [b.c4]), filter(nil), rowset=16
1194
affinitize, partition wise, force partition granule
1195
7 - output([b.c1], [b.c2], [b.c3], [b.c4]), filter(nil), rowset=16
1196
access([b.c1], [b.c2], [b.c3], [b.c4]), partitions(p0sp[0-2])
1197
is_index_back=false, is_global_index=false,
1198
range_key([b.c1], [b.c2], [b.c3]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true
1199
8 - output([c.c1], [c.c3], [c.yyy], [c.max(c4)]), filter(nil), rowset=16
1200
9 - output([c.c1], [c.c3], [c.yyy], [c.max(c4)]), filter(nil), rowset=16
1201
(#keys=1, [c.c3]), is_single, dop=1
1202
10 - output([c.c1], [c.c3], [c.yyy], [c.max(c4)]), filter(nil), rowset=16
1203
access([c.c1], [c.c3], [c.yyy], [c.max(c4)])
1204
11 - output([t4.c1], [t4.c3], [T_FUN_SUM(t4.c2)], [T_FUN_MAX(t4.c4)]), filter(nil), rowset=16
1205
limit(1), offset(nil)
1206
12 - output([t4.c1], [t4.c3], [T_FUN_SUM(t4.c2)], [T_FUN_MAX(t4.c4)]), filter(nil), rowset=16
1207
sort_keys([T_FUN_SUM(t4.c2), ASC])
1208
13 - output([t4.c1], [t4.c3], [T_FUN_SUM(t4.c2)], [T_FUN_MAX(t4.c4)]), filter(nil), rowset=16
1210
14 - output([t4.c1], [t4.c3], [T_FUN_SUM(t4.c2)], [T_FUN_MAX(t4.c4)]), filter(nil), rowset=16
1211
sort_keys([T_FUN_SUM(t4.c2), ASC]), topn(1)
1212
15 - output([t4.c1], [t4.c3], [T_FUN_SUM(t4.c2)], [T_FUN_MAX(t4.c4)]), filter(nil), rowset=16
1213
partition wise, force partition granule
1214
16 - output([t4.c1], [t4.c3], [T_FUN_SUM(t4.c2)], [T_FUN_MAX(t4.c4)]), filter(nil), rowset=16
1215
group([t4.c3]), agg_func([T_FUN_SUM(t4.c2)], [T_FUN_MAX(t4.c4)])
1216
17 - output([t4.c1], [t4.c2], [t4.c3], [t4.c4]), filter(nil), rowset=16
1217
access([t4.c1], [t4.c2], [t4.c3], [t4.c4]), partitions(p0sp[0-2])
1218
is_index_back=false, is_global_index=false,
1219
range_key([t4.c1], [t4.c2], [t4.c3]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true
1220
explain select /*+use_nl(a b) use_nl(b c)*/ * from (select c1, c3, sum(c2) as yyy, max(c4) from t4 group by c3 order by yyy limit 1) as a join t5 as b on a.c1 = b.c1 and a.c3 = b.c3 join t6 as c on b.c1=c.c1 and b.c3 = c.c3;
1222
==================================================================================
1223
|ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)|
1224
----------------------------------------------------------------------------------
1225
|0 |PX COORDINATOR | |1 |20 |
1226
|1 |└─EXCHANGE OUT DISTR |:EX10002|1 |18 |
1227
|2 | └─HASH JOIN | |1 |14 |
1228
|3 | ├─PX PARTITION ITERATOR | |1 |7 |
1229
|4 | │ └─TABLE FULL SCAN |c |1 |7 |
1230
|5 | └─NESTED-LOOP JOIN | |1 |7 |
1231
|6 | ├─PX PARTITION ITERATOR | |1 |7 |
1232
|7 | │ └─TABLE FULL SCAN |b |1 |7 |
1233
|8 | └─MATERIAL | |1 |12 |
1234
|9 | └─EXCHANGE IN DISTR | |1 |12 |
1235
|10| └─EXCHANGE OUT DISTR (PKEY) |:EX10001|1 |11 |
1236
|11| └─SUBPLAN SCAN |a |1 |10 |
1237
|12| └─LIMIT | |1 |10 |
1238
|13| └─EXCHANGE IN MERGE SORT DISTR| |1 |10 |
1239
|14| └─EXCHANGE OUT DISTR |:EX10000|1 |9 |
1240
|15| └─TOP-N SORT | |1 |7 |
1241
|16| └─PX PARTITION ITERATOR | |1 |7 |
1242
|17| └─HASH GROUP BY | |1 |7 |
1243
|18| └─TABLE FULL SCAN |t4 |1 |7 |
1244
==================================================================================
1246
-------------------------------------
1247
0 - output([INTERNAL_FUNCTION(a.c1, a.c3, a.yyy, a.max(c4), b.c1, b.c2, b.c3, b.c4, c.c1, c.c2, c.c3, c.c4)]), filter(nil), rowset=16
1248
1 - output([INTERNAL_FUNCTION(a.c1, a.c3, a.yyy, a.max(c4), b.c1, b.c2, b.c3, b.c4, c.c1, c.c2, c.c3, c.c4)]), filter(nil), rowset=16
1250
2 - output([c.c1], [a.c1], [b.c3], [c.c3], [c.c2], [c.c4], [b.c1], [a.c3], [b.c2], [b.c4], [a.yyy], [a.max(c4)]), filter(nil), rowset=16
1251
equal_conds([c.c1 = a.c1], [b.c3 = c.c3]), other_conds(nil)
1252
3 - output([c.c1], [c.c2], [c.c3], [c.c4]), filter(nil), rowset=16
1253
affinitize, partition wise, force partition granule
1254
4 - output([c.c1], [c.c2], [c.c3], [c.c4]), filter(nil), rowset=16
1255
access([c.c1], [c.c2], [c.c3], [c.c4]), partitions(p0sp[0-2])
1256
is_index_back=false, is_global_index=false,
1257
range_key([c.c1], [c.c2], [c.c3]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true
1258
5 - output([a.c1], [b.c3], [b.c1], [a.c3], [b.c2], [b.c4], [a.yyy], [a.max(c4)]), filter(nil), rowset=16
1259
conds([a.c1 = b.c1], [a.c3 = b.c3]), nl_params_(nil), use_batch=false
1260
6 - output([b.c1], [b.c2], [b.c3], [b.c4]), filter(nil), rowset=16
1261
affinitize, partition wise, force partition granule
1262
7 - output([b.c1], [b.c2], [b.c3], [b.c4]), filter(nil), rowset=16
1263
access([b.c1], [b.c2], [b.c3], [b.c4]), partitions(p0sp[0-2])
1264
is_index_back=false, is_global_index=false,
1265
range_key([b.c1], [b.c2], [b.c3]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true
1266
8 - output([a.c1], [a.c3], [a.yyy], [a.max(c4)]), filter(nil), rowset=16
1267
9 - output([a.c1], [a.c3], [a.yyy], [a.max(c4)]), filter(nil), rowset=16
1268
10 - output([a.c1], [a.c3], [a.yyy], [a.max(c4)]), filter(nil), rowset=16
1269
(#keys=1, [a.c3]), is_single, dop=1
1270
11 - output([a.c1], [a.c3], [a.yyy], [a.max(c4)]), filter(nil), rowset=16
1271
access([a.c1], [a.c3], [a.yyy], [a.max(c4)])
1272
12 - output([t4.c1], [t4.c3], [T_FUN_SUM(t4.c2)], [T_FUN_MAX(t4.c4)]), filter(nil), rowset=16
1273
limit(1), offset(nil)
1274
13 - output([t4.c1], [t4.c3], [T_FUN_SUM(t4.c2)], [T_FUN_MAX(t4.c4)]), filter(nil), rowset=16
1275
sort_keys([T_FUN_SUM(t4.c2), ASC])
1276
14 - output([t4.c1], [t4.c3], [T_FUN_SUM(t4.c2)], [T_FUN_MAX(t4.c4)]), filter(nil), rowset=16
1278
15 - output([t4.c1], [t4.c3], [T_FUN_SUM(t4.c2)], [T_FUN_MAX(t4.c4)]), filter(nil), rowset=16
1279
sort_keys([T_FUN_SUM(t4.c2), ASC]), topn(1)
1280
16 - output([t4.c1], [t4.c3], [T_FUN_SUM(t4.c2)], [T_FUN_MAX(t4.c4)]), filter(nil), rowset=16
1281
partition wise, force partition granule
1282
17 - output([t4.c1], [t4.c3], [T_FUN_SUM(t4.c2)], [T_FUN_MAX(t4.c4)]), filter(nil), rowset=16
1283
group([t4.c3]), agg_func([T_FUN_SUM(t4.c2)], [T_FUN_MAX(t4.c4)])
1284
18 - output([t4.c1], [t4.c2], [t4.c3], [t4.c4]), filter(nil), rowset=16
1285
access([t4.c1], [t4.c2], [t4.c3], [t4.c4]), partitions(p0sp[0-2])
1286
is_index_back=false, is_global_index=false,
1287
range_key([t4.c1], [t4.c2], [t4.c3]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true
1288
explain select /*+use_nl(a b) use_nl(b c)*/ * from t5 as a join (select c1, c3, sum(c2) as yyy, max(c4) from t4 group by c3 order by yyy limit 1) as b on a.c1 = b.c1 and a.c3 = b.c3 join t6 as c on b.c1=c.c1 and b.c3 = c.c3;
1290
==================================================================================
1291
|ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)|
1292
----------------------------------------------------------------------------------
1293
|0 |PX COORDINATOR | |1 |20 |
1294
|1 |└─EXCHANGE OUT DISTR |:EX10002|1 |18 |
1295
|2 | └─HASH JOIN | |1 |14 |
1296
|3 | ├─PX PARTITION ITERATOR | |1 |7 |
1297
|4 | │ └─TABLE FULL SCAN |c |1 |7 |
1298
|5 | └─NESTED-LOOP JOIN | |1 |7 |
1299
|6 | ├─PX PARTITION ITERATOR | |1 |7 |
1300
|7 | │ └─TABLE FULL SCAN |a |1 |7 |
1301
|8 | └─MATERIAL | |1 |12 |
1302
|9 | └─EXCHANGE IN DISTR | |1 |12 |
1303
|10| └─EXCHANGE OUT DISTR (PKEY) |:EX10001|1 |11 |
1304
|11| └─SUBPLAN SCAN |b |1 |10 |
1305
|12| └─LIMIT | |1 |10 |
1306
|13| └─EXCHANGE IN MERGE SORT DISTR| |1 |10 |
1307
|14| └─EXCHANGE OUT DISTR |:EX10000|1 |9 |
1308
|15| └─TOP-N SORT | |1 |7 |
1309
|16| └─PX PARTITION ITERATOR | |1 |7 |
1310
|17| └─HASH GROUP BY | |1 |7 |
1311
|18| └─TABLE FULL SCAN |t4 |1 |7 |
1312
==================================================================================
1314
-------------------------------------
1315
0 - output([INTERNAL_FUNCTION(a.c1, a.c2, a.c3, a.c4, b.c1, b.c3, b.yyy, b.max(c4), c.c1, c.c2, c.c3, c.c4)]), filter(nil), rowset=16
1316
1 - output([INTERNAL_FUNCTION(a.c1, a.c2, a.c3, a.c4, b.c1, b.c3, b.yyy, b.max(c4), c.c1, c.c2, c.c3, c.c4)]), filter(nil), rowset=16
1318
2 - output([c.c1], [a.c1], [b.c3], [c.c3], [c.c2], [c.c4], [b.c1], [a.c3], [a.c2], [a.c4], [b.yyy], [b.max(c4)]), filter(nil), rowset=16
1319
equal_conds([c.c1 = a.c1], [b.c3 = c.c3]), other_conds(nil)
1320
3 - output([c.c1], [c.c2], [c.c3], [c.c4]), filter(nil), rowset=16
1321
affinitize, partition wise, force partition granule
1322
4 - output([c.c1], [c.c2], [c.c3], [c.c4]), filter(nil), rowset=16
1323
access([c.c1], [c.c2], [c.c3], [c.c4]), partitions(p0sp[0-2])
1324
is_index_back=false, is_global_index=false,
1325
range_key([c.c1], [c.c2], [c.c3]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true
1326
5 - output([a.c1], [b.c3], [b.c1], [a.c3], [a.c2], [a.c4], [b.yyy], [b.max(c4)]), filter(nil), rowset=16
1327
conds([a.c1 = b.c1], [a.c3 = b.c3]), nl_params_(nil), use_batch=false
1328
6 - output([a.c1], [a.c2], [a.c3], [a.c4]), filter(nil), rowset=16
1329
affinitize, partition wise, force partition granule
1330
7 - output([a.c1], [a.c2], [a.c3], [a.c4]), filter(nil), rowset=16
1331
access([a.c1], [a.c2], [a.c3], [a.c4]), partitions(p0sp[0-2])
1332
is_index_back=false, is_global_index=false,
1333
range_key([a.c1], [a.c2], [a.c3]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true
1334
8 - output([b.c3], [b.c1], [b.yyy], [b.max(c4)]), filter(nil), rowset=16
1335
9 - output([b.c3], [b.c1], [b.yyy], [b.max(c4)]), filter(nil), rowset=16
1336
10 - output([b.c3], [b.c1], [b.yyy], [b.max(c4)]), filter(nil), rowset=16
1337
(#keys=1, [b.c3]), is_single, dop=1
1338
11 - output([b.c1], [b.c3], [b.yyy], [b.max(c4)]), filter(nil), rowset=16
1339
access([b.c1], [b.c3], [b.yyy], [b.max(c4)])
1340
12 - output([t4.c1], [t4.c3], [T_FUN_SUM(t4.c2)], [T_FUN_MAX(t4.c4)]), filter(nil), rowset=16
1341
limit(1), offset(nil)
1342
13 - output([t4.c1], [t4.c3], [T_FUN_SUM(t4.c2)], [T_FUN_MAX(t4.c4)]), filter(nil), rowset=16
1343
sort_keys([T_FUN_SUM(t4.c2), ASC])
1344
14 - output([t4.c1], [t4.c3], [T_FUN_SUM(t4.c2)], [T_FUN_MAX(t4.c4)]), filter(nil), rowset=16
1346
15 - output([t4.c1], [t4.c3], [T_FUN_SUM(t4.c2)], [T_FUN_MAX(t4.c4)]), filter(nil), rowset=16
1347
sort_keys([T_FUN_SUM(t4.c2), ASC]), topn(1)
1348
16 - output([t4.c1], [t4.c3], [T_FUN_SUM(t4.c2)], [T_FUN_MAX(t4.c4)]), filter(nil), rowset=16
1349
partition wise, force partition granule
1350
17 - output([t4.c1], [t4.c3], [T_FUN_SUM(t4.c2)], [T_FUN_MAX(t4.c4)]), filter(nil), rowset=16
1351
group([t4.c3]), agg_func([T_FUN_SUM(t4.c2)], [T_FUN_MAX(t4.c4)])
1352
18 - output([t4.c1], [t4.c2], [t4.c3], [t4.c4]), filter(nil), rowset=16
1353
access([t4.c1], [t4.c2], [t4.c3], [t4.c4]), partitions(p0sp[0-2])
1354
is_index_back=false, is_global_index=false,
1355
range_key([t4.c1], [t4.c2], [t4.c3]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true
1356
explain select /*+use_nl(a b) use_nl(b c)*/ * from t5 as a join t6 as b on a.c1 = b.c1 and a.c3 = b.c3 join (select c1, c3, sum(c2) as yyy, max(c4) from t4 group by c3 order by yyy limit 1) as c on b.c1=c.c1 and b.c3 = c.c3;
1358
==================================================================================
1359
|ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)|
1360
----------------------------------------------------------------------------------
1361
|0 |PX COORDINATOR | |1 |20 |
1362
|1 |└─EXCHANGE OUT DISTR |:EX10002|1 |18 |
1363
|2 | └─HASH JOIN | |1 |14 |
1364
|3 | ├─PX PARTITION ITERATOR | |1 |7 |
1365
|4 | │ └─TABLE FULL SCAN |a |1 |7 |
1366
|5 | └─NESTED-LOOP JOIN | |1 |7 |
1367
|6 | ├─PX PARTITION ITERATOR | |1 |7 |
1368
|7 | │ └─TABLE FULL SCAN |b |1 |7 |
1369
|8 | └─MATERIAL | |1 |12 |
1370
|9 | └─EXCHANGE IN DISTR | |1 |12 |
1371
|10| └─EXCHANGE OUT DISTR (PKEY) |:EX10001|1 |11 |
1372
|11| └─SUBPLAN SCAN |c |1 |10 |
1373
|12| └─LIMIT | |1 |10 |
1374
|13| └─EXCHANGE IN MERGE SORT DISTR| |1 |10 |
1375
|14| └─EXCHANGE OUT DISTR |:EX10000|1 |9 |
1376
|15| └─TOP-N SORT | |1 |7 |
1377
|16| └─PX PARTITION ITERATOR | |1 |7 |
1378
|17| └─HASH GROUP BY | |1 |7 |
1379
|18| └─TABLE FULL SCAN |t4 |1 |7 |
1380
==================================================================================
1382
-------------------------------------
1383
0 - output([INTERNAL_FUNCTION(a.c1, a.c2, a.c3, a.c4, b.c1, b.c2, b.c3, b.c4, c.c1, c.c3, c.yyy, c.max(c4))]), filter(nil), rowset=16
1384
1 - output([INTERNAL_FUNCTION(a.c1, a.c2, a.c3, a.c4, b.c1, b.c2, b.c3, b.c4, c.c1, c.c3, c.yyy, c.max(c4))]), filter(nil), rowset=16
1386
2 - output([a.c1], [b.c1], [a.c3], [b.c3], [a.c2], [a.c4], [c.c1], [c.c3], [b.c2], [b.c4], [c.yyy], [c.max(c4)]), filter(nil), rowset=16
1387
equal_conds([a.c1 = b.c1], [a.c3 = b.c3]), other_conds(nil)
1388
3 - output([a.c1], [a.c2], [a.c3], [a.c4]), filter(nil), rowset=16
1389
affinitize, partition wise, force partition granule
1390
4 - output([a.c1], [a.c2], [a.c3], [a.c4]), filter(nil), rowset=16
1391
access([a.c1], [a.c2], [a.c3], [a.c4]), partitions(p0sp[0-2])
1392
is_index_back=false, is_global_index=false,
1393
range_key([a.c1], [a.c2], [a.c3]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true
1394
5 - output([b.c1], [b.c3], [c.c1], [c.c3], [b.c2], [b.c4], [c.yyy], [c.max(c4)]), filter(nil), rowset=16
1395
conds([b.c1 = c.c1], [b.c3 = c.c3]), nl_params_(nil), use_batch=false
1396
6 - output([b.c1], [b.c2], [b.c3], [b.c4]), filter(nil), rowset=16
1397
affinitize, partition wise, force partition granule
1398
7 - output([b.c1], [b.c2], [b.c3], [b.c4]), filter(nil), rowset=16
1399
access([b.c1], [b.c2], [b.c3], [b.c4]), partitions(p0sp[0-2])
1400
is_index_back=false, is_global_index=false,
1401
range_key([b.c1], [b.c2], [b.c3]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true
1402
8 - output([c.c1], [c.c3], [c.yyy], [c.max(c4)]), filter(nil), rowset=16
1403
9 - output([c.c1], [c.c3], [c.yyy], [c.max(c4)]), filter(nil), rowset=16
1404
10 - output([c.c1], [c.c3], [c.yyy], [c.max(c4)]), filter(nil), rowset=16
1405
(#keys=1, [c.c3]), is_single, dop=1
1406
11 - output([c.c1], [c.c3], [c.yyy], [c.max(c4)]), filter(nil), rowset=16
1407
access([c.c1], [c.c3], [c.yyy], [c.max(c4)])
1408
12 - output([t4.c1], [t4.c3], [T_FUN_SUM(t4.c2)], [T_FUN_MAX(t4.c4)]), filter(nil), rowset=16
1409
limit(1), offset(nil)
1410
13 - output([t4.c1], [t4.c3], [T_FUN_SUM(t4.c2)], [T_FUN_MAX(t4.c4)]), filter(nil), rowset=16
1411
sort_keys([T_FUN_SUM(t4.c2), ASC])
1412
14 - output([t4.c1], [t4.c3], [T_FUN_SUM(t4.c2)], [T_FUN_MAX(t4.c4)]), filter(nil), rowset=16
1414
15 - output([t4.c1], [t4.c3], [T_FUN_SUM(t4.c2)], [T_FUN_MAX(t4.c4)]), filter(nil), rowset=16
1415
sort_keys([T_FUN_SUM(t4.c2), ASC]), topn(1)
1416
16 - output([t4.c1], [t4.c3], [T_FUN_SUM(t4.c2)], [T_FUN_MAX(t4.c4)]), filter(nil), rowset=16
1417
partition wise, force partition granule
1418
17 - output([t4.c1], [t4.c3], [T_FUN_SUM(t4.c2)], [T_FUN_MAX(t4.c4)]), filter(nil), rowset=16
1419
group([t4.c3]), agg_func([T_FUN_SUM(t4.c2)], [T_FUN_MAX(t4.c4)])
1420
18 - output([t4.c1], [t4.c2], [t4.c3], [t4.c4]), filter(nil), rowset=16
1421
access([t4.c1], [t4.c2], [t4.c3], [t4.c4]), partitions(p0sp[0-2])
1422
is_index_back=false, is_global_index=false,
1423
range_key([t4.c1], [t4.c2], [t4.c3]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true
1424
explain select /*+use_nl(a b) use_nl(b c)*/ * from (select c1, c3, sum(c2) as yyy, max(c4) from t4 group by c3 order by yyy limit 2) as a left join t5 as b on a.c1 = b.c1 and a.c3 = b.c3 left join t6 as c on b.c1=c.c1 and b.c3 = c.c3;
1426
==============================================================================
1427
|ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)|
1428
------------------------------------------------------------------------------
1429
|0 |PX COORDINATOR | |1 |41 |
1430
|1 |└─EXCHANGE OUT DISTR |:EX10002|1 |39 |
1431
|2 | └─HASH OUTER JOIN | |1 |35 |
1432
|3 | ├─EXCHANGE IN DISTR | |1 |12 |
1433
|4 | │ └─EXCHANGE OUT DISTR (PKEY) |:EX10001|1 |11 |
1434
|5 | │ └─SUBPLAN SCAN |a |1 |10 |
1435
|6 | │ └─LIMIT | |1 |10 |
1436
|7 | │ └─EXCHANGE IN MERGE SORT DISTR| |1 |10 |
1437
|8 | │ └─EXCHANGE OUT DISTR |:EX10000|1 |9 |
1438
|9 | │ └─TOP-N SORT | |1 |7 |
1439
|10| │ └─PX PARTITION ITERATOR | |1 |7 |
1440
|11| │ └─HASH GROUP BY | |1 |7 |
1441
|12| │ └─TABLE FULL SCAN |t4 |1 |7 |
1442
|13| └─PX PARTITION ITERATOR | |1 |23 |
1443
|14| └─NESTED-LOOP OUTER JOIN | |1 |23 |
1444
|15| ├─TABLE FULL SCAN |b |1 |7 |
1445
|16| └─TABLE RANGE SCAN |c |1 |46 |
1446
==============================================================================
1448
-------------------------------------
1449
0 - output([INTERNAL_FUNCTION(a.c1, a.c3, a.yyy, a.max(c4), b.c1, b.c2, b.c3, b.c4, c.c1, c.c2, c.c3, c.c4)]), filter(nil), rowset=16
1450
1 - output([INTERNAL_FUNCTION(a.c1, a.c3, a.yyy, a.max(c4), b.c1, b.c2, b.c3, b.c4, c.c1, c.c2, c.c3, c.c4)]), filter(nil), rowset=16
1452
2 - output([a.c1], [b.c1], [a.c3], [b.c3], [a.yyy], [a.max(c4)], [b.c2], [b.c4], [c.c1], [c.c2], [c.c3], [c.c4]), filter(nil), rowset=16
1453
equal_conds([a.c1 = b.c1], [a.c3 = b.c3]), other_conds(nil)
1454
3 - output([a.c1], [a.c3], [a.yyy], [a.max(c4)]), filter(nil), rowset=16
1455
4 - output([a.c1], [a.c3], [a.yyy], [a.max(c4)]), filter(nil), rowset=16
1456
(#keys=1, [a.c3]), is_single, dop=1
1457
5 - output([a.c1], [a.c3], [a.yyy], [a.max(c4)]), filter(nil), rowset=16
1458
access([a.c1], [a.c3], [a.yyy], [a.max(c4)])
1459
6 - output([t4.c1], [t4.c3], [T_FUN_SUM(t4.c2)], [T_FUN_MAX(t4.c4)]), filter(nil), rowset=16
1460
limit(2), offset(nil)
1461
7 - output([t4.c1], [t4.c3], [T_FUN_SUM(t4.c2)], [T_FUN_MAX(t4.c4)]), filter(nil), rowset=16
1462
sort_keys([T_FUN_SUM(t4.c2), ASC])
1463
8 - output([t4.c1], [t4.c3], [T_FUN_SUM(t4.c2)], [T_FUN_MAX(t4.c4)]), filter(nil), rowset=16
1465
9 - output([t4.c1], [t4.c3], [T_FUN_SUM(t4.c2)], [T_FUN_MAX(t4.c4)]), filter(nil), rowset=16
1466
sort_keys([T_FUN_SUM(t4.c2), ASC]), topn(2)
1467
10 - output([t4.c1], [t4.c3], [T_FUN_SUM(t4.c2)], [T_FUN_MAX(t4.c4)]), filter(nil), rowset=16
1468
partition wise, force partition granule
1469
11 - output([t4.c1], [t4.c3], [T_FUN_SUM(t4.c2)], [T_FUN_MAX(t4.c4)]), filter(nil), rowset=16
1470
group([t4.c3]), agg_func([T_FUN_SUM(t4.c2)], [T_FUN_MAX(t4.c4)])
1471
12 - output([t4.c1], [t4.c2], [t4.c3], [t4.c4]), filter(nil), rowset=16
1472
access([t4.c1], [t4.c2], [t4.c3], [t4.c4]), partitions(p0sp[0-2])
1473
is_index_back=false, is_global_index=false,
1474
range_key([t4.c1], [t4.c2], [t4.c3]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true
1475
13 - output([b.c1], [b.c3], [b.c2], [b.c4], [c.c1], [c.c2], [c.c3], [c.c4]), filter(nil), rowset=16
1476
affinitize, partition wise, force partition granule
1477
14 - output([b.c1], [b.c3], [b.c2], [b.c4], [c.c1], [c.c2], [c.c3], [c.c4]), filter(nil), rowset=16
1478
conds(nil), nl_params_([b.c1(:0)], [b.c3(:1)]), use_batch=true
1479
15 - output([b.c1], [b.c2], [b.c3], [b.c4]), filter(nil), rowset=16
1480
access([b.c1], [b.c2], [b.c3], [b.c4]), partitions(p0sp[0-2])
1481
is_index_back=false, is_global_index=false,
1482
range_key([b.c1], [b.c2], [b.c3]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true
1483
16 - output([c.c1], [c.c2], [c.c3], [c.c4]), filter([:1 = c.c3]), rowset=16
1484
access([GROUP_ID], [c.c1], [c.c2], [c.c3], [c.c4]), partitions(p0sp[0-2])
1485
is_index_back=false, is_global_index=false, filter_before_indexback[false],
1486
range_key([c.c1], [c.c2], [c.c3]), range(MIN ; MAX),
1487
range_cond([:0 = c.c1])
1488
explain select /*+use_nl(a b) use_nl(b c)*/ * from t5 as a left join (select c1, c3, sum(c2) as yyy, max(c4) from t4 group by c3 order by yyy limit 1) as b on a.c1 = b.c1 and a.c3 = b.c3 left join t6 as c on b.c1=c.c1 and b.c3 = c.c3;
1490
======================================================================================
1491
|ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)|
1492
--------------------------------------------------------------------------------------
1493
|0 |PX COORDINATOR | |1 |24 |
1494
|1 |└─EXCHANGE OUT DISTR |:EX10003|1 |22 |
1495
|2 | └─HASH RIGHT OUTER JOIN | |1 |18 |
1496
|3 | ├─PX PARTITION ITERATOR | |1 |7 |
1497
|4 | │ └─TABLE FULL SCAN |c |1 |7 |
1498
|5 | └─EXCHANGE IN DISTR | |1 |11 |
1499
|6 | └─EXCHANGE OUT DISTR (PKEY) |:EX10002|1 |10 |
1500
|7 | └─NESTED-LOOP OUTER JOIN | |1 |7 |
1501
|8 | ├─PX PARTITION ITERATOR | |1 |7 |
1502
|9 | │ └─TABLE FULL SCAN |a |1 |7 |
1503
|10| └─MATERIAL | |1 |12 |
1504
|11| └─EXCHANGE IN DISTR | |1 |12 |
1505
|12| └─EXCHANGE OUT DISTR (PKEY) |:EX10001|1 |11 |
1506
|13| └─SUBPLAN SCAN |b |1 |10 |
1507
|14| └─LIMIT | |1 |10 |
1508
|15| └─EXCHANGE IN MERGE SORT DISTR| |1 |10 |
1509
|16| └─EXCHANGE OUT DISTR |:EX10000|1 |9 |
1510
|17| └─TOP-N SORT | |1 |7 |
1511
|18| └─PX PARTITION ITERATOR | |1 |7 |
1512
|19| └─HASH GROUP BY | |1 |7 |
1513
|20| └─TABLE FULL SCAN |t4 |1 |7 |
1514
======================================================================================
1516
-------------------------------------
1517
0 - output([INTERNAL_FUNCTION(a.c1, a.c2, a.c3, a.c4, b.c1, b.c3, b.yyy, b.max(c4), c.c1, c.c2, c.c3, c.c4)]), filter(nil), rowset=16
1518
1 - output([INTERNAL_FUNCTION(a.c1, a.c2, a.c3, a.c4, b.c1, b.c3, b.yyy, b.max(c4), c.c1, c.c2, c.c3, c.c4)]), filter(nil), rowset=16
1520
2 - output([b.c1], [c.c1], [b.c3], [c.c3], [c.c2], [c.c4], [a.c1], [a.c3], [a.c2], [a.c4], [b.yyy], [b.max(c4)]), filter(nil), rowset=16
1521
equal_conds([b.c1 = c.c1], [b.c3 = c.c3]), other_conds(nil)
1522
3 - output([c.c1], [c.c2], [c.c3], [c.c4]), filter(nil), rowset=16
1523
affinitize, force partition granule
1524
4 - output([c.c1], [c.c2], [c.c3], [c.c4]), filter(nil), rowset=16
1525
access([c.c1], [c.c2], [c.c3], [c.c4]), partitions(p0sp[0-2])
1526
is_index_back=false, is_global_index=false,
1527
range_key([c.c1], [c.c2], [c.c3]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true
1528
5 - output([b.c1], [b.c3], [a.c1], [a.c3], [a.c2], [a.c4], [b.yyy], [b.max(c4)]), filter(nil), rowset=16
1529
6 - output([b.c1], [b.c3], [a.c1], [a.c3], [a.c2], [a.c4], [b.yyy], [b.max(c4)]), filter(nil), rowset=16
1530
(#keys=1, [b.c3]), dop=1
1531
7 - output([b.c1], [b.c3], [a.c1], [a.c3], [a.c2], [a.c4], [b.yyy], [b.max(c4)]), filter(nil), rowset=16
1532
conds([a.c1 = b.c1], [a.c3 = b.c3]), nl_params_(nil), use_batch=false
1533
8 - output([a.c1], [a.c2], [a.c3], [a.c4]), filter(nil), rowset=16
1534
affinitize, force partition granule
1535
9 - output([a.c1], [a.c2], [a.c3], [a.c4]), filter(nil), rowset=16
1536
access([a.c1], [a.c2], [a.c3], [a.c4]), partitions(p0sp[0-2])
1537
is_index_back=false, is_global_index=false,
1538
range_key([a.c1], [a.c2], [a.c3]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true
1539
10 - output([b.c1], [b.c3], [b.yyy], [b.max(c4)]), filter(nil), rowset=16
1540
11 - output([b.c1], [b.c3], [b.yyy], [b.max(c4)]), filter(nil), rowset=16
1541
12 - output([b.c1], [b.c3], [b.yyy], [b.max(c4)]), filter(nil), rowset=16
1542
(#keys=1, [b.c3]), is_single, dop=1
1543
13 - output([b.c1], [b.c3], [b.yyy], [b.max(c4)]), filter(nil), rowset=16
1544
access([b.c1], [b.c3], [b.yyy], [b.max(c4)])
1545
14 - output([t4.c1], [t4.c3], [T_FUN_SUM(t4.c2)], [T_FUN_MAX(t4.c4)]), filter(nil), rowset=16
1546
limit(1), offset(nil)
1547
15 - output([t4.c1], [t4.c3], [T_FUN_SUM(t4.c2)], [T_FUN_MAX(t4.c4)]), filter(nil), rowset=16
1548
sort_keys([T_FUN_SUM(t4.c2), ASC])
1549
16 - output([t4.c1], [t4.c3], [T_FUN_SUM(t4.c2)], [T_FUN_MAX(t4.c4)]), filter(nil), rowset=16
1551
17 - output([t4.c1], [t4.c3], [T_FUN_SUM(t4.c2)], [T_FUN_MAX(t4.c4)]), filter(nil), rowset=16
1552
sort_keys([T_FUN_SUM(t4.c2), ASC]), topn(1)
1553
18 - output([t4.c1], [t4.c3], [T_FUN_SUM(t4.c2)], [T_FUN_MAX(t4.c4)]), filter(nil), rowset=16
1554
partition wise, force partition granule
1555
19 - output([t4.c1], [t4.c3], [T_FUN_SUM(t4.c2)], [T_FUN_MAX(t4.c4)]), filter(nil), rowset=16
1556
group([t4.c3]), agg_func([T_FUN_SUM(t4.c2)], [T_FUN_MAX(t4.c4)])
1557
20 - output([t4.c1], [t4.c2], [t4.c3], [t4.c4]), filter(nil), rowset=16
1558
access([t4.c1], [t4.c2], [t4.c3], [t4.c4]), partitions(p0sp[0-2])
1559
is_index_back=false, is_global_index=false,
1560
range_key([t4.c1], [t4.c2], [t4.c3]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true
1561
explain select /*+use_nl(a b) use_nl(b c)*/ * from t5 as a left join t6 as b on a.c1 = b.c1 and a.c3 = b.c3 left join (select c1, c3, sum(c2) as yyy, max(c4) from t4 group by c3 order by yyy limit 1) as c on b.c1=c.c1 and b.c3 = c.c3;
1563
==================================================================================
1564
|ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)|
1565
----------------------------------------------------------------------------------
1566
|0 |PX COORDINATOR | |1 |20 |
1567
|1 |└─EXCHANGE OUT DISTR |:EX10002|1 |18 |
1568
|2 | └─HASH OUTER JOIN | |1 |14 |
1569
|3 | ├─PX PARTITION ITERATOR | |1 |7 |
1570
|4 | │ └─TABLE FULL SCAN |a |1 |7 |
1571
|5 | └─NESTED-LOOP OUTER JOIN | |1 |7 |
1572
|6 | ├─PX PARTITION ITERATOR | |1 |7 |
1573
|7 | │ └─TABLE FULL SCAN |b |1 |7 |
1574
|8 | └─MATERIAL | |1 |12 |
1575
|9 | └─EXCHANGE IN DISTR | |1 |12 |
1576
|10| └─EXCHANGE OUT DISTR (PKEY) |:EX10001|1 |11 |
1577
|11| └─SUBPLAN SCAN |c |1 |10 |
1578
|12| └─LIMIT | |1 |10 |
1579
|13| └─EXCHANGE IN MERGE SORT DISTR| |1 |10 |
1580
|14| └─EXCHANGE OUT DISTR |:EX10000|1 |9 |
1581
|15| └─TOP-N SORT | |1 |7 |
1582
|16| └─PX PARTITION ITERATOR | |1 |7 |
1583
|17| └─HASH GROUP BY | |1 |7 |
1584
|18| └─TABLE FULL SCAN |t4 |1 |7 |
1585
==================================================================================
1587
-------------------------------------
1588
0 - output([INTERNAL_FUNCTION(a.c1, a.c2, a.c3, a.c4, b.c1, b.c2, b.c3, b.c4, c.c1, c.c3, c.yyy, c.max(c4))]), filter(nil), rowset=16
1589
1 - output([INTERNAL_FUNCTION(a.c1, a.c2, a.c3, a.c4, b.c1, b.c2, b.c3, b.c4, c.c1, c.c3, c.yyy, c.max(c4))]), filter(nil), rowset=16
1591
2 - output([a.c1], [b.c1], [a.c3], [b.c3], [a.c2], [a.c4], [c.c1], [c.c3], [b.c2], [b.c4], [c.yyy], [c.max(c4)]), filter(nil), rowset=16
1592
equal_conds([a.c1 = b.c1], [a.c3 = b.c3]), other_conds(nil)
1593
3 - output([a.c1], [a.c2], [a.c3], [a.c4]), filter(nil), rowset=16
1594
affinitize, partition wise, force partition granule
1595
4 - output([a.c1], [a.c2], [a.c3], [a.c4]), filter(nil), rowset=16
1596
access([a.c1], [a.c2], [a.c3], [a.c4]), partitions(p0sp[0-2])
1597
is_index_back=false, is_global_index=false,
1598
range_key([a.c1], [a.c2], [a.c3]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true
1599
5 - output([b.c1], [b.c3], [c.c1], [c.c3], [b.c2], [b.c4], [c.yyy], [c.max(c4)]), filter(nil), rowset=16
1600
conds([b.c1 = c.c1], [b.c3 = c.c3]), nl_params_(nil), use_batch=false
1601
6 - output([b.c1], [b.c2], [b.c3], [b.c4]), filter(nil), rowset=16
1602
affinitize, partition wise, force partition granule
1603
7 - output([b.c1], [b.c2], [b.c3], [b.c4]), filter(nil), rowset=16
1604
access([b.c1], [b.c2], [b.c3], [b.c4]), partitions(p0sp[0-2])
1605
is_index_back=false, is_global_index=false,
1606
range_key([b.c1], [b.c2], [b.c3]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true
1607
8 - output([c.c1], [c.c3], [c.yyy], [c.max(c4)]), filter(nil), rowset=16
1608
9 - output([c.c1], [c.c3], [c.yyy], [c.max(c4)]), filter(nil), rowset=16
1609
10 - output([c.c1], [c.c3], [c.yyy], [c.max(c4)]), filter(nil), rowset=16
1610
(#keys=1, [c.c3]), is_single, dop=1
1611
11 - output([c.c1], [c.c3], [c.yyy], [c.max(c4)]), filter(nil), rowset=16
1612
access([c.c1], [c.c3], [c.yyy], [c.max(c4)])
1613
12 - output([t4.c1], [t4.c3], [T_FUN_SUM(t4.c2)], [T_FUN_MAX(t4.c4)]), filter(nil), rowset=16
1614
limit(1), offset(nil)
1615
13 - output([t4.c1], [t4.c3], [T_FUN_SUM(t4.c2)], [T_FUN_MAX(t4.c4)]), filter(nil), rowset=16
1616
sort_keys([T_FUN_SUM(t4.c2), ASC])
1617
14 - output([t4.c1], [t4.c3], [T_FUN_SUM(t4.c2)], [T_FUN_MAX(t4.c4)]), filter(nil), rowset=16
1619
15 - output([t4.c1], [t4.c3], [T_FUN_SUM(t4.c2)], [T_FUN_MAX(t4.c4)]), filter(nil), rowset=16
1620
sort_keys([T_FUN_SUM(t4.c2), ASC]), topn(1)
1621
16 - output([t4.c1], [t4.c3], [T_FUN_SUM(t4.c2)], [T_FUN_MAX(t4.c4)]), filter(nil), rowset=16
1622
partition wise, force partition granule
1623
17 - output([t4.c1], [t4.c3], [T_FUN_SUM(t4.c2)], [T_FUN_MAX(t4.c4)]), filter(nil), rowset=16
1624
group([t4.c3]), agg_func([T_FUN_SUM(t4.c2)], [T_FUN_MAX(t4.c4)])
1625
18 - output([t4.c1], [t4.c2], [t4.c3], [t4.c4]), filter(nil), rowset=16
1626
access([t4.c1], [t4.c2], [t4.c3], [t4.c4]), partitions(p0sp[0-2])
1627
is_index_back=false, is_global_index=false,
1628
range_key([t4.c1], [t4.c2], [t4.c3]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true
1629
explain select /*+use_nl(c)*/ * from t5 as a left join t6 as b on a.c1 = b.c1 and a.c3 = b.c3 left join t4 as c on b.c1=c.c1 and b.c3 = c.c3;
1631
================================================================
1632
|ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)|
1633
----------------------------------------------------------------
1634
|0 |PX COORDINATOR | |1 |26 |
1635
|1 |└─EXCHANGE OUT DISTR |:EX10000|1 |25 |
1636
|2 | └─PX PARTITION ITERATOR | |1 |20 |
1637
|3 | └─HASH RIGHT OUTER JOIN | |1 |20 |
1638
|4 | ├─TABLE FULL SCAN |c |1 |7 |
1639
|5 | └─HASH OUTER JOIN | |1 |14 |
1640
|6 | ├─TABLE FULL SCAN |a |1 |7 |
1641
|7 | └─TABLE FULL SCAN |b |1 |7 |
1642
================================================================
1644
-------------------------------------
1645
0 - output([INTERNAL_FUNCTION(a.c1, a.c2, a.c3, a.c4, b.c1, b.c2, b.c3, b.c4, c.c1, c.c2, c.c3, c.c4)]), filter(nil), rowset=16
1646
1 - output([INTERNAL_FUNCTION(a.c1, a.c2, a.c3, a.c4, b.c1, b.c2, b.c3, b.c4, c.c1, c.c2, c.c3, c.c4)]), filter(nil), rowset=16
1648
2 - output([b.c1], [c.c1], [b.c3], [c.c3], [c.c2], [c.c4], [a.c1], [a.c3], [a.c2], [a.c4], [b.c2], [b.c4]), filter(nil), rowset=16
1649
partition wise, force partition granule
1650
3 - output([b.c1], [c.c1], [b.c3], [c.c3], [c.c2], [c.c4], [a.c1], [a.c3], [a.c2], [a.c4], [b.c2], [b.c4]), filter(nil), rowset=16
1651
equal_conds([b.c1 = c.c1], [b.c3 = c.c3]), other_conds(nil)
1652
4 - output([c.c1], [c.c2], [c.c3], [c.c4]), filter(nil), rowset=16
1653
access([c.c1], [c.c2], [c.c3], [c.c4]), partitions(p0sp[0-2])
1654
is_index_back=false, is_global_index=false,
1655
range_key([c.c1], [c.c2], [c.c3]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true
1656
5 - output([b.c1], [b.c3], [a.c1], [a.c3], [a.c2], [a.c4], [b.c2], [b.c4]), filter(nil), rowset=16
1657
equal_conds([a.c1 = b.c1], [a.c3 = b.c3]), other_conds(nil)
1658
6 - output([a.c1], [a.c2], [a.c3], [a.c4]), filter(nil), rowset=16
1659
access([a.c1], [a.c2], [a.c3], [a.c4]), partitions(p0sp[0-2])
1660
is_index_back=false, is_global_index=false,
1661
range_key([a.c1], [a.c2], [a.c3]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true
1662
7 - output([b.c1], [b.c2], [b.c3], [b.c4]), filter(nil), rowset=16
1663
access([b.c1], [b.c2], [b.c3], [b.c4]), partitions(p0sp[0-2])
1664
is_index_back=false, is_global_index=false,
1665
range_key([b.c1], [b.c2], [b.c3]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true
1666
explain select /*+use_nl(c)*/ * from t5 as a left join t6 as b on a.c1 = b.c1 and a.c3 = b.c3 left join t4 as c on b.c1=c.c1 and b.c3 = c.c3 limit 100;
1668
===================================================================================
1669
|ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)|
1670
-----------------------------------------------------------------------------------
1672
|1 |└─PX COORDINATOR | |1 |30 |
1673
|2 | └─EXCHANGE OUT DISTR |:EX10002|1 |29 |
1674
|3 | └─LIMIT | |1 |24 |
1675
|4 | └─HASH RIGHT OUTER JOIN | |1 |24 |
1676
|5 | ├─PX PARTITION ITERATOR | |1 |7 |
1677
|6 | │ └─TABLE FULL SCAN |c |1 |7 |
1678
|7 | └─HASH OUTER JOIN | |1 |18 |
1679
|8 | ├─EXCHANGE IN DISTR | |1 |11 |
1680
|9 | │ └─EXCHANGE OUT DISTR (PKEY) |:EX10001|1 |10 |
1681
|10| │ └─SUBPLAN SCAN |VIEW1 |1 |9 |
1682
|11| │ └─LIMIT | |1 |9 |
1683
|12| │ └─EXCHANGE IN DISTR | |1 |9 |
1684
|13| │ └─EXCHANGE OUT DISTR |:EX10000|1 |8 |
1685
|14| │ └─LIMIT | |1 |7 |
1686
|15| │ └─PX PARTITION ITERATOR| |1 |7 |
1687
|16| │ └─TABLE FULL SCAN |a |1 |7 |
1688
|17| └─PX PARTITION ITERATOR | |1 |7 |
1689
|18| └─TABLE FULL SCAN |b |1 |7 |
1690
===================================================================================
1692
-------------------------------------
1693
0 - output([VIEW1.a.c1], [VIEW1.a.c2], [VIEW1.a.c3], [VIEW1.a.c4], [b.c1], [b.c2], [b.c3], [b.c4], [c.c1], [c.c2], [c.c3], [c.c4]), filter(nil), rowset=16
1694
limit(100), offset(nil)
1695
1 - output([VIEW1.a.c1], [VIEW1.a.c2], [VIEW1.a.c3], [VIEW1.a.c4], [b.c1], [b.c2], [b.c3], [b.c4], [c.c1], [c.c2], [c.c3], [c.c4]), filter(nil), rowset=16
1696
2 - output([VIEW1.a.c1], [VIEW1.a.c2], [VIEW1.a.c3], [VIEW1.a.c4], [b.c1], [b.c2], [b.c3], [b.c4], [c.c1], [c.c2], [c.c3], [c.c4]), filter(nil), rowset=16
1698
3 - output([VIEW1.a.c1], [VIEW1.a.c2], [VIEW1.a.c3], [VIEW1.a.c4], [b.c1], [b.c2], [b.c3], [b.c4], [c.c1], [c.c2], [c.c3], [c.c4]), filter(nil), rowset=16
1699
limit(100), offset(nil)
1700
4 - output([VIEW1.a.c1], [VIEW1.a.c2], [VIEW1.a.c3], [VIEW1.a.c4], [b.c1], [b.c2], [b.c3], [b.c4], [c.c1], [c.c2], [c.c3], [c.c4]), filter(nil), rowset=16
1701
equal_conds([b.c1 = c.c1], [b.c3 = c.c3]), other_conds(nil)
1702
5 - output([c.c1], [c.c2], [c.c3], [c.c4]), filter(nil), rowset=16
1703
affinitize, partition wise, force partition granule
1704
6 - output([c.c1], [c.c2], [c.c3], [c.c4]), filter(nil), rowset=16
1705
access([c.c1], [c.c2], [c.c3], [c.c4]), partitions(p0sp[0-2])
1706
is_index_back=false, is_global_index=false,
1707
range_key([c.c1], [c.c2], [c.c3]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true
1708
7 - output([VIEW1.a.c1], [VIEW1.a.c2], [VIEW1.a.c3], [VIEW1.a.c4], [b.c1], [b.c2], [b.c3], [b.c4]), filter(nil), rowset=16
1709
equal_conds([VIEW1.a.c1 = b.c1], [VIEW1.a.c3 = b.c3]), other_conds(nil)
1710
8 - output([VIEW1.a.c1], [VIEW1.a.c2], [VIEW1.a.c3], [VIEW1.a.c4]), filter(nil), rowset=16
1711
9 - output([VIEW1.a.c1], [VIEW1.a.c2], [VIEW1.a.c3], [VIEW1.a.c4]), filter(nil), rowset=16
1712
(#keys=1, [VIEW1.a.c3]), is_single, dop=1
1713
10 - output([VIEW1.a.c1], [VIEW1.a.c3], [VIEW1.a.c2], [VIEW1.a.c4]), filter(nil), rowset=16
1714
access([VIEW1.a.c1], [VIEW1.a.c3], [VIEW1.a.c2], [VIEW1.a.c4])
1715
11 - output([a.c1], [a.c3], [a.c2], [a.c4]), filter(nil), rowset=16
1716
limit(100), offset(nil)
1717
12 - output([a.c1], [a.c3], [a.c2], [a.c4]), filter(nil), rowset=16
1718
13 - output([a.c1], [a.c3], [a.c2], [a.c4]), filter(nil), rowset=16
1720
14 - output([a.c1], [a.c3], [a.c2], [a.c4]), filter(nil), rowset=16
1721
limit(100), offset(nil)
1722
15 - output([a.c1], [a.c2], [a.c3], [a.c4]), filter(nil), rowset=16
1723
force partition granule
1724
16 - output([a.c1], [a.c2], [a.c3], [a.c4]), filter(nil), rowset=16
1725
access([a.c1], [a.c2], [a.c3], [a.c4]), partitions(p0sp[0-2])
1726
limit(100), offset(nil), is_index_back=false, is_global_index=false,
1727
range_key([a.c1], [a.c2], [a.c3]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true
1728
17 - output([b.c1], [b.c2], [b.c3], [b.c4]), filter(nil), rowset=16
1729
affinitize, partition wise, force partition granule
1730
18 - output([b.c1], [b.c2], [b.c3], [b.c4]), filter(nil), rowset=16
1731
access([b.c1], [b.c2], [b.c3], [b.c4]), partitions(p0sp[0-2])
1732
is_index_back=false, is_global_index=false,
1733
range_key([b.c1], [b.c2], [b.c3]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true
1734
explain select /*+use_bnl(a b)*/ * from (select c1, c3, sum(c2) as yyy, max(c4) from t4 group by c3 order by yyy limit 2) as a left join t5 as b on a.c1 = b.c1 and a.c3 = b.c3 ;
1736
==============================================================================
1737
|ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)|
1738
------------------------------------------------------------------------------
1739
|0 |PX COORDINATOR | |1 |23 |
1740
|1 |└─EXCHANGE OUT DISTR |:EX10002|1 |22 |
1741
|2 | └─HASH RIGHT OUTER JOIN | |1 |19 |
1742
|3 | ├─PX PARTITION ITERATOR | |1 |7 |
1743
|4 | │ └─TABLE FULL SCAN |b |1 |7 |
1744
|5 | └─EXCHANGE IN DISTR | |1 |12 |
1745
|6 | └─EXCHANGE OUT DISTR (PKEY) |:EX10001|1 |11 |
1746
|7 | └─SUBPLAN SCAN |a |1 |10 |
1747
|8 | └─LIMIT | |1 |10 |
1748
|9 | └─EXCHANGE IN MERGE SORT DISTR| |1 |10 |
1749
|10| └─EXCHANGE OUT DISTR |:EX10000|1 |9 |
1750
|11| └─TOP-N SORT | |1 |7 |
1751
|12| └─PX PARTITION ITERATOR | |1 |7 |
1752
|13| └─HASH GROUP BY | |1 |7 |
1753
|14| └─TABLE FULL SCAN |t4 |1 |7 |
1754
==============================================================================
1756
-------------------------------------
1757
0 - output([INTERNAL_FUNCTION(a.c1, a.c3, a.yyy, a.max(c4), b.c1, b.c2, b.c3, b.c4)]), filter(nil), rowset=16
1758
1 - output([INTERNAL_FUNCTION(a.c1, a.c3, a.yyy, a.max(c4), b.c1, b.c2, b.c3, b.c4)]), filter(nil), rowset=16
1760
2 - output([a.c1], [b.c1], [a.c3], [b.c3], [b.c2], [b.c4], [a.yyy], [a.max(c4)]), filter(nil), rowset=16
1761
equal_conds([a.c1 = b.c1], [a.c3 = b.c3]), other_conds(nil)
1762
3 - output([b.c1], [b.c2], [b.c3], [b.c4]), filter(nil), rowset=16
1763
affinitize, force partition granule
1764
4 - output([b.c1], [b.c2], [b.c3], [b.c4]), filter(nil), rowset=16
1765
access([b.c1], [b.c2], [b.c3], [b.c4]), partitions(p0sp[0-2])
1766
is_index_back=false, is_global_index=false,
1767
range_key([b.c1], [b.c2], [b.c3]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true
1768
5 - output([a.c1], [a.c3], [a.yyy], [a.max(c4)]), filter(nil), rowset=16
1769
6 - output([a.c1], [a.c3], [a.yyy], [a.max(c4)]), filter(nil), rowset=16
1770
(#keys=1, [a.c3]), is_single, dop=1
1771
7 - output([a.c1], [a.c3], [a.yyy], [a.max(c4)]), filter(nil), rowset=16
1772
access([a.c1], [a.c3], [a.yyy], [a.max(c4)])
1773
8 - output([t4.c1], [t4.c3], [T_FUN_SUM(t4.c2)], [T_FUN_MAX(t4.c4)]), filter(nil), rowset=16
1774
limit(2), offset(nil)
1775
9 - output([t4.c1], [t4.c3], [T_FUN_SUM(t4.c2)], [T_FUN_MAX(t4.c4)]), filter(nil), rowset=16
1776
sort_keys([T_FUN_SUM(t4.c2), ASC])
1777
10 - output([t4.c1], [t4.c3], [T_FUN_SUM(t4.c2)], [T_FUN_MAX(t4.c4)]), filter(nil), rowset=16
1779
11 - output([t4.c1], [t4.c3], [T_FUN_SUM(t4.c2)], [T_FUN_MAX(t4.c4)]), filter(nil), rowset=16
1780
sort_keys([T_FUN_SUM(t4.c2), ASC]), topn(2)
1781
12 - output([t4.c1], [t4.c3], [T_FUN_SUM(t4.c2)], [T_FUN_MAX(t4.c4)]), filter(nil), rowset=16
1782
partition wise, force partition granule
1783
13 - output([t4.c1], [t4.c3], [T_FUN_SUM(t4.c2)], [T_FUN_MAX(t4.c4)]), filter(nil), rowset=16
1784
group([t4.c3]), agg_func([T_FUN_SUM(t4.c2)], [T_FUN_MAX(t4.c4)])
1785
14 - output([t4.c1], [t4.c2], [t4.c3], [t4.c4]), filter(nil), rowset=16
1786
access([t4.c1], [t4.c2], [t4.c3], [t4.c4]), partitions(p0sp[0-2])
1787
is_index_back=false, is_global_index=false,
1788
range_key([t4.c1], [t4.c2], [t4.c3]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true