oceanbase

agg.result
169 строк · 8.8 Кб
Перенос по словам
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
explain select /*+ USE_PX parallel(2) */ subject, avg(score), max(score), min(score) from score group by subject;
72
Query Plan
73
=====================================================================
74
|ID|OPERATOR                         |NAME    |EST.ROWS|EST.TIME(us)|
75
---------------------------------------------------------------------
76
|0 |PX COORDINATOR                   |        |2       |20          |
77
|1 |└─EXCHANGE OUT DISTR             |:EX10001|2       |18          |
78
|2 |  └─HASH GROUP BY                |        |2       |16          |
79
|3 |    └─EXCHANGE IN DISTR          |        |4       |15          |
80
|4 |      └─EXCHANGE OUT DISTR (HASH)|:EX10000|4       |14          |
81
|5 |        └─HASH GROUP BY          |        |4       |10          |
82
|6 |          └─PX BLOCK ITERATOR    |        |28      |8           |
83
|7 |            └─TABLE FULL SCAN    |score   |28      |8           |
84
=====================================================================
85
Outputs & filters:
86
-------------------------------------
87
  0 - output([INTERNAL_FUNCTION(score.subject, cast(cast(T_FUN_SUM(T_FUN_SUM(score.score)), DECIMAL(33, 0)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(score.score)),
88
       DECIMAL(20, 0)), DECIMAL(15, 4)), T_FUN_MAX(T_FUN_MAX(score.score)), T_FUN_MIN(T_FUN_MIN(score.score)))]), filter(nil), rowset=256
89
  1 - output([INTERNAL_FUNCTION(score.subject, cast(cast(T_FUN_SUM(T_FUN_SUM(score.score)), DECIMAL(33, 0)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(score.score)),
90
       DECIMAL(20, 0)), DECIMAL(15, 4)), T_FUN_MAX(T_FUN_MAX(score.score)), T_FUN_MIN(T_FUN_MIN(score.score)))]), filter(nil), rowset=256
91
      dop=2
92
  2 - output([score.subject], [T_FUN_MAX(T_FUN_MAX(score.score))], [T_FUN_MIN(T_FUN_MIN(score.score))], [T_FUN_SUM(T_FUN_SUM(score.score))], [T_FUN_COUNT_SUM(T_FUN_COUNT(score.score))]), filter(nil), rowset=256
93
      group([score.subject]), agg_func([T_FUN_MAX(T_FUN_MAX(score.score))], [T_FUN_MIN(T_FUN_MIN(score.score))], [T_FUN_SUM(T_FUN_SUM(score.score))], [T_FUN_COUNT_SUM(T_FUN_COUNT(score.score))])
94
  3 - output([score.subject], [T_FUN_MAX(score.score)], [T_FUN_MIN(score.score)], [T_FUN_SUM(score.score)], [T_FUN_COUNT(score.score)]), filter(nil), rowset=256
95
  4 - output([score.subject], [T_FUN_MAX(score.score)], [T_FUN_MIN(score.score)], [T_FUN_SUM(score.score)], [T_FUN_COUNT(score.score)]), filter(nil), rowset=256
96
      (#keys=1, [score.subject]), dop=2
97
  5 - output([score.subject], [T_FUN_MAX(score.score)], [T_FUN_MIN(score.score)], [T_FUN_SUM(score.score)], [T_FUN_COUNT(score.score)]), filter(nil), rowset=256
98
      group([score.subject]), agg_func([T_FUN_MAX(score.score)], [T_FUN_MIN(score.score)], [T_FUN_SUM(score.score)], [T_FUN_COUNT(score.score)])
99
  6 - output([score.subject], [score.score]), filter(nil), rowset=256
100
  7 - output([score.subject], [score.score]), filter(nil), rowset=256
101
      access([score.subject], [score.score]), partitions(p[0-5])
102
      is_index_back=false, is_global_index=false, 
103
      range_key([score.sid], [score.subject]), range(MIN,MIN ; MAX,MAX)always true
104
select /*+ USE_PX parallel(2) */ subject, avg(score), max(score), min(score) from score group by subject;
105
subject	avg(score)	max(score)	min(score)
106
EN	83.7143	100	58
107
MA	83.7143	100	58
108
explain select /*+ USE_PX parallel(2) */ cls, subject, avg(score), max(score), min(score) from score join stu using (sid) group by cls, subject;
109
Query Plan
110
=====================================================================
111
|ID|OPERATOR                         |NAME    |EST.ROWS|EST.TIME(us)|
112
---------------------------------------------------------------------
113
|0 |PX COORDINATOR                   |        |9       |66          |
114
|1 |└─EXCHANGE OUT DISTR             |:EX10001|9       |57          |
115
|2 |  └─HASH GROUP BY                |        |9       |48          |
116
|3 |    └─EXCHANGE IN DISTR          |        |16      |45          |
117
|4 |      └─EXCHANGE OUT DISTR (HASH)|:EX10000|16      |37          |
118
|5 |        └─HASH GROUP BY          |        |16      |20          |
119
|6 |          └─PX PARTITION ITERATOR|        |28      |16          |
120
|7 |            └─MERGE JOIN         |        |28      |16          |
121
|8 |              ├─TABLE FULL SCAN  |score   |28      |8           |
122
|9 |              └─SORT             |        |14      |8           |
123
|10|                └─TABLE FULL SCAN|stu     |14      |7           |
124
=====================================================================
125
Outputs & filters:
126
-------------------------------------
127
  0 - output([INTERNAL_FUNCTION(stu.cls, score.subject, cast(cast(T_FUN_SUM(T_FUN_SUM(score.score)), DECIMAL(33, 0)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(score.score)),
128
       DECIMAL(20, 0)), DECIMAL(15, 4)), T_FUN_MAX(T_FUN_MAX(score.score)), T_FUN_MIN(T_FUN_MIN(score.score)))]), filter(nil), rowset=256
