oceanbase

1
set ob_query_timeout=1000000000;
2
drop database if exists px_test;
3
create database px_test;
4
use px_test;
5
create table stu (
6
sid     int,
7
name    varchar(32),
8
cls     int,
9
primary key (cls, sid)
10
) partition by hash(sid) partitions 6;
11
create table teacher (
12
tid        int,
13
name       varchar(32),
14
subject    varchar(4),
15
primary key (tid)
16
) partition by hash(tid) partitions 8;
17
create table score (
18
sid     int,
19
subject varchar(4),
20
score   int,
21
primary key (sid, subject)
22
) partition by hash(sid) partitions 6;
23
insert into stu values
24
(11, 'a1', 1),
25
(12, 'b1', 1),
26
(13, 'c1', 1),
27
(21, 'a2', 2),
28
(22, 'b2', 2),
29
(31, 'a3', 3),
30
(41, 'a4', 4),
31
(42, 'b4', 4),
32
(51, 'a5', 5),
33
(52, 'b5', 5),
34
(61, 'a6', 6),
35
(62, 'b6', 6),
36
(63, 'c6', 6),
37
(64, 'd6', 6);
38
insert into teacher values
39
(1, 'Miss Zhang', 'EN'),
40
(2, 'Mr Wang', 'MA');
41
insert into score values
42
(11, 'EN', 60),
43
(12, 'EN', 70),
44
(13, 'EN', 80),
45
(21, 'EN', 58),
46
(22, 'EN', 90),
47
(31, 'EN', 80),
48
(41, 'EN', 80),
49
(42, 'EN', 90),
50
(51, 'EN', 89),
51
(52, 'EN', 99),
52
(61, 'EN', 100),
53
(62, 'EN', 90),
54
(63, 'EN', 99),
55
(64, 'EN', 87);
56
insert into score values
57
(11, 'MA', 60),
58
(12, 'MA', 70),
59
(13, 'MA', 80),
60
(21, 'MA', 58),
61
(22, 'MA', 90),
62
(31, 'MA', 80),
63
(41, 'MA', 80),
64
(42, 'MA', 90),
65
(51, 'MA', 89),
66
(52, 'MA', 99),
67
(61, 'MA', 100),
68
(62, 'MA', 90),
69
(63, 'MA', 99),
70
(64, 'MA', 87);
71
select * from stu;
72
sid	name	cls
73
11	a1	1
74
12	b1	1
75
13	c1	1
76
21	a2	2
77
22	b2	2
78
31	a3	3
79
41	a4	4
80
42	b4	4
81
51	a5	5
82
52	b5	5
83
61	a6	6
84
62	b6	6
85
63	c6	6
86
64	d6	6
87
select /*+ USE_PX parallel(2) */ * from stu;
88
sid	name	cls
89
11	a1	1
90
12	b1	1
91
13	c1	1
92
21	a2	2
93
22	b2	2
94
31	a3	3
95
41	a4	4
96
42	b4	4
97
51	a5	5
98
52	b5	5
99
61	a6	6
100
62	b6	6
101
63	c6	6
102
64	d6	6
103
select /*+ USE_PX parallel(6) */ * from stu;
104
sid	name	cls
105
11	a1	1
106
12	b1	1
107
13	c1	1
108
21	a2	2
109
22	b2	2
110
31	a3	3
111
41	a4	4
112
42	b4	4
113
51	a5	5
114
52	b5	5
115
61	a6	6
116
62	b6	6
117
63	c6	6
118
64	d6	6
119
select /*+ USE_PX parallel(18) */ * from stu;
120
sid	name	cls
121
11	a1	1
122
12	b1	1
123
13	c1	1
124
21	a2	2
125
22	b2	2
126
31	a3	3
127
41	a4	4
128
42	b4	4
129
51	a5	5
130
52	b5	5
131
61	a6	6
132
62	b6	6
133
63	c6	6
134
64	d6	6
135
select /*+ USE_PX parallel(28) */ name, cls from stu;
136
name	cls
137
a1	1
138
a2	2
139
a3	3
140
a4	4
141
a5	5
142
a6	6
143
b1	1
144
b2	2
145
b4	4
146
b5	5
147
b6	6
148
c1	1
149
c6	6
150
d6	6
151
select * from teacher;
152
tid	name	subject
153
1	Miss Zhang	EN
154
2	Mr Wang	MA
155
select /*+ USE_PX parallel(2) */ * from teacher;
156
tid	name	subject
157
1	Miss Zhang	EN
158
2	Mr Wang	MA
159
select /*+ USE_PX parallel(6) */ * from teacher;
160
tid	name	subject
161
1	Miss Zhang	EN
162
2	Mr Wang	MA
163
select /*+ USE_PX parallel(8) */ * from teacher;
164
tid	name	subject
165
1	Miss Zhang	EN
166
2	Mr Wang	MA
167
select /*+ USE_PX parallel(18) */ name from teacher;
168
name
169
Miss Zhang
170
Mr Wang
171
select * from score;
172
sid	subject	score
173
11	EN	60
174
11	MA	60
175
12	EN	70
176
12	MA	70
177
13	EN	80
178
13	MA	80
179
21	EN	58
180
21	MA	58
181
22	EN	90
182
22	MA	90
183
31	EN	80
184
31	MA	80
185
41	EN	80
186
41	MA	80
187
42	EN	90
188
42	MA	90
189
51	EN	89
190
51	MA	89
191
52	EN	99
192
52	MA	99
193
61	EN	100
194
61	MA	100
195
62	EN	90
196
62	MA	90
197
63	EN	99
198
63	MA	99
199
64	EN	87
200
64	MA	87
201
select /*+ USE_PX parallel(2) */ * from score;
202
sid	subject	score
203
11	EN	60
204
11	MA	60
205
12	EN	70
206
12	MA	70
207
13	EN	80
208
13	MA	80
209
21	EN	58
210
21	MA	58
211
22	EN	90
212
22	MA	90
213
31	EN	80
214
31	MA	80
215
41	EN	80
216
41	MA	80
217
42	EN	90
218
42	MA	90
219
51	EN	89
220
51	MA	89
221
52	EN	99
222
52	MA	99
223
61	EN	100
224
61	MA	100
225
62	EN	90
226
62	MA	90
227
63	EN	99
228
63	MA	99
229
64	EN	87
230
64	MA	87
231
select /*+ USE_PX parallel(6) */ * from score;
232
sid	subject	score
233
11	EN	60
234
11	MA	60
235
12	EN	70
236
12	MA	70
237
13	EN	80
238
13	MA	80
239
21	EN	58
240
21	MA	58
241
22	EN	90
242
22	MA	90
243
31	EN	80
244
31	MA	80
245
41	EN	80
246
41	MA	80
247
42	EN	90
248
42	MA	90
249
51	EN	89
250
51	MA	89
251
52	EN	99
252
52	MA	99
253
61	EN	100
254
61	MA	100
255
62	EN	90
256
62	MA	90
257
63	EN	99
258
63	MA	99
259
64	EN	87
260
64	MA	87
261
select /*+ USE_PX parallel(8) */ * from score;
262
sid	subject	score
263
11	EN	60
264
11	MA	60
265
12	EN	70
266
12	MA	70
267
13	EN	80
268
13	MA	80
269
21	EN	58
270
21	MA	58
271
22	EN	90
272
22	MA	90
273
31	EN	80
274
31	MA	80
275
41	EN	80
276
41	MA	80
277
42	EN	90
278
42	MA	90
279
51	EN	89
280
51	MA	89
281
52	EN	99
282
52	MA	99
283
61	EN	100
284
61	MA	100
285
62	EN	90
286
62	MA	90
287
63	EN	99
288
63	MA	99
289
64	EN	87
290
64	MA	87
291
select /*+ USE_PX parallel(18) */ score, sid from score;
292
score	sid
293
100	61
294
100	61
295
58	21
296
58	21
297
60	11
298
60	11
299
70	12
300
70	12
301
80	13
302
80	13
303
80	31
304
80	31
305
80	41
306
80	41
307
87	64
308
87	64
309
89	51
310
89	51
311
90	22
312
90	22
313
90	42
314
90	42
315
90	62
316
90	62
317
99	52
318
99	52
319
99	63
320
99	63
321
select /*+ USE_PX parallel(3) */ * from score where score > 60 and score < 90;
322
sid	subject	score
323
12	EN	70
324
12	MA	70
325
13	EN	80
326
13	MA	80
327
31	EN	80
328
31	MA	80
329
41	EN	80
330
41	MA	80
331
51	EN	89
332
51	MA	89
333
64	EN	87
334
64	MA	87
335
select /*+ USE_PX parallel(3) */ * from score where score > 60 and score < 90 and subject = 'MA';
336
sid	subject	score
337
12	MA	70
338
13	MA	80
339
31	MA	80
340
41	MA	80
341
51	MA	89
342
64	MA	87
343
select /*+ USE_PX parallel(3) */ * from score where score > 60 and score < 90 and subject IN ('MA', 'EN', 'HIS');
344
sid	subject	score
345
12	EN	70
346
12	MA	70
347
13	EN	80
348
13	MA	80
349
31	EN	80
350
31	MA	80
351
41	EN	80
352
41	MA	80
353
51	EN	89
354
51	MA	89
355
64	EN	87
356
64	MA	87
357
select /*+ USE_PX parallel(3) */ * from score where sid >=20 and sid <= 40 and score > 60 and score < 90 and subject IN ('MA', 'EN', 'HIS');
358
sid	subject	score
359
31	EN	80
360
31	MA	80
361
select /*+ USE_PX parallel(3) */ * from score where sid IN (41, 51) and score > 60 and score < 90 and subject IN ('MA', 'EN', 'HIS');
362
sid	subject	score
363
41	EN	80
364
41	MA	80
365
51	EN	89
366
51	MA	89
367
explain select /*+ USE_PX parallel(2) */ * from stu order by name desc;
368
Query Plan
369
==============================================================
370
|ID|OPERATOR                  |NAME    |EST.ROWS|EST.TIME(us)|
371
--------------------------------------------------------------
372
|0 |PX COORDINATOR MERGE SORT |        |14      |26          |
373
|1 |└─EXCHANGE OUT DISTR      |:EX10000|14      |18          |
374
|2 |  └─SORT                  |        |14      |9           |
375
|3 |    └─PX BLOCK ITERATOR   |        |14      |7           |
376
|4 |      └─TABLE FULL SCAN   |stu     |14      |7           |
377
==============================================================
378
Outputs & filters:
379
-------------------------------------
380
  0 - output([INTERNAL_FUNCTION(stu.sid, stu.name, stu.cls)]), filter(nil), rowset=16
