PARTITION
PARTITION is a SQL command used in creating tables, specifically partitioned tables, which logically break a table into segments that help improve database performance. Each segment represents a subset of data and is stored separately, enabling data management and access to be distributed across multiple pieces of hardware or database instances.
Example
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) PARTITION BY RANGE( YEAR(hired) ) ( PARTITION p0 VALUES LESS THAN (1991), PARTITION p1 VALUES LESS THAN (1996), PARTITION p2 VALUES LESS THAN (2001), PARTITION p3 VALUES LESS THAN MAXVALUE);
Output
Query OK, 0 rows affected (0.07 sec)
Explanation
This example creates a partitioned table employees
. The table is partitioned by the hired
column using the range method. Four partitions are defined, where each partition holds rows for which the hired
year falls within a given range. For example, partition p1 holds rows with hired
years from 1991 to 1995.
Example
CREATE TABLE employees ( id INT, first_name VARCHAR(20), last_name VARCHAR(20), department_id INT);
INSERT INTO employeesVALUES (1, 'John', 'Doe', 1), (2, 'Jane', 'Smith', 2), (3, 'Alice', 'Johnson', 1), (4, 'Bob', 'Brown', 2);
SELECT *FROM employeesPARTITION BY department_id;
Output
id | first_name | last_name | department_id----+------------+-----------+--------------- 1 | John | Doe | 1 3 | Alice | Johnson | 1 2 | Jane | Smith | 2 4 | Bob | Brown | 2(4 rows)
Explanation
In this example, the PARTITION BY
clause is used with the SELECT
statement to divide the employees
table into partitions based on the department_id
column. It then returns all data from the employees
table, but the rows in each partition are ordered separately.
Example
CREATE PARTITION FUNCTION PartitionRange (INT)AS RANGE LEFT FOR VALUES (300, 600, 900);
CREATE PARTITION SCHEME PartitionSchemeAS PARTITION PartitionRange TO ([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY]);
CREATE TABLE SampleTable( ID INT IDENTITY, Details VARCHAR(20))ON PartitionScheme (ID);
INSERT INTO SampleTable VALUES ('Detail1'), ('Detail2'), ('Detail3'), ('Detail4'), ('Detail5');
Output
SELECT $PARTITION.PartitionRange(ID) AS Partition, COUNT(*) AS TotalRowsFROM SampleTableGROUP BY $PARTITION.PartitionRange(ID);
Output:
Partition TotalRows1 3002 3003 3004 100
Explanation
This code creates a partition of the SampleTable
based on the ID
field. The data is partitioned into four parts: less than 300, between 300 and 600, between 600 and 900, and greater than 900. The output query shows the count of records that fall into each of the partitions.
Example
SELECT DEPARTMENT_ID, LAST_NAME, SALARY, SUM(SALARY) OVER (PARTITION BY DEPARTMENT_ID ORDER BY SALARY) AS dept_totalFROM EMPLOYEESWHERE DEPARTMENT_ID IN (10,20,30)ORDER BY DEPARTMENT_ID, SALARY;
Output
DEPARTMENT_ID LAST_NAME SALARY DEPT_TOTAL------------- ---------- ------- -----------10 Whalen 4400 440020 Fay 6000 600020 Hartstein 13000 1900030 Baida 2900 290030 Tobias 2800 570030 Himuro 2600 830030 Colmeres 2500 10800
Explanation
In the SQL query given, the SUM() function is used with the OVER() clause to create a sub-total for each department. The PARTITION BY clause is used to divide the SUM() into partitions based on the DEPARTMENT_ID, and ORDER BY to sort the rows in each partition. This results in a running total of the salaries for each department, ordered by the salary within the department.