## Module 3: Hash and Key Partitioned Tables + In this exercise, we will create a Hash partitioned table and a Key partitioned table. After inserting the same data into both tables, we will compare the distribution of the data. ### Step 1. Create a new Hash partitioned table `employees1` ```sql CREATE TABLE employees1( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULL DEFAULT '1970-01-01', job_code INT ) PARTITION BY HASH(YEAR(hired)) PARTITIONS 5; ``` ```output Query OK, 0 rows affected (0.54 sec) ``` ### Step 2. Insert some data into `employees1` ```sql INSERT INTO employees1 VALUES(1, 'Jim', 'Lee', '2023-04-04', 2); INSERT INTO employees1 VALUES(2, 'Tim', 'Anderson', '2023-05-10', 2); INSERT INTO employees1 VALUES(3, 'Mike', 'Jackson', '2023-05-10', 3); INSERT INTO employees1 VALUES(4, 'Benjamin', 'Thompson', '2023-09-24', 1); INSERT INTO employees1 VALUES(5, 'Charlotte', 'Clark', '2023-09-24', 1); ``` ```output Query OK, 1 row affected (0.00 sec) ``` + We found that all rows inserted have same YEAR(hired) value. Let's check the distribution of the data. ```sql ANALYZE TABLE employees1; SELECT table_name, partition_name, partition_description, table_rows FROM information_schema.partitions WHERE table_schema = 'test' AND table_name='employees1' ORDER BY partition_name; ``` ```output +------------+----------------+-----------------------+------------+ | table_name | partition_name | partition_description | table_rows | +------------+----------------+-----------------------+------------+ | employees1 | p0 | | 0 | | employees1 | p1 | | 0 | | employees1 | p2 | | 0 | | employees1 | p3 | | 5 | | employees1 | p4 | | 0 | +------------+----------------+-----------------------+------------+ 5 rows in set (0.01 sec) ``` + You can see that all rows are stored in partition `p3`. Why? Let's now truncate the table `employees1` and insert 5 new rows again. ```sql TRUNCATE TABLE employees1; INSERT INTO employees1 VALUES(1, 'Jim', 'Lee', '2020-04-04', 2); INSERT INTO employees1 VALUES(2, 'Tim', 'Anderson', '2021-05-10', 2); INSERT INTO employees1 VALUES(3, 'Mike', 'Jackson', '2022-05-10', 3); INSERT INTO employees1 VALUES(4, 'Benjamin', 'Thompson', '2023-09-24', 1); INSERT INTO employees1 VALUES(5, 'Charlotte', 'Clark', '2024-09-24', 1); ``` ```output Query OK, 1 row affected (0.01 sec) ``` + This time all rows inserted have different `YEAR(hired)` value. Let's check the distribution of the data. ```sql ANALYZE TABLE employees1; SELECT table_name, partition_name, partition_description, table_rows FROM information_schema.partitions WHERE table_schema = 'test' AND table_name='employees1' ORDER BY partition_name; ``` ```output +------------+----------------+-----------------------+------------+ | table_name | partition_name | partition_description | table_rows | +------------+----------------+-----------------------+------------+ | employees1 | p0 | | 1 | | employees1 | p1 | | 1 | | employees1 | p2 | | 1 | | employees1 | p3 | | 1 | | employees1 | p4 | | 1 | +------------+----------------+-----------------------+------------+ 5 rows in set (0.01 sec) ``` + This time there is one row each partition. ### Step 3. Create a new Key partitioned table `employees2` ```sql CREATE TABLE employees2( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULL DEFAULT '1970-01-01', job_code INT ) PARTITION BY KEY(hired) PARTITIONS 5; ``` ```output Query OK, 0 rows affected (0.54 sec) ``` ### Step 4. Insert some data into `employees2` ```sql INSERT INTO employees2 VALUES(1, 'Jim', 'Lee', '2023-04-04', 2); INSERT INTO employees2 VALUES(2, 'Tim', 'Anderson', '2023-05-10', 2); INSERT INTO employees2 VALUES(3, 'Mike', 'Jackson', '2023-05-10', 3); INSERT INTO employees2 VALUES(4, 'Benjamin', 'Thompson', '2023-09-24', 1); INSERT INTO employees2 VALUES(5, 'Charlotte', 'Clark', '2023-09-24', 1); ``` ```output Query OK, 1 row affected (0.00 sec) ``` + All rows inserted into `employees2` have different `hired` value, but the `VALUES` of `YEAR(hired)` are same. Let's check the distribution of the data. ```sql ANALYZE TABLE employees2; SELECT table_name, partition_name, partition_description, table_rows FROM information_schema.partitions WHERE table_schema = 'test' AND table_name='employees2' ORDER BY partition_name; ``` ```output +------------+----------------+-----------------------+------------+ | table_name | partition_name | partition_description | table_rows | +------------+----------------+-----------------------+------------+ | employees2 | p0 | | 2 | | employees2 | p1 | | 2 | | employees2 | p2 | | 0 | | employees2 | p3 | | 1 | | employees2 | p4 | | 0 | +------------+----------------+-----------------------+------------+ 5 rows in set (0.02 sec) ``` + The rows are distribute in 3 partitions, even the values of YEAR(hired) are same. Let's truncate the table `employees2` and insert 5 new rows again ```sql TRUNCATE TABLE employees2; INSERT INTO employees2 VALUES(1, 'Jim', 'Lee', '2021-05-10', 2); INSERT INTO employees2 VALUES(2, 'Tim', 'Anderson', '2021-05-10', 2); INSERT INTO employees2 VALUES(3, 'Mike', 'Jackson', '2021-05-10', 3); INSERT INTO employees2 VALUES(4, 'Benjamin', 'Thompson', '2021-05-10', 1); INSERT INTO employees2 VALUES(5, 'Charlotte', 'Clark', '2021-05-10', 1); ``` ```output Query OK, 1 row affected (0.00 sec) ``` + All rows inserted into `employees2` have the same `hired` value. Let's check the distribution of the data. ```sql ANALYZE TABLE employees2; SELECT table_name, partition_name, partition_description, table_rows FROM information_schema.partitions WHERE table_schema = 'test' AND table_name='employees2' ORDER BY partition_name; ``` ```output +------------+----------------+-----------------------+------------+ | table_name | partition_name | partition_description | table_rows | +------------+----------------+-----------------------+------------+ | employees2 | p0 | | 0 | | employees2 | p1 | | 0 | | employees2 | p2 | | 0 | | employees2 | p3 | | 0 | | employees2 | p4 | | 5 | +------------+----------------+-----------------------+------------+ 5 rows in set (0.01 sec) ``` + All rows are stored in partition `p4`. Why? Let's truncate the table `employees2` and insert 5 new rows again. ```sql TRUNCATE TABLE employees2; INSERT INTO employees2 VALUES(1, 'Jim', 'Lee', '2020-04-04', 2); INSERT INTO employees2 VALUES(2, 'Tim', 'Anderson', '2021-05-10', 2); INSERT INTO employees2 VALUES(3, 'Mike', 'Jackson', '2022-05-10', 3); INSERT INTO employees2 VALUES(4, 'Benjamin', 'Thompson', '2023-09-24', 1); INSERT INTO employees2 VALUES(5, 'Charlotte', 'Clark', '2024-09-24', 1); ``` ```output Query OK, 1 row affected (0.00 sec) ``` + All rows inserted into `employees2` have different `hired` value. Let's check the distribution of the data. ```sql ANALYZE TABLE employees2; SELECT table_name, partition_name, partition_description, table_rows FROM information_schema.partitions WHERE table_schema = 'test' AND table_name='employees2' ORDER BY partition_name; ``` ```output +------------+----------------+-----------------------+------------+ | table_name | partition_name | partition_description | table_rows | +------------+----------------+-----------------------+------------+ | employees2 | p0 | | 1 | | employees2 | p1 | | 2 | | employees2 | p2 | | 1 | | employees2 | p3 | | 0 | | employees2 | p4 | | 1 | +------------+----------------+-----------------------+------------+ 5 rows in set (0.01 sec) ``` ### Step 5. Clean up the tables ```sql DROP TABLE employees1; DROP TABLE employees2; ``` ```output Query OK, 0 rows affected (0.53 sec) ```