# `RANGE` Partition - Example

+ The partition key is of integer type

```10
CREATE TABLE subscribers (
 sub_id INT NOT NULL,
 firstname VARCHAR(25) NOT NULL,
 lastname VARCHAR(25) NOT NULL,
 journalid INT NOT NULL,
 joined DATE
)
~!@PARTITION BY RANGE(YEAR(joined))@!~ (
 PARTITION p0 VALUES LESS THAN (2000),
 PARTITION p1 VALUES LESS THAN (2010),
 PARTITION p2 VALUES LESS THAN (2020),
 PARTITION p3 VALUES LESS THAN (2030),
 PARTITION p4 VALUES LESS THAN MAXVALUE
); 
```

------------------------------------------------------------------------------
With `RANGE` partitioning, rows with column or expression values falling within a specified range are assigned to a given partition, the expression should return an integer. For example, suppose you have a table that stores subscriber data and you want to specify that subscribers that joined between 2000 and 2009 are stored in one partition, subscribers that joined between 2010 and 2019 are stored in another partition and so on. In other words, you partition the table based on contiguous ranges of integer values. `RANGE` partitioning is a good fit for such a partitioning scheme.

```
CREATE TABLE subscribers (
 sub_id INT NOT NULL,
 firstname VARCHAR(25) NOT NULL,
 lastname VARCHAR(25) NOT NULL,
 journalid INT NOT NULL,
 joined DATE
)
PARTITION BY RANGE( YEAR(joined) ) (
 PARTITION p0 VALUES LESS THAN (2000),
 PARTITION p1 VALUES LESS THAN (2010),
 PARTITION p2 VALUES LESS THAN (2020),
 PARTITION p3 VALUES LESS THAN (2030),
 PARTITION p4 VALUES LESS THAN MAXVALUE
);
```

From the example, we can see when a table is partitioned by `RANGE`, each partition contains rows for which the partitioning expression value lies within a given `RANGE`. Ranges have to be contiguous but not overlapping. You can define it by using `VALUES LESS THAN`, and `MAXVALUE` represents an integer value that is larger than all other integer values.

`RANGE` partitioning is good for scenarios such as:

1. Removing the outdated data periodically. Assume you are working with the subscribers table mentioned earlier, you can easily accomplish this task by employing DDL operation `ALTER TABLE subscribers DROP PARTITION <partition_name>;`. This approach proves to be quicker compared to executing the DML operation `DELETE FROM subscribers WHERE YEAR ...;`, as it effectively eliminates all records of subscribers which departed by predefined partition.

2. The column contains time or date information, or any other sequentially generated data which can extracted as integer type.

3. Avoiding full table scan. For example, TiDB can efficiently identify that only the `p2` partition needs to be scanned when running a query like `SELECT COUNT(*) FROM subscribers WHERE joined BETWEEN 2010-01-01 AND 2010-12-31 GROUP BY journalid;`, as the other partitions do not satisfy the `WHERE` condition.

When inserting a row into a table partitioned by `RANGE` and the column value used to determine the partition is `NULL`, the row will be inserted into the lowest partition available.

In `RANGE` partitioning, you can partition based on the values of the timestamp column by using `UNIX_TIMESTAMP()`.

Note that `PARTITION BY RANGE` actually only supports integer compatible data types, and will throw an error for other types, for example:

```
tidb> CREATE TABLE subscribers (
 -> sub_id INT NOT NULL,
 -> firstname VARCHAR(25) NOT NULL,
 -> lastname VARCHAR(25) NOT NULL,
 -> journalid DATE NOT NULL,
 -> joined DATE
 -> )
 -> PARTITION BY RANGE(joined)
 -> (
 -> PARTITION `p2023` VALUES LESS THAN ('2024-01-01 00:00:00'),
 -> PARTITION `p2024` VALUES LESS THAN ('2025-01-01 00:00:00'),
 -> PARTITION `p2025` VALUES LESS THAN ('2026-01-01 00:00:00'),
 -> PARTITION `p3026` VALUES LESS THAN ('2027-01-01 00:00:00')
 -> );
ERROR 1697 (HY000): VALUES value for partition 'p2023' must have type INT
```

If we want to partition by ranges based on other types, we can use `Range COLUMNS` partition.

More details: `https://docs.pingcap.com/tidb/stable/partitioned-table#range-partitioning`.