129
  1 - output([INTERNAL_FUNCTION(stu.cls, score.subject, cast(cast(T_FUN_SUM(T_FUN_SUM(score.score)), DECIMAL(33, 0)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(score.score)),
130
       DECIMAL(20, 0)), DECIMAL(15, 4)), T_FUN_MAX(T_FUN_MAX(score.score)), T_FUN_MIN(T_FUN_MIN(score.score)))]), filter(nil), rowset=256
131
      dop=2
132
  2 - output([stu.cls], [score.subject], [T_FUN_MAX(T_FUN_MAX(score.score))], [T_FUN_MIN(T_FUN_MIN(score.score))], [T_FUN_SUM(T_FUN_SUM(score.score))], 
133
      [T_FUN_COUNT_SUM(T_FUN_COUNT(score.score))]), filter(nil), rowset=256
134
      group([stu.cls], [score.subject]), agg_func([T_FUN_MAX(T_FUN_MAX(score.score))], [T_FUN_MIN(T_FUN_MIN(score.score))], [T_FUN_SUM(T_FUN_SUM(score.score))],
135
       [T_FUN_COUNT_SUM(T_FUN_COUNT(score.score))])
136
  3 - output([stu.cls], [score.subject], [T_FUN_MAX(score.score)], [T_FUN_MIN(score.score)], [T_FUN_SUM(score.score)], [T_FUN_COUNT(score.score)]), filter(nil), rowset=256
137
  4 - output([stu.cls], [score.subject], [T_FUN_MAX(score.score)], [T_FUN_MIN(score.score)], [T_FUN_SUM(score.score)], [T_FUN_COUNT(score.score)]), filter(nil), rowset=256
138
      (#keys=2, [stu.cls], [score.subject]), dop=2
139
  5 - output([stu.cls], [score.subject], [T_FUN_MAX(score.score)], [T_FUN_MIN(score.score)], [T_FUN_SUM(score.score)], [T_FUN_COUNT(score.score)]), filter(nil), rowset=256
140
      group([stu.cls], [score.subject]), agg_func([T_FUN_MAX(score.score)], [T_FUN_MIN(score.score)], [T_FUN_SUM(score.score)], [T_FUN_COUNT(score.score)])
141
  6 - output([score.score], [stu.cls], [score.subject]), filter(nil), rowset=256
142
      partition wise, force partition granule
143
  7 - output([score.score], [stu.cls], [score.subject]), filter(nil), rowset=256
144
      equal_conds([score.sid = stu.sid]), other_conds(nil)
145
      merge_directions([ASC])
146
  8 - output([score.sid], [score.subject], [score.score]), filter(nil), rowset=256
147
      access([score.sid], [score.subject], [score.score]), partitions(p[0-5])
148
      is_index_back=false, is_global_index=false, 
149
      range_key([score.sid], [score.subject]), range(MIN,MIN ; MAX,MAX)always true
150
  9 - output([stu.cls], [stu.sid]), filter(nil), rowset=256
151
      sort_keys([stu.sid, ASC])
152
 10 - output([stu.cls], [stu.sid]), filter(nil), rowset=256
153
      access([stu.cls], [stu.sid]), partitions(p[0-5])
154
      is_index_back=false, is_global_index=false, 
155
      range_key([stu.cls], [stu.sid]), range(MIN,MIN ; MAX,MAX)always true
156
select /*+ USE_PX parallel(2) */ cls, subject, avg(score), max(score), min(score) from score join stu using (sid) group by cls, subject;
157
cls	subject	avg(score)	max(score)	min(score)
158
1	EN	70.0000	80	60
159
1	MA	70.0000	80	60
160
2	EN	74.0000	90	58
161
2	MA	74.0000	90	58
162
3	EN	80.0000	80	80
163
3	MA	80.0000	80	80
164
4	EN	85.0000	90	80
165
4	MA	85.0000	90	80
166
5	EN	94.0000	99	89
167
5	MA	94.0000	99	89
168
6	EN	94.0000	100	87
169
6	MA	94.0000	100	87
170
oceanbase

Использование cookies
