1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
|
set @@global.connect_exact_info=ON;
# This will be used to see what data files are created
CREATE TABLE dr1 (
fname VARCHAR(256) NOT NULL FLAG=2,
ftype CHAR(8) NOT NULL FLAG=3
# ,FSIZE INT(6) NOT NULL FLAG=5 removed because Unix size != Windows size
) engine=CONNECT table_type=DIR file_name='t1#P#*.*';
#
# Testing partitioning on inward table
#
CREATE TABLE t1 (
id INT NOT NULL,
msg VARCHAR(32)
) ENGINE=CONNECT TABLE_TYPE=CSV AVG_ROW_LENGTH=10
PARTITION BY RANGE(id) (
PARTITION first VALUES LESS THAN(10),
PARTITION middle VALUES LESS THAN(50),
PARTITION last VALUES LESS THAN(MAXVALUE));
INSERT INTO t1 VALUES(4, 'four'),(24, 'twenty four');
INSERT INTO t1 VALUES(7,'seven'),(10,'ten'),(40,'forty'),(60,'sixty'),(81,'eighty one');
SELECT partition_name, table_rows FROM information_schema.partitions WHERE table_name = 't1';
partition_name table_rows
first 2
middle 3
last 2
SELECT * FROM t1;
id msg
4 four
7 seven
24 twenty four
10 ten
40 forty
60 sixty
81 eighty one
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE id > 50;
id select_type table partitions type possible_keys key key_len ref rows Extra
1 SIMPLE t1 last ALL NULL NULL NULL NULL 3 Using where
SELECT * FROM t1 WHERE id > 50;
id msg
60 sixty
81 eighty one
UPDATE t1 set id = 41 WHERE msg = 'four';
ERROR HY000: Got error 174 'Cannot update column id because it is used for partitioning' from CONNECT
UPDATE t1 set msg = 'quatre' WHERE id = 4;
SELECT * FROM dr1 ORDER BY fname, ftype;
fname ftype
t1#P#first .csv
t1#P#last .csv
t1#P#middle .csv
#
# Altering partitioning on inward table
#
ALTER TABLE t1
PARTITION by range(id) (
PARTITION first VALUES LESS THAN(11),
PARTITION middle VALUES LESS THAN(50),
PARTITION last VALUES LESS THAN(MAXVALUE));
SELECT partition_name, table_rows FROM information_schema.partitions WHERE table_name = 't1';
partition_name table_rows
first 3
middle 2
last 2
SELECT * FROM dr1 ORDER BY fname, ftype;
fname ftype
t1#P#first .csv
t1#P#last .csv
t1#P#middle .csv
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE id=10;
id select_type table partitions type possible_keys key key_len ref rows Extra
1 SIMPLE t1 first ALL NULL NULL NULL NULL 3 Using where
SELECT * FROM t1 WHERE id=10;
id msg
10 ten
DELETE FROM t1 WHERE id in (4,60);
SELECT * FROM t1;
id msg
7 seven
10 ten
24 twenty four
40 forty
81 eighty one
DROP TABLE t1;
#
# Testing partitioning on a void outward table
#
ALTER TABLE dr1 FILE_NAME='part*.*';
CREATE TABLE t1 (
rwid INT(6) DEFAULT 0 SPECIAL=ROWID,
rnum INT(6) DEFAULT 0 SPECIAL=ROWNUM,
prtn VARCHAR(64) DEFAULT '' SPECIAL=PARTID,
tbn VARCHAR(64) DEFAULT '' SPECIAL=TABID,
fid VARCHAR(256) DEFAULT '' SPECIAL=FNAME,
id INT KEY NOT NULL,
msg VARCHAR(32)
) ENGINE=CONNECT TABLE_TYPE=FIX FILE_NAME='part%s.txt';
ALTER TABLE t1
PARTITION by range columns(id) (
PARTITION `1` VALUES LESS THAN(10),
PARTITION `2` VALUES LESS THAN(50),
PARTITION `3` VALUES LESS THAN(MAXVALUE));
SHOW INDEX FROM t1;
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
t1 0 PRIMARY 1 id A NULL NULL NULL XINDEX
INSERT INTO t1(id,msg) VALUES(4, 'four');
SELECT * FROM dr1 ORDER BY fname, ftype;
fname ftype
part1 .fnx
part1 .txt
INSERT INTO t1(id,msg) VALUES(7,'seven'),(10,'ten'),(40,'forty'),(60,'sixty'),(81,'eighty one');
INSERT INTO t1(id,msg) VALUES(72,'seventy two'),(20,'twenty'),(1,'one'),(35,'thirty five'),(8,'eight');
SELECT partition_name, table_rows FROM information_schema.partitions WHERE table_name = 't1';
partition_name table_rows
1 4
2 4
3 3
SELECT * FROM t1;
rwid rnum prtn tbn fid id msg
1 1 1 t1 part1 4 four
2 2 1 t1 part1 7 seven
3 3 1 t1 part1 1 one
4 4 1 t1 part1 8 eight
1 1 2 t1 part2 10 ten
2 2 2 t1 part2 40 forty
3 3 2 t1 part2 20 twenty
4 4 2 t1 part2 35 thirty five
1 1 3 t1 part3 60 sixty
2 2 3 t1 part3 81 eighty one
3 3 3 t1 part3 72 seventy two
SELECT * FROM t1 order by id;
rwid rnum prtn tbn fid id msg
3 3 1 t1 part1 1 one
1 1 1 t1 part1 4 four
2 2 1 t1 part1 7 seven
4 4 1 t1 part1 8 eight
1 1 2 t1 part2 10 ten
3 3 2 t1 part2 20 twenty
4 4 2 t1 part2 35 thirty five
2 2 2 t1 part2 40 forty
1 1 3 t1 part3 60 sixty
3 3 3 t1 part3 72 seventy two
2 2 3 t1 part3 81 eighty one
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE id = 10;
id select_type table partitions type possible_keys key key_len ref rows Extra
1 SIMPLE t1 2 const PRIMARY PRIMARY 4 const 1
SELECT * FROM t1 WHERE id = 10;
rwid rnum prtn tbn fid id msg
1 1 2 t1 part2 10 ten
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE id >= 40;
id select_type table partitions type possible_keys key key_len ref rows Extra
1 SIMPLE t1 2,3 range PRIMARY PRIMARY 4 NULL 4 Using where
SELECT * FROM t1 WHERE id >= 40;
rwid rnum prtn tbn fid id msg
2 2 2 t1 part2 40 forty
1 1 3 t1 part3 60 sixty
3 3 3 t1 part3 72 seventy two
2 2 3 t1 part3 81 eighty one
SELECT count(*) FROM t1 WHERE id < 10;
count(*)
4
SELECT case when id < 10 then 1 when id < 50 then 2 else 3 end as pn, count(*) FROM t1 group by pn;
pn count(*)
1 4
2 4
3 3
SELECT prtn, count(*) FROM t1 group by prtn;
prtn count(*)
1 4
2 4
3 3
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE id > 50;
id select_type table partitions type possible_keys key key_len ref rows Extra
1 SIMPLE t1 3 range PRIMARY PRIMARY 4 NULL 3 Using where
SELECT * FROM t1 WHERE id = 35;
rwid rnum prtn tbn fid id msg
4 4 2 t1 part2 35 thirty five
SELECT * FROM dr1 ORDER BY fname, ftype;
fname ftype
part1 .fnx
part1 .txt
part2 .fnx
part2 .txt
part3 .fnx
part3 .txt
# This does not change the partition file data and is WRONG
ALTER TABLE t1
PARTITION by range columns(id) (
PARTITION `1` VALUES LESS THAN(11),
PARTITION `2` VALUES LESS THAN(70),
PARTITION `3` VALUES LESS THAN(MAXVALUE));
Warnings:
Warning 1105 Data repartition in 1 is unchecked
Warning 1105 Data repartition in 2 is unchecked
Warning 1105 Data repartition in 3 is unchecked
SELECT CASE WHEN id < 11 THEN 1 WHEN id < 70 THEN 2 ELSE 3 END AS pn, COUNT(*) FROM t1 GROUP BY pn;
pn COUNT(*)
1 5
2 4
3 2
SELECT partition_name, table_rows FROM information_schema.partitions WHERE table_name = 't1';
partition_name table_rows
1 4
2 4
3 3
SELECT * FROM dr1 ORDER BY fname, ftype;
fname ftype
part1 .fnx
part1 .txt
part2 .fnx
part2 .txt
part3 .fnx
part3 .txt
#
# This is the correct way to change partitioning:
# Save table values, erase the table, then re-insert saved values in modified table
#
CREATE TABLE t2 (
id INT NOT NULL,
msg VARCHAR(32)
) ENGINE=CONNECT TABLE_TYPE=FIX;
Warnings:
Warning 1105 No file name. Table will use t2.fix
INSERT INTO t2 SELECT id, msg FROM t1;
DELETE FROM t1;
INSERT INTO t1(id,msg) SELECT * FROM t2;
SELECT partition_name, table_rows FROM information_schema.partitions WHERE table_name = 't1';
partition_name table_rows
1 5
2 4
3 2
SELECT * FROM t1;
rwid rnum prtn tbn fid id msg
1 1 1 t1 part1 4 four
2 2 1 t1 part1 7 seven
3 3 1 t1 part1 1 one
4 4 1 t1 part1 8 eight
5 5 1 t1 part1 10 ten
1 1 2 t1 part2 40 forty
2 2 2 t1 part2 20 twenty
3 3 2 t1 part2 35 thirty five
4 4 2 t1 part2 60 sixty
1 1 3 t1 part3 81 eighty one
2 2 3 t1 part3 72 seventy two
SELECT * FROM dr1 ORDER BY fname, ftype;
fname ftype
part1 .fnx
part1 .txt
part2 .fnx
part2 .txt
part3 .fnx
part3 .txt
DROP TABLE t2;
DROP TABLE t1;
#
# Testing partitioning on a populated outward table
#
CREATE TABLE t1 (
id INT NOT NULL,
msg VARCHAR(32)
) ENGINE=CONNECT TABLE_TYPE=FIX FILE_NAME='part%s.txt'
PARTITION by range columns(id) (
PARTITION `1` VALUES LESS THAN(11),
PARTITION `2` VALUES LESS THAN(70),
PARTITION `3` VALUES LESS THAN(MAXVALUE));
Warnings:
Warning 1105 Data repartition in 1 is unchecked
Warning 1105 Data repartition in 2 is unchecked
Warning 1105 Data repartition in 3 is unchecked
SELECT partition_name, table_rows FROM information_schema.partitions WHERE table_name = 't1';
partition_name table_rows
1 5
2 4
3 2
SELECT * FROM t1 WHERE id < 11;
id msg
4 four
7 seven
1 one
8 eight
10 ten
SELECT * FROM t1 WHERE id >= 70;
id msg
81 eighty one
72 seventy two
SELECT * FROM dr1 ORDER BY fname, ftype;
fname ftype
part1 .fnx
part1 .txt
part2 .fnx
part2 .txt
part3 .fnx
part3 .txt
#
# Testing indexing on a partitioned table
#
CREATE INDEX XID ON t1(id);
SHOW INDEX FROM t1;
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
t1 1 XID 1 id A NULL NULL NULL XINDEX
SELECT * FROM dr1 ORDER BY fname, ftype;
fname ftype
part1 .fnx
part1 .txt
part2 .fnx
part2 .txt
part3 .fnx
part3 .txt
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE id = 10;
id select_type table partitions type possible_keys key key_len ref rows Extra
1 SIMPLE t1 1 ref XID XID 4 const 1
DROP INDEX XID ON t1;
SHOW INDEX FROM t1;
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
SELECT * FROM dr1 ORDER BY fname, ftype;
fname ftype
part1 .txt
part2 .txt
part3 .txt
ALTER TABLE t1 ADD PRIMARY KEY (id);
SHOW INDEX FROM t1;
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
t1 0 PRIMARY 1 id A NULL NULL NULL XINDEX
SELECT * FROM dr1 ORDER BY fname, ftype;
fname ftype
part1 .fnx
part1 .txt
part2 .fnx
part2 .txt
part3 .fnx
part3 .txt
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE id = 10;
id select_type table partitions type possible_keys key key_len ref rows Extra
1 SIMPLE t1 1 const PRIMARY PRIMARY 4 const 1
ALTER TABLE t1 DROP PRIMARY KEY;
SHOW INDEX FROM t1;
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
SELECT * FROM dr1 ORDER BY fname, ftype;
fname ftype
part1 .txt
part2 .txt
part3 .txt
DROP TABLE t1;
DROP TABLE dr1;
set @@global.connect_exact_info=OFF;
|