381
      sort_keys([stu.name, DESC])
382
  1 - output([stu.name], [INTERNAL_FUNCTION(stu.sid, stu.name, stu.cls)]), filter(nil), rowset=16
383
      dop=2
384
  2 - output([stu.name], [stu.cls], [stu.sid]), filter(nil), rowset=16
385
      sort_keys([stu.name, DESC])
386
  3 - output([stu.cls], [stu.sid], [stu.name]), filter(nil), rowset=16
387
  4 - output([stu.cls], [stu.sid], [stu.name]), filter(nil), rowset=16
388
      access([stu.cls], [stu.sid], [stu.name]), partitions(p[0-5])
389
      is_index_back=false, is_global_index=false, 
390
      range_key([stu.cls], [stu.sid]), range(MIN,MIN ; MAX,MAX)always true
391
select /*+ USE_PX parallel(2) */ * from stu order by name desc;
392
sid	name	cls
393
64	d6	6
394
63	c6	6
395
13	c1	1
396
62	b6	6
397
52	b5	5
398
42	b4	4
399
22	b2	2
400
12	b1	1
401
61	a6	6
402
51	a5	5
403
41	a4	4
404
31	a3	3
405
21	a2	2
406
11	a1	1
407
select /*+ USE_PX parallel(2) */ * from stu order by name;
408
sid	name	cls
409
11	a1	1
410
21	a2	2
411
31	a3	3
412
41	a4	4
413
51	a5	5
414
61	a6	6
415
12	b1	1
416
22	b2	2
417
42	b4	4
418
52	b5	5
419
62	b6	6
420
13	c1	1
421
63	c6	6
422
64	d6	6
423
explain select /*+ USE_PX parallel(2) */ * from stu order by name limit 3;
424
Query Plan
425
================================================================
426
|ID|OPERATOR                    |NAME    |EST.ROWS|EST.TIME(us)|
427
----------------------------------------------------------------
428
|0 |LIMIT                       |        |3       |12          |
429
|1 |└─PX COORDINATOR MERGE SORT |        |3       |12          |
430
|2 |  └─EXCHANGE OUT DISTR      |:EX10000|3       |10          |
431
|3 |    └─TOP-N SORT            |        |3       |8           |
432
|4 |      └─PX BLOCK ITERATOR   |        |14      |7           |
433
|5 |        └─TABLE FULL SCAN   |stu     |14      |7           |
434
================================================================
435
Outputs & filters:
436
-------------------------------------
437
  0 - output([stu.sid], [stu.name], [stu.cls]), filter(nil), rowset=16
