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