blob: 1f5329a53ab7d94c9ad337a45c08b875c3821208 (
plain)
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
|
use test;
--disable_result_log
--disable_query_log
--source suite/parts/inc/part_exch_tabs.inc
--enable_result_log
--enable_query_log
--sorted_result
SELECT * FROM t_10;
--sorted_result
SELECT * FROM t_100;
--sorted_result
SELECT * FROM t_1000;
--sorted_result
SELECT * FROM tp;
--sorted_result
SELECT * FROM tsp;
--sorted_result
SELECT * FROM tsp_00;
--sorted_result
SELECT * FROM tsp_01;
--sorted_result
SELECT * FROM tsp_02;
--sorted_result
SELECT * FROM tsp_03;
--sorted_result
SELECT * FROM tsp_04;
# 1) Valid exchange with partitions.
# exchange of values < 10 of tp to t and complete contents of t to p0 and back.
ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10;
--sorted_result
SELECT * FROM t_10;
--sorted_result
SELECT * FROM tp WHERE a BETWEEN 0 AND 10;
# Back to the former contents.
ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10;
--sorted_result
SELECT * FROM t_10;
--sorted_result
SELECT * FROM tp WHERE a BETWEEN 0 AND 10;
# Exchange with empty table.
ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_empty;
--sorted_result
SELECT * FROM t_empty;
--sorted_result
SELECT * FROM tp WHERE a BETWEEN 0 AND 10;
# Back to the former contents.
ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_empty;
--sorted_result
SELECT * FROM t_empty;
--sorted_result
SELECT * FROM tp WHERE a BETWEEN 0 AND 10;
# Exchange with null table.
ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_null;
--sorted_result
SELECT * FROM t_null;
--sorted_result
SELECT * FROM tp WHERE a BETWEEN 0 AND 10;
# Back to the former contents.
ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_null;
--sorted_result
SELECT * FROM t_null;
--sorted_result
SELECT * FROM tp WHERE a BETWEEN 0 AND 10;
# exchange of values < 100 of tp to t and complete contents of t to p1 and back.
ALTER TABLE tp EXCHANGE PARTITION p1 WITH TABLE t_100;
--sorted_result
SELECT * FROM t_100;
--sorted_result
SELECT * FROM tp WHERE a BETWEEN 10 AND 100;
# Back to the former contents.
ALTER TABLE tp EXCHANGE PARTITION p1 WITH TABLE t_100;
--sorted_result
SELECT * FROM t_100;
--sorted_result
SELECT * FROM tp WHERE a BETWEEN 10 AND 100;
# exchange of values < 1000 of tp to t and complete contents of t to p2 and back.
ALTER TABLE tp EXCHANGE PARTITION p2 WITH TABLE t_1000;
--sorted_result
SELECT * FROM t_1000;
--sorted_result
SELECT * FROM tp WHERE a BETWEEN 100 AND 1000;
# Back to the former contents.
ALTER TABLE tp EXCHANGE PARTITION p2 WITH TABLE t_1000;
--sorted_result
SELECT * FROM t_1000;
--sorted_result
SELECT * FROM tp WHERE a BETWEEN 100 AND 1000;
# 2) Valid exchange of subpartitions.
# exchange of values < 10 of tsp to t and complete contents of t to p0 and back.
ALTER TABLE tsp EXCHANGE PARTITION sp00 WITH TABLE tsp_00;
ALTER TABLE tsp EXCHANGE PARTITION sp01 WITH TABLE tsp_01;
ALTER TABLE tsp EXCHANGE PARTITION sp02 WITH TABLE tsp_02;
ALTER TABLE tsp EXCHANGE PARTITION sp03 WITH TABLE tsp_03;
ALTER TABLE tsp EXCHANGE PARTITION sp04 WITH TABLE tsp_04;
--sorted_result
SELECT * FROM tsp_00;
--sorted_result
SELECT * FROM tsp_01;
--sorted_result
SELECT * FROM tsp_02;
--sorted_result
SELECT * FROM tsp_03;
--sorted_result
SELECT * FROM tsp_04;
--sorted_result
SELECT * FROM tsp WHERE a BETWEEN 0 AND 10;
# Back to the former contents.
ALTER TABLE tsp EXCHANGE PARTITION sp00 WITH TABLE tsp_00;
ALTER TABLE tsp EXCHANGE PARTITION sp01 WITH TABLE tsp_01;
ALTER TABLE tsp EXCHANGE PARTITION sp02 WITH TABLE tsp_02;
ALTER TABLE tsp EXCHANGE PARTITION sp03 WITH TABLE tsp_03;
ALTER TABLE tsp EXCHANGE PARTITION sp04 WITH TABLE tsp_04;
--sorted_result
SELECT * FROM tsp_00;
--sorted_result
SELECT * FROM tsp_01;
--sorted_result
SELECT * FROM tsp_02;
--sorted_result
SELECT * FROM tsp_03;
--sorted_result
SELECT * FROM tsp_04;
--sorted_result
SELECT * FROM tsp WHERE a BETWEEN 0 AND 10;
# Exchange with null table.
ALTER TABLE tsp EXCHANGE PARTITION sp01 WITH TABLE t_null;
--sorted_result
SELECT * FROM t_null;
--sorted_result
SELECT * FROM tsp WHERE a BETWEEN 0 AND 10;
# Back to the former contents.
ALTER TABLE tsp EXCHANGE PARTITION sp01 WITH TABLE t_null;
--sorted_result
SELECT * FROM t_null;
--sorted_result
SELECT * FROM tsp WHERE a BETWEEN 0 AND 10;
--source suite/parts/inc/part_exch_drop_tabs.inc
|