438
      limit(3), offset(nil)
439
  1 - output([stu.sid], [stu.name], [stu.cls]), filter(nil), rowset=16
440
      sort_keys([stu.name, ASC])
441
  2 - output([stu.sid], [stu.name], [stu.cls]), filter(nil), rowset=16
442
      dop=2
443
  3 - output([stu.sid], [stu.name], [stu.cls]), filter(nil), rowset=16
444
      sort_keys([stu.name, ASC]), topn(3)
445
  4 - output([stu.cls], [stu.sid], [stu.name]), filter(nil), rowset=16
446
  5 - output([stu.cls], [stu.sid], [stu.name]), filter(nil), rowset=16
447
      access([stu.cls], [stu.sid], [stu.name]), partitions(p[0-5])
448
      is_index_back=false, is_global_index=false, 
449
      range_key([stu.cls], [stu.sid]), range(MIN,MIN ; MAX,MAX)always true
450
select /*+ USE_PX parallel(2) */ * from stu order by name limit 3;
451
sid	name	cls
452
11	a1	1
453
21	a2	2
454
31	a3	3
455
create table t11 (c1 int, c2 int, c3 int, c4 int) partition by hash(c1);
456
select /*+use_px parallel(2) read_consistency(weak) */ t0.c1 from (select c1,c2 from t11 where t11.c1 BETWEEN 1000000000000001 and 1000000000000500) t0 join (select c1,c2 from t11 where t11.c1 BETWEEN 1000000000000001 and 1000000000000500) t1 on t0.c1=t1.c1 join (select c1,c2 from t11 where t11.c1 BETWEEN 1000000000000001 and 1000000000000500) t2 on t0.c1=t2.c1 join (select c1,c2 from t11 where t11.c1 BETWEEN 1000000000000001 and 1000000000000500) t3 on t0.c1=t3.c1 join (select c1,c2 from t11 where t11.c1 BETWEEN 1000000000000001 and 1000000000000500) t4 on t0.c1=t4.c1 join (select c1,c2 from t11 where t11.c1 BETWEEN 1000000000000001 and 1000000000000500) t5 on t0.c1=t5.c1 join (select c1,c2 from t11 where t11.c1 BETWEEN 1000000000000001 and 1000000000000500) t6 on t0.c1=t6.c1 join (select c1,c2 from t11 where t11.c1 BETWEEN 1000000000000001 and 1000000000000500) t7 on t0.c1=t7.c1 join (select c1,c2 from t11 where t11.c1 BETWEEN 1000000000000001 and 1000000000000500) t8 on t0.c1=t8.c1 join (select c1,c2 from t11 where t11.c1 BETWEEN 1000000000000001 and 1000000000000500) t9 on t0.c1=t9.c1 join (select c1,c2 from t11 where t11.c1 BETWEEN 1000000000000001 and 1000000000000500) t10 on t0.c1=t10.c1 join (select c1,c2 from t11 where t11.c1 BETWEEN 1000000000000001 and 1000000000000500) t11 on t0.c1=t11.c1 join (select c1,c2 from t11 where t11.c1 BETWEEN 1000000000000001 and 1000000000000500) t12 on t0.c1=t12.c1 join (select c1,c2 from t11 where t11.c1 BETWEEN 1000000000000001 and 1000000000000500) t13 on t0.c1=t13.c1 join (select c1,c2 from t11 where t11.c1 BETWEEN 1000000000000001 and 1000000000000500) t14 on t0.c1=t14.c1 join (select c1,c2 from t11 where t11.c1 BETWEEN 1000000000000001 and 1000000000000500) t15 on t0.c1=t15.c1 join (select c1,c2 from t11 where t11.c1 BETWEEN 1000000000000001 and 1000000000000500) t16 on t0.c1=t16.c1 join (select c1,c2 from t11 where t11.c1 BETWEEN 1000000000000001 and 1000000000000500) t17 on t0.c1=t17.c1 join (select c1,c2 from t11 where t11.c1 BETWEEN 1000000000000001 and 1000000000000500) t18 on t0.c1=t18.c1 join (select c1,c2 from t11 where t11.c1 BETWEEN 1000000000000001 and 1000000000000500) t19 on t0.c1=t19.c1;
457
c1
458

