MySQL 5.6.14 Source Code Document
 All Classes Namespaces Files Functions Variables Typedefs Enumerations Enumerator Friends Macros Groups Pages
partition_methods1.inc
1 ################################################################################
2 # inc/partition_methods1.inc #
3 # #
4 # Purpose: #
5 # Create and check partitioned tables #
6 # The partitioning function use the column f_int1 #
7 # #
8 # For all partitioning methods #
9 # PARTITION BY HASH/KEY/LIST/RANGE #
10 # PARTITION BY RANGE/LIST ... SUBPARTITION BY HASH/KEY ... #
11 # do #
12 # 1. Create the partitioned table #
13 # 2 Insert the content of the table t0_template into t1 #
14 # 3. Execute inc/partition_check.inc #
15 # 4. Drop the table t1 #
16 # done #
17 # #
18 # The parameter #
19 # $unique -- PRIMARY KEY or UNIQUE INDEXes to be created within the #
20 # CREATE TABLE STATEMENT #
21 # has to be set before sourcing this routine. #
22 # Example: #
23 # let $unique= , UNIQUE INDEX uidx1 (f_int1); #
24 # inc/partition_method1s.inc #
25 # #
26 # Attention: The routine inc/partition_methods2.inc is very similar #
27 # to this one. So if something has to be changed here it #
28 # might be necessary to do it also there #
29 # #
30 #------------------------------------------------------------------------------#
31 # Original Author: mleich #
32 # Original Date: 2006-03-05 #
33 # Change Author: HH #
34 # Change Date: 2006-05-12 #
35 # Change: Introduced DATA/INDEX DIRECTORY #
36 ################################################################################
37 
38 --disable_warnings
39 DROP TABLE IF EXISTS t1;
40 --enable_warnings
41 
42 let $partitioning= ;
43 #----------- PARTITION BY HASH
44 if ($with_partitioning)
45 {
46 let $partitioning= PARTITION BY HASH(f_int1) PARTITIONS 2;
47 if ($with_directories)
48 {
49 let $partitioning=
50 PARTITION BY HASH(f_int1) PARTITIONS 2
51 (PARTITION p1
52 $data_directory
53 $index_directory,
54 PARTITION p2
55 $data_directory
56 $index_directory);
57 }
58 }
59 --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
60 eval CREATE TABLE t1 (
61 $column_list
62 $unique
63 )
64 $partitioning;
65 eval $insert_all;
66 --source suite/parts/inc/partition_check.inc
67 DROP TABLE t1;
68 --source suite/parts/inc/partition_check_drop.inc
69 
70 #----------- PARTITION BY KEY
71 if ($with_partitioning)
72 {
73 let $partitioning=
74 PARTITION BY KEY(f_int1) PARTITIONS 5;
75 if ($with_directories)
76 {
77 let $partitioning=
78 PARTITION BY KEY(f_int1) PARTITIONS 5
79 (PARTITION p1
80 $data_directory
81 $index_directory,
82 PARTITION p2
83 $data_directory
84 $index_directory,
85 PARTITION p3
86 $data_directory
87 $index_directory,
88 PARTITION p4
89 $data_directory
90 $index_directory,
91 PARTITION p5
92 $data_directory
93 $index_directory);
94 }
95 }
96 --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
97 eval CREATE TABLE t1 (
98 $column_list
99 $unique
100 )
101 $partitioning;
102 eval $insert_all;
103 --source suite/parts/inc/partition_check.inc
104 DROP TABLE t1;
105 --source suite/parts/inc/partition_check_drop.inc
106 
107 #----------- PARTITION BY LIST
108 if ($with_partitioning)
109 {
110 let $partitioning=
111 PARTITION BY LIST(MOD(f_int1,4))
112 (PARTITION part_3 VALUES IN (-3),
113  PARTITION part_2 VALUES IN (-2),
114  PARTITION part_1 VALUES IN (-1),
115  PARTITION part_N VALUES IN (NULL),
116  PARTITION part0 VALUES IN (0),
117  PARTITION part1 VALUES IN (1),
118  PARTITION part2 VALUES IN (2),
119  PARTITION part3 VALUES IN (3));
120 if ($with_directories)
121 {
122 let $partitioning=
123 PARTITION BY LIST(MOD(f_int1,4))
124 (PARTITION part_3 VALUES IN (-3)
125 $data_directory $index_directory,
126  PARTITION part_2 VALUES IN (-2)
127 $data_directory $index_directory,
128  PARTITION part_1 VALUES IN (-1)
129 $data_directory $index_directory,
130  PARTITION part_N VALUES IN (NULL)
131 $data_directory $index_directory,
132  PARTITION part0 VALUES IN (0)
133 $data_directory $index_directory,
134  PARTITION part1 VALUES IN (1)
135 $data_directory $index_directory,
136  PARTITION part2 VALUES IN (2)
137 $data_directory $index_directory,
138  PARTITION part3 VALUES IN (3)
139 $data_directory $index_directory);
140 }
141 }
142 --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
143 eval CREATE TABLE t1 (
144 $column_list
145 $unique
146 )
147 $partitioning;
148 eval $insert_all;
149 --source suite/parts/inc/partition_check.inc
150 DROP TABLE t1;
151 --source suite/parts/inc/partition_check_drop.inc
152 
153 #----------- PARTITION BY RANGE
154 if ($with_partitioning)
155 {
156 let $partitioning= PARTITION BY RANGE(f_int1)
157 (PARTITION parta VALUES LESS THAN (0),
158 PARTITION partb VALUES LESS THAN ($max_row_div4),
159 PARTITION partc VALUES LESS THAN ($max_row_div2),
160 PARTITION partd VALUES LESS THAN ($max_row_div2 + $max_row_div4),
161 PARTITION parte VALUES LESS THAN ($max_row),
162 PARTITION partf VALUES LESS THAN $MAX_VALUE);
163 if ($with_directories)
164 {
165 let $partitioning= PARTITION BY RANGE(f_int1)
166 (PARTITION parta VALUES LESS THAN (0)
167 $data_directory
168 $index_directory,
169 PARTITION partb VALUES LESS THAN ($max_row_div4)
170 $data_directory
171 $index_directory,
172 PARTITION partc VALUES LESS THAN ($max_row_div2)
173 $data_directory
174 $index_directory,
175 PARTITION partd VALUES LESS THAN ($max_row_div2 + $max_row_div4)
176 $data_directory
177 $index_directory,
178 PARTITION parte VALUES LESS THAN ($max_row)
179 $data_directory
180 $index_directory,
181 PARTITION partf VALUES LESS THAN $MAX_VALUE
182 $data_directory
183 $index_directory);
184 }
185 }
186 --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
187 eval CREATE TABLE t1 (
188 $column_list
189 $unique
190 )
191 $partitioning;
192 eval $insert_all;
193 --source suite/parts/inc/partition_check.inc
194 DROP TABLE t1;
195 --source suite/parts/inc/partition_check_drop.inc
196 
197 #----------- PARTITION BY RANGE -- SUBPARTITION BY HASH
198 if ($with_partitioning)
199 {
200 let $partitioning=
201 PARTITION BY RANGE(f_int1 DIV 2) SUBPARTITION BY HASH(f_int1) SUBPARTITIONS 2
202 (PARTITION parta VALUES LESS THAN (0),
203 PARTITION partb VALUES LESS THAN ($max_row_div4),
204 PARTITION partc VALUES LESS THAN ($max_row_div2),
205 PARTITION partd VALUES LESS THAN $MAX_VALUE);
206 if ($with_directories)
207 {
208 let $partitioning=
209 PARTITION BY RANGE(f_int1 DIV 2) SUBPARTITION BY HASH(f_int1) SUBPARTITIONS 2
210 (PARTITION parta VALUES LESS THAN (0)
211 $data_directory
212 $index_directory,
213 PARTITION partb VALUES LESS THAN ($max_row_div4)
214 $data_directory
215 $index_directory,
216 PARTITION partc VALUES LESS THAN ($max_row_div2)
217 $data_directory
218 $index_directory,
219 PARTITION partd VALUES LESS THAN $MAX_VALUE
220 $data_directory
221 $index_directory);
222 }
223 }
224 --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
225 eval CREATE TABLE t1 (
226 $column_list
227 $unique
228 )
229 $partitioning;
230 eval $insert_all;
231 --source suite/parts/inc/partition_check.inc
232 DROP TABLE t1;
233 --source suite/parts/inc/partition_check_drop.inc
234 
235 #----------- PARTITION BY RANGE -- SUBPARTITION BY KEY
236 if ($with_partitioning)
237 {
238 let $partitioning= PARTITION BY RANGE(f_int1) SUBPARTITION BY KEY(f_int1)
239 (PARTITION part1 VALUES LESS THAN (0)
240 (SUBPARTITION subpart11, SUBPARTITION subpart12),
241 PARTITION part2 VALUES LESS THAN ($max_row_div4)
242 (SUBPARTITION subpart21, SUBPARTITION subpart22),
243 PARTITION part3 VALUES LESS THAN ($max_row_div2)
244 (SUBPARTITION subpart31, SUBPARTITION subpart32),
245 PARTITION part4 VALUES LESS THAN $MAX_VALUE
246 (SUBPARTITION subpart41, SUBPARTITION subpart42));
247 if ($with_directories)
248 {
249 let $partitioning= PARTITION BY RANGE(f_int1) SUBPARTITION BY KEY(f_int1)
250 (PARTITION part1 VALUES LESS THAN (0)
251 (SUBPARTITION subpart11 $data_directory $index_directory,
252  SUBPARTITION subpart12 $data_directory $index_directory),
253 PARTITION part2 VALUES LESS THAN ($max_row_div4)
254 (SUBPARTITION subpart21 $data_directory $index_directory,
255  SUBPARTITION subpart22 $data_directory $index_directory),
256 PARTITION part3 VALUES LESS THAN ($max_row_div2)
257 (SUBPARTITION subpart31 $data_directory $index_directory,
258  SUBPARTITION subpart32 $data_directory $index_directory),
259 PARTITION part4 VALUES LESS THAN $MAX_VALUE
260 (SUBPARTITION subpart41 $data_directory $index_directory,
261  SUBPARTITION subpart42 $data_directory $index_directory));
262 }
263 }
264 --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
265 eval CREATE TABLE t1 (
266 $column_list
267 $unique
268 )
269 $partitioning;
270 eval $insert_all;
271 --source suite/parts/inc/partition_check.inc
272 DROP TABLE t1;
273 --source suite/parts/inc/partition_check_drop.inc
274 
275 #----------- PARTITION BY LIST -- SUBPARTITION BY HASH
276 if ($with_partitioning)
277 {
278 let $partitioning=
279 PARTITION BY LIST(ABS(MOD(f_int1,3))) SUBPARTITION BY HASH(f_int1 + 1)
280 (PARTITION part1 VALUES IN (0)
281  (SUBPARTITION sp11,
282  SUBPARTITION sp12),
283  PARTITION part2 VALUES IN (1)
284  (SUBPARTITION sp21,
285  SUBPARTITION sp22),
286  PARTITION part3 VALUES IN (2)
287  (SUBPARTITION sp31,
288  SUBPARTITION sp32),
289  PARTITION part4 VALUES IN (NULL)
290  (SUBPARTITION sp41,
291  SUBPARTITION sp42));
292 if ($with_directories)
293 {
294 let $partitioning=
295 PARTITION BY LIST(ABS(MOD(f_int1,3))) SUBPARTITION BY HASH(f_int1 + 1)
296 (PARTITION part1 VALUES IN (0)
297  $data_directory
298  $index_directory
299  (SUBPARTITION sp11
300  $data_directory
301  $index_directory,
302  SUBPARTITION sp12
303  $data_directory
304  $index_directory),
305  PARTITION part2 VALUES IN (1)
306  $data_directory
307  $index_directory
308  (SUBPARTITION sp21
309  $data_directory
310  $index_directory,
311  SUBPARTITION sp22
312  $data_directory
313  $index_directory),
314  PARTITION part3 VALUES IN (2)
315  $data_directory
316  $index_directory
317  (SUBPARTITION sp31,
318  SUBPARTITION sp32),
319  PARTITION part4 VALUES IN (NULL)
320  $data_directory
321  $index_directory
322  (SUBPARTITION sp41
323  $data_directory
324  $index_directory,
325  SUBPARTITION sp42
326  $data_directory
327  $index_directory));
328 }
329 }
330 --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
331 eval CREATE TABLE t1 (
332 $column_list
333 $unique
334 )
335 $partitioning;
336 eval $insert_all;
337 --source suite/parts/inc/partition_check.inc
338 DROP TABLE t1;
339 --source suite/parts/inc/partition_check_drop.inc
340 
341 #----------- PARTITION BY LIST -- SUBPARTITION BY KEY
342 if ($with_partitioning)
343 {
344 let $partitioning=
345 PARTITION BY LIST(ABS(MOD(f_int1,2)))
346 SUBPARTITION BY KEY(f_int1) SUBPARTITIONS $sub_part_no
347 (PARTITION part1 VALUES IN (0),
348  PARTITION part2 VALUES IN (1),
349  PARTITION part3 VALUES IN (NULL));
350 if ($with_directories)
351 {
352 let $partitioning=
353 PARTITION BY LIST(ABS(MOD(f_int1,2)))
354 SUBPARTITION BY KEY(f_int1) SUBPARTITIONS $sub_part_no
355 (PARTITION part1 VALUES IN (0)
356  $data_directory
357  $index_directory,
358  PARTITION part2 VALUES IN (1)
359  $data_directory
360  $index_directory,
361  PARTITION part3 VALUES IN (NULL)
362  $data_directory
363  $index_directory);
364 }
365 }
366 --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
367 eval CREATE TABLE t1 (
368 $column_list
369 $unique
370 )
371 $partitioning;
372 eval $insert_all;
373 --source suite/parts/inc/partition_check.inc
374 DROP TABLE t1;
375 --source suite/parts/inc/partition_check_drop.inc