DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS VAR_POP;
DROP TABLE IF EXISTS TEMP0;
DROP TABLE IF EXISTS VAR_SAMP;
DROP TABLE IF EXISTS ti;
DROP TABLE IF EXISTS members;
DROP TABLE IF EXISTS members_2;
DROP TABLE IF EXISTS employees;
DROP TABLE IF EXISTS employees_2;
DROP TABLE IF EXISTS employees_3;
DROP TABLE IF EXISTS quarterly_report_status;
DROP TABLE IF EXISTS employees_4;
DROP TABLE IF EXISTS h2;
DROP TABLE IF EXISTS rcx;
DROP TABLE IF EXISTS r1;
DROP TABLE IF EXISTS rc1;
DROP TABLE IF EXISTS rx;
DROP TABLE IF EXISTS rc2;
DROP TABLE IF EXISTS rc3;
DROP TABLE IF EXISTS rc4;
DROP TABLE IF EXISTS employees_by_lname;
DROP TABLE IF EXISTS customers_1;
DROP TABLE IF EXISTS customers_2;
DROP TABLE IF EXISTS customers_3;
DROP TABLE IF EXISTS employees_hash;
DROP TABLE IF EXISTS employees_hash_1;
DROP TABLE IF EXISTS t1_hash;
DROP TABLE IF EXISTS employees_linear_hash;
DROP TABLE IF EXISTS t1_linear_hash;
DROP TABLE IF EXISTS k1;
DROP TABLE IF EXISTS k2;
DROP TABLE IF EXISTS tm1;
DROP TABLE IF EXISTS tk;
DROP TABLE IF EXISTS ts;
DROP TABLE IF EXISTS ts_1;
DROP TABLE IF EXISTS ts_3;
DROP TABLE IF EXISTS ts_4;
DROP TABLE IF EXISTS ts_5;
DROP TABLE IF EXISTS trb3;
DROP TABLE IF EXISTS tr;
DROP TABLE IF EXISTS members_3;
DROP TABLE IF EXISTS clients;
DROP TABLE IF EXISTS clients_lk;
DROP TABLE IF EXISTS trb1;
CREATE TABLE t1 (i INT, j INT, k INT, PRIMARY KEY (i)) ENGINE = XENGINE PARTITION BY KEY(i) PARTITIONS 4;
Table	Op	Msg_type	Msg_text
test.t1	optimize	status	OK
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
Table	Op	Msg_type	Msg_text
test.t1	repair	status	OK
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SHOW TABLES;
Tables_in_test
TEMP0
VAR_POP
VAR_SAMP
t1
SELECT * FROM t1 ORDER BY i LIMIT 10;
i	j	k
1	1	1
2	2	2
3	3	3
4	4	4
5	5	5
6	6	6
7	7	7
8	8	8
9	9	9
10	10	10
SELECT COUNT(*) FROM t1;
COUNT(*)
1000
CREATE TABLE ti(
id INT,
amount DECIMAL(7,2),
tr_date DATE
) ENGINE=XENGINE
PARTITION BY HASH(MONTH(tr_date))
PARTITIONS 6;
CREATE TABLE members (
firstname VARCHAR(25) NOT NULL,
lastname VARCHAR(25) NOT NULL,
username VARCHAR(16) NOT NULL,
email VARCHAR(35),
joined DATE NOT NULL
) ENGINE=XENGINE
PARTITION BY KEY(joined)
PARTITIONS 6;
CREATE TABLE members_2 (
firstname VARCHAR(25) NOT NULL,
lastname VARCHAR(25) NOT NULL,
username VARCHAR(16) NOT NULL,
email VARCHAR(35),
joined DATE NOT NULL
) ENGINE=XENGINE
PARTITION BY RANGE(YEAR(joined)) (
PARTITION p0 VALUES LESS THAN (1960),
PARTITION p1 VALUES LESS THAN (1970),
PARTITION p2 VALUES LESS THAN (1980),
PARTITION p3 VALUES LESS THAN (1990),
PARTITION p4 VALUES LESS THAN MAXVALUE
);
CREATE TABLE t2 (val INT)
ENGINE=XENGINE
PARTITION BY LIST(val)(
PARTITION mypart VALUES IN (1,3,5),
PARTITION MyPart VALUES IN (2,4,6)
);
ERROR HY000: Duplicate partition name MyPart
CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT NOT NULL,
store_id INT NOT NULL
) ENGINE=XENGINE
PARTITION BY RANGE (store_id) (
PARTITION p0 VALUES LESS THAN (6),
PARTITION p1 VALUES LESS THAN (11),
PARTITION p2 VALUES LESS THAN (16),
PARTITION p3 VALUES LESS THAN MAXVALUE
);
CREATE TABLE employees_2 (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT NOT NULL,
store_id INT NOT NULL
) ENGINE=XENGINE
PARTITION BY RANGE (job_code) (
PARTITION p0 VALUES LESS THAN (100),
PARTITION p1 VALUES LESS THAN (1000),
PARTITION p2 VALUES LESS THAN (10000)
);
CREATE TABLE employees_3 (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT,
store_id INT
) ENGINE=XENGINE
PARTITION BY RANGE (YEAR(separated)) (
PARTITION p0 VALUES LESS THAN (1991),
PARTITION p1 VALUES LESS THAN (1996),
PARTITION p2 VALUES LESS THAN (2001),
PARTITION p3 VALUES LESS THAN MAXVALUE
);
CREATE TABLE quarterly_report_status (
report_id INT NOT NULL,
report_status VARCHAR(20) NOT NULL,
report_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=XENGINE
PARTITION BY RANGE (UNIX_TIMESTAMP(report_updated)) (
PARTITION p0 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-01-01 00:00:00') ),
PARTITION p1 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-04-01 00:00:00') ),
PARTITION p2 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-07-01 00:00:00') ),
PARTITION p3 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-10-01 00:00:00') ),
PARTITION p4 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-01-01 00:00:00') ),
PARTITION p5 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-04-01 00:00:00') ),
PARTITION p6 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-07-01 00:00:00') ),
PARTITION p7 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-10-01 00:00:00') ),
PARTITION p8 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-01-01 00:00:00') ),
PARTITION p9 VALUES LESS THAN (MAXVALUE)
);
CREATE TABLE employees_4 (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT,
store_id INT
) ENGINE=XENGINE
PARTITION BY LIST(store_id) (
PARTITION pNorth VALUES IN (3,5,6,9,17),
PARTITION pEast VALUES IN (1,2,10,11,19,20),
PARTITION pWest VALUES IN (4,12,13,14,18),
PARTITION pCentral VALUES IN (7,8,15,16)
);
CREATE TABLE h2 (
c1 INT,
c2 INT
) ENGINE=XENGINE
PARTITION BY LIST(c1) (
PARTITION p0 VALUES IN (1, 4, 7),
PARTITION p1 VALUES IN (2, 5, 8)
);
INSERT INTO h2 VALUES (3, 5);
ERROR HY000: Table has no partition for value 3
CREATE TABLE rcx (
a INT,
b INT,
c CHAR(3),
d INT
) ENGINE=XENGINE
PARTITION BY RANGE COLUMNS(a,d,c) (
PARTITION p0 VALUES LESS THAN (5,10,'ggg'),
PARTITION p1 VALUES LESS THAN (10,20,'mmm'),
PARTITION p2 VALUES LESS THAN (15,30,'sss'),
PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE,MAXVALUE)
);
CREATE TABLE r1 (
a INT,
b INT
) ENGINE=XENGINE
PARTITION BY RANGE (a)  (
PARTITION p0 VALUES LESS THAN (5),
PARTITION p1 VALUES LESS THAN (MAXVALUE)
);
INSERT INTO r1 VALUES (5,10), (5,11), (5,12);
CREATE TABLE rc1 (
a INT,
b INT
) ENGINE=XENGINE
PARTITION BY RANGE COLUMNS(a, b) (
PARTITION p0 VALUES LESS THAN (5, 12),
PARTITION p3 VALUES LESS THAN (MAXVALUE, MAXVALUE)
);
INSERT INTO rc1 VALUES (5,10), (5,11), (5,12);
SELECT (5,10) < (5,12), (5,11) < (5,12), (5,12) < (5,12);
(5,10) < (5,12)	(5,11) < (5,12)	(5,12) < (5,12)
1	1	0
CREATE TABLE rx (
a INT,
b INT
) ENGINE=XENGINE
PARTITION BY RANGE COLUMNS (a)  (
PARTITION p0 VALUES LESS THAN (5),
PARTITION p1 VALUES LESS THAN (MAXVALUE)
);
INSERT INTO rx VALUES (5,10), (5,11), (5,12);
CREATE TABLE rc2 (
a INT,
b INT
) ENGINE=XENGINE
PARTITION BY RANGE COLUMNS(a,b) (
PARTITION p0 VALUES LESS THAN (0,10),
PARTITION p1 VALUES LESS THAN (10,20),
PARTITION p2 VALUES LESS THAN (10,30),
PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE)
);
CREATE TABLE rc3 (
a INT,
b INT
) ENGINE=XENGINE
PARTITION BY RANGE COLUMNS(a,b) (
PARTITION p0 VALUES LESS THAN (0,10),
PARTITION p1 VALUES LESS THAN (10,20),
PARTITION p2 VALUES LESS THAN (10,30),
PARTITION p3 VALUES LESS THAN (10,35),
PARTITION p4 VALUES LESS THAN (20,40),
PARTITION p5 VALUES LESS THAN (MAXVALUE,MAXVALUE)
);
CREATE TABLE rc4 (
a INT,
b INT,
c INT
) ENGINE=XENGINE
PARTITION BY RANGE COLUMNS(a,b,c) (
PARTITION p0 VALUES LESS THAN (0,25,50),
PARTITION p1 VALUES LESS THAN (10,20,100),
PARTITION p2 VALUES LESS THAN (10,30,50),
PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE,MAXVALUE)
);
SELECT (0,25,50) < (10,20,100), (10,20,100) < (10,30,50);
(0,25,50) < (10,20,100)	(10,20,100) < (10,30,50)
1	1
CREATE TABLE rcf (
a INT,
b INT,
c INT
) ENGINE=XENGINE
PARTITION BY RANGE COLUMNS(a,b,c) (
PARTITION p0 VALUES LESS THAN (0,25,50),
PARTITION p1 VALUES LESS THAN (20,20,100),
PARTITION p2 VALUES LESS THAN (10,30,50),
PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE,MAXVALUE)
);
ERROR HY000: VALUES LESS THAN value must be strictly increasing for each partition
CREATE TABLE employees_by_lname (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT NOT NULL,
store_id INT NOT NULL
) ENGINE=XENGINE
PARTITION BY RANGE COLUMNS (lname)  (
PARTITION p0 VALUES LESS THAN ('g'),
PARTITION p1 VALUES LESS THAN ('m'),
PARTITION p2 VALUES LESS THAN ('t'),
PARTITION p3 VALUES LESS THAN (MAXVALUE)
);
ALTER TABLE employees_by_lname PARTITION BY RANGE COLUMNS (lname)  (
PARTITION p0 VALUES LESS THAN ('g'),
PARTITION p1 VALUES LESS THAN ('m'),
PARTITION p2 VALUES LESS THAN ('t'),
PARTITION p3 VALUES LESS THAN (MAXVALUE)
);
ALTER TABLE employees_by_lname PARTITION BY RANGE COLUMNS (hired)  (
PARTITION p0 VALUES LESS THAN ('1970-01-01'),
PARTITION p1 VALUES LESS THAN ('1980-01-01'),
PARTITION p2 VALUES LESS THAN ('1990-01-01'),
PARTITION p3 VALUES LESS THAN ('2000-01-01'),
PARTITION p4 VALUES LESS THAN ('2010-01-01'),
PARTITION p5 VALUES LESS THAN (MAXVALUE)
);
CREATE TABLE customers_1 (
first_name VARCHAR(25),
last_name VARCHAR(25),
street_1 VARCHAR(30),
street_2 VARCHAR(30),
city VARCHAR(15),
renewal DATE
) ENGINE=XENGINE
PARTITION BY LIST COLUMNS(city) (
PARTITION pRegion_1 VALUES IN('Oskarshamn', 'Högsby', 'Mönsterås'),
PARTITION pRegion_2 VALUES IN('Vimmerby', 'Hultsfred', 'Västervik'),
PARTITION pRegion_3 VALUES IN('Nässjö', 'Eksjö', 'Vetlanda'),
PARTITION pRegion_4 VALUES IN('Uppvidinge', 'Alvesta', 'Växjo')
);
CREATE TABLE customers_2 (
first_name VARCHAR(25),
last_name VARCHAR(25),
street_1 VARCHAR(30),
street_2 VARCHAR(30),
city VARCHAR(15),
renewal DATE
) ENGINE=XENGINE
PARTITION BY LIST COLUMNS(renewal) (
PARTITION pWeek_1 VALUES IN('2010-02-01', '2010-02-02', '2010-02-03',
'2010-02-04', '2010-02-05', '2010-02-06', '2010-02-07'),
PARTITION pWeek_2 VALUES IN('2010-02-08', '2010-02-09', '2010-02-10',
'2010-02-11', '2010-02-12', '2010-02-13', '2010-02-14'),
PARTITION pWeek_3 VALUES IN('2010-02-15', '2010-02-16', '2010-02-17',
'2010-02-18', '2010-02-19', '2010-02-20', '2010-02-21'),
PARTITION pWeek_4 VALUES IN('2010-02-22', '2010-02-23', '2010-02-24',
'2010-02-25', '2010-02-26', '2010-02-27', '2010-02-28')
);
CREATE TABLE customers_3 (
first_name VARCHAR(25),
last_name VARCHAR(25),
street_1 VARCHAR(30),
street_2 VARCHAR(30),
city VARCHAR(15),
renewal DATE
) ENGINE=XENGINE
PARTITION BY RANGE COLUMNS(renewal) (
PARTITION pWeek_1 VALUES LESS THAN('2010-02-09'),
PARTITION pWeek_2 VALUES LESS THAN('2010-02-15'),
PARTITION pWeek_3 VALUES LESS THAN('2010-02-22'),
PARTITION pWeek_4 VALUES LESS THAN('2010-03-01')
);
CREATE TABLE employees_hash (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT,
store_id INT
) ENGINE=XENGINE
PARTITION BY HASH(store_id)
PARTITIONS 4;
CREATE TABLE employees_hash_1 (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT,
store_id INT
) ENGINE=XENGINE
PARTITION BY HASH( YEAR(hired) )
PARTITIONS 4;
CREATE TABLE t1_hash (
col1 INT,
col2 CHAR(5),
col3 DATE
) ENGINE=XENGINE
PARTITION BY HASH( YEAR(col3) )
PARTITIONS 4;
CREATE TABLE employees_linear_hash (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT,
store_id INT
) ENGINE=XENGINE
PARTITION BY LINEAR HASH( YEAR(hired) )
PARTITIONS 4;
CREATE TABLE t1_linear_hash (
col1 INT,
col2 CHAR(5),
col3 DATE
) ENGINE=XENGINE
PARTITION BY LINEAR HASH( YEAR(col3) )
PARTITIONS 6;
CREATE TABLE k1 (
id INT NOT NULL PRIMARY KEY,
name VARCHAR(20)
) ENGINE=XENGINE
PARTITION BY KEY()
PARTITIONS 2;
CREATE TABLE k2 (
id INT NOT NULL,
name VARCHAR(20),
UNIQUE KEY (id)
) ENGINE=XENGINE
PARTITION BY KEY()
PARTITIONS 2;
CREATE TABLE tm1 (
s1 CHAR(32) PRIMARY KEY
) ENGINE=XENGINE
PARTITION BY KEY(s1)
PARTITIONS 10;
CREATE TABLE tk (
col1 INT NOT NULL,
col2 CHAR(5),
col3 DATE
) ENGINE=XENGINE
PARTITION BY LINEAR KEY (col1)
PARTITIONS 3;
CREATE TABLE ts (
id INT,
purchased DATE
) ENGINE=XENGINE
PARTITION BY RANGE( YEAR(purchased) )
SUBPARTITION BY HASH( TO_DAYS(purchased) )
SUBPARTITIONS 2 (
PARTITION p0 VALUES LESS THAN (1990),
PARTITION p1 VALUES LESS THAN (2000),
PARTITION p2 VALUES LESS THAN MAXVALUE
);
CREATE TABLE ts_1 (
id INT,
purchased DATE
) ENGINE=XENGINE
PARTITION BY RANGE( YEAR(purchased) )
SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
PARTITION p0 VALUES LESS THAN (1990) (
SUBPARTITION s0,
SUBPARTITION s1
),
PARTITION p1 VALUES LESS THAN (2000) (
SUBPARTITION s2,
SUBPARTITION s3
),
PARTITION p2 VALUES LESS THAN MAXVALUE (
SUBPARTITION s4,
SUBPARTITION s5
)
);
CREATE TABLE ts_2 (
id INT,
purchased DATE
) ENGINE=XENGINE
PARTITION BY RANGE( YEAR(purchased) )
SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
PARTITION p0 VALUES LESS THAN (1990) (
SUBPARTITION s0,
SUBPARTITION s1
),
PARTITION p1 VALUES LESS THAN (2000),
PARTITION p2 VALUES LESS THAN MAXVALUE (
SUBPARTITION s2,
SUBPARTITION s3
)
);
ERROR 42000: Wrong number of subpartitions defined, mismatch with previous setting near '
PARTITION p2 VALUES LESS THAN MAXVALUE (
SUBPARTITION s2,
SUBPARTITION s3
)
)' at line 11
CREATE TABLE ts_3 (
id INT,
purchased DATE
) ENGINE=XENGINE
PARTITION BY RANGE( YEAR(purchased) )
SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
PARTITION p0 VALUES LESS THAN (1990) (
SUBPARTITION s0,
SUBPARTITION s1
),
PARTITION p1 VALUES LESS THAN (2000) (
SUBPARTITION s2,
SUBPARTITION s3
),
PARTITION p2 VALUES LESS THAN MAXVALUE (
SUBPARTITION s4,
SUBPARTITION s5
)
);
CREATE TABLE ts_4 (
id INT,
purchased DATE
) ENGINE=XENGINE
PARTITION BY RANGE( YEAR(purchased) )
SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
PARTITION p0 VALUES LESS THAN (1990) (
SUBPARTITION s0,
SUBPARTITION s1
),
PARTITION p1 VALUES LESS THAN (2000) (
SUBPARTITION s2,
SUBPARTITION s3
),
PARTITION p2 VALUES LESS THAN MAXVALUE (
SUBPARTITION s4,
SUBPARTITION s5
)
);
CREATE TABLE ts_5 (
id INT,
purchased DATE
) ENGINE=XENGINE
PARTITION BY RANGE(YEAR(purchased))
SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
PARTITION p0 VALUES LESS THAN (1990) (
SUBPARTITION s0a,
SUBPARTITION s0b
),
PARTITION p1 VALUES LESS THAN (2000) (
SUBPARTITION s1a,
SUBPARTITION s1b
),
PARTITION p2 VALUES LESS THAN MAXVALUE (
SUBPARTITION s2a,
SUBPARTITION s2b
)
);
CREATE TABLE trb3 (
id INT,
name VARCHAR(50),
purchased DATE
) ENGINE=XENGINE
PARTITION BY RANGE( YEAR(purchased) ) (
PARTITION p0 VALUES LESS THAN (1990),
PARTITION p1 VALUES LESS THAN (1995),
PARTITION p2 VALUES LESS THAN (2000),
PARTITION p3 VALUES LESS THAN (2005)
);
ALTER TABLE trb3 PARTITION BY KEY(id) PARTITIONS 2;
CREATE TABLE tr (
id INT,
name VARCHAR(50),
purchased DATE
) ENGINE=XENGINE
PARTITION BY RANGE( YEAR(purchased) ) (
PARTITION p0 VALUES LESS THAN (1990),
PARTITION p1 VALUES LESS THAN (1995),
PARTITION p2 VALUES LESS THAN (2000),
PARTITION p3 VALUES LESS THAN (2005)
);
INSERT INTO tr VALUES
(1, 'desk organiser', '2003-10-15'),
(2, 'CD player', '1993-11-05'),
(3, 'TV set', '1996-03-10'),
(4, 'bookcase', '1982-01-10'),
(5, 'exercise bike', '2004-05-09'),
(6, 'sofa', '1987-06-05'),
(7, 'popcorn maker', '2001-11-22'),
(8, 'aquarium', '1992-08-04'),
(9, 'study desk', '1984-09-16'),
(10, 'lava lamp', '1998-12-25');
SELECT * FROM tr WHERE purchased BETWEEN '1995-01-01' AND '1999-12-31';
id	name	purchased
3	TV set	1996-03-10
10	lava lamp	1998-12-25
ALTER TABLE tr DROP PARTITION p2;
SELECT * FROM tr WHERE purchased BETWEEN '1995-01-01' AND '1999-12-31';
id	name	purchased
CREATE TABLE members_3 (
id INT,
fname VARCHAR(25),
lname VARCHAR(25),
dob DATE
) ENGINE=XENGINE
PARTITION BY RANGE( YEAR(dob) ) (
PARTITION p0 VALUES LESS THAN (1970),
PARTITION p1 VALUES LESS THAN (1980),
PARTITION p2 VALUES LESS THAN (1990)
);
ALTER TABLE members_3 ADD PARTITION (PARTITION p3 VALUES LESS THAN (2000));
ALTER TABLE members_3 ADD PARTITION (PARTITION n VALUES LESS THAN (1960));
ERROR HY000: VALUES LESS THAN value must be strictly increasing for each partition
CREATE TABLE clients (
id INT,
fname VARCHAR(30),
lname VARCHAR(30),
signed DATE
) ENGINE=XENGINE
PARTITION BY HASH( MONTH(signed) )
PARTITIONS 12;
ALTER TABLE clients COALESCE PARTITION 4;
CREATE TABLE clients_lk (
id INT,
fname VARCHAR(30),
lname VARCHAR(30),
signed DATE
) ENGINE=XENGINE
PARTITION BY LINEAR KEY(signed)
PARTITIONS 12;
ALTER TABLE clients COALESCE PARTITION 18;
ERROR HY000: Cannot remove all partitions, use DROP TABLE instead
ALTER TABLE clients ADD PARTITION PARTITIONS 6;
CREATE TABLE trb1 (
id INT,
name VARCHAR(50),
purchased DATE
) ENGINE=XENGINE
PARTITION BY RANGE(id) (
PARTITION p0 VALUES LESS THAN (3),
PARTITION p1 VALUES LESS THAN (7),
PARTITION p2 VALUES LESS THAN (9),
PARTITION p3 VALUES LESS THAN (11)
);
INSERT INTO trb1 VALUES
(1, 'desk organiser', '2003-10-15'),
(2, 'CD player', '1993-11-05'),
(3, 'TV set', '1996-03-10'),
(4, 'bookcase', '1982-01-10'),
(5, 'exercise bike', '2004-05-09'),
(6, 'sofa', '1987-06-05'),
(7, 'popcorn maker', '2001-11-22'),
(8, 'aquarium', '1992-08-04'),
(9, 'study desk', '1984-09-16'),
(10, 'lava lamp', '1998-12-25');
ALTER TABLE trb1 ADD PRIMARY KEY (id);
DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS VAR_POP;
DROP TABLE IF EXISTS TEMP0;
DROP TABLE IF EXISTS VAR_SAMP;
DROP TABLE IF EXISTS ti;
DROP TABLE IF EXISTS members;
DROP TABLE IF EXISTS members_2;
DROP TABLE IF EXISTS employees;
DROP TABLE IF EXISTS employees_2;
DROP TABLE IF EXISTS employees_3;
DROP TABLE IF EXISTS quarterly_report_status;
DROP TABLE IF EXISTS employees_4;
DROP TABLE IF EXISTS h2;
DROP TABLE IF EXISTS rcx;
DROP TABLE IF EXISTS r1;
DROP TABLE IF EXISTS rc1;
DROP TABLE IF EXISTS rx;
DROP TABLE IF EXISTS rc2;
DROP TABLE IF EXISTS rc3;
DROP TABLE IF EXISTS rc4;
DROP TABLE IF EXISTS employees_by_lname;
DROP TABLE IF EXISTS customers_1;
DROP TABLE IF EXISTS customers_2;
DROP TABLE IF EXISTS customers_3;
DROP TABLE IF EXISTS employees_hash;
DROP TABLE IF EXISTS employees_hash_1;
DROP TABLE IF EXISTS t1_hash;
DROP TABLE IF EXISTS employees_linear_hash;
DROP TABLE IF EXISTS t1_linear_hash;
DROP TABLE IF EXISTS k1;
DROP TABLE IF EXISTS k2;
DROP TABLE IF EXISTS tm1;
DROP TABLE IF EXISTS tk;
DROP TABLE IF EXISTS ts;
DROP TABLE IF EXISTS ts_1;
DROP TABLE IF EXISTS ts_3;
DROP TABLE IF EXISTS ts_4;
DROP TABLE IF EXISTS ts_5;
DROP TABLE IF EXISTS trb3;
DROP TABLE IF EXISTS tr;
DROP TABLE IF EXISTS members_3;
DROP TABLE IF EXISTS clients;
DROP TABLE IF EXISTS clients_lk;
DROP TABLE IF EXISTS trb1;