459
drop table if exists t1;
460
create table t1(c1 int primary key, c2 varchar(10)) partition by hash(c1) partitions 2;
461
insert into t1 values(1, 'aaa'),(2,'bbb');
462
explain basic select * from t1 where c2 not in('', 'xxx');
463
Query Plan
464
=======================================
465
|ID|OPERATOR                 |NAME    |
466
---------------------------------------
467
|0 |PX COORDINATOR           |        |
468
|1 |└─EXCHANGE OUT DISTR     |:EX10000|
469
|2 |  └─PX PARTITION ITERATOR|        |
470
|3 |    └─TABLE FULL SCAN    |t1      |
471
=======================================
472
Outputs & filters:
473
-------------------------------------
474
  0 - output([INTERNAL_FUNCTION(t1.c1, t1.c2)]), filter(nil), rowset=16
475
  1 - output([INTERNAL_FUNCTION(t1.c1, t1.c2)]), filter(nil), rowset=16
476
      dop=1
477
  2 - output([t1.c1], [t1.c2]), filter(nil), rowset=16
478
      force partition granule
479
  3 - output([t1.c1], [t1.c2]), filter([(T_OP_NOT_IN, t1.c2, ('', 'xxx'))]), rowset=16
480
      access([t1.c1], [t1.c2]), partitions(p[0-1])
481
      is_index_back=false, is_global_index=false, filter_before_indexback[false], 
482
      range_key([t1.c1]), range(MIN ; MAX)always true
483
select * from t1 where c2 not in('', 'xxx');
484
c1	c2
485
2	bbb
486
1	aaa
487