RANGE-PARTITIONED TABLES

This topic contains RANGE-PARTITIONED TABLES that is available since Oracle 8i.
1. RANGE-PARTITIONED TABLES
The PARTITION BY RANGE clause of the CREATE TABLE statement specifies that the table or index is to be range-partitioned.
The PARTITION clauses identify the individual partition ranges, and the optional sub clauses of a PARTITION clause can specify physical and other attributes specific to a partition segment.
Using Reference Partitioning:
Range partitioning is useful when you have distinct ranges of data you want to store together. The classic example of this is the use of dates.
Here an example:
Creates a table of four partitions, one for each quarter of season, partitioning a table using date ranges allows all data to be stored in same partition.
Once historical data is no longer needed the whole partition can be removed. If the table is indexed correctly search criteria can limit the search to the partitions.
The highest partition must have a partition bound, all of whose values are MAXVALUE. This ensures that all rows in the underlying table can be represented in the index.
CREATE TABLE clothes
(clo_number NUMBER NOT NULL,
clo_date DATE NOT NULL DEFAULT SYSDATE,
clo_note VARCHAR2(100)
)
TABLESPACE users COMPRESS NOLOGGING
PARTITION BY RANGE (clo_date)
(PARTITION p_winter VALUES LESS THAN (TO_DATE(’21/03/2016′, ‘DD/MM/YYYY’)) TABLESPACE users,
PARTITION p_spring VALUES LESS THAN (TO_DATE(’21/06/2016′, ‘DD/MM/YYYY’)) TABLESPACE users,
PARTITION p_sumer VALUES LESS THAN (TO_DATE(’21/09/2016′, ‘DD/MM/YYYY’)) TABLESPACE users,
PARTITION p_fall VALUES LESS THAN (TO_DATE(’21/12/2016′, ‘DD/MM/YYYY’)) TABLESPACE users,
PARTITION p_others VALUES LESS THAN (MAXVALUE) TABLESPACE users );
The range partition is now created and some records are stored into table as the below statement shows:

Range partition and its performance is best when the data evenly distributes across the range.
Download the script that contains the scenario explained:
RANGE-PARTITIONED TABLES – script.sql
Around the scenery:
-Link to others type of partitions already discussed:
-HASH-PARTITIONED TABLES (articolo di Novembre)
-LIST-PARTITIONED TABLES and REFERENCE-PARTITIONED TABLES
-INTERVALPARTITIONING (articolo di Settembre)
-Concept of Partitioning Partitioning enables tables and indexes to be subdivided into individual smaller pieces. Each piece of the database object is called a partition. A partition has its own name, and may optionally have its own storage characteristics. From the perspective of a database administrator, a partitioned object has multiple pieces that can be managed either collectively or individually.
Out of scope:
– Composite Partitioned Tables
– Range-partitioned tables with global indexes