HASH-PARTITIONED TABLES

The PARTITION BY HASH clause of the CREATE TABLE statement identifies that the table is to be hash-
partitioned. The PARTITIONS clause can then be used to specify the number of partitions to create, and
optionally, the tablespaces to store them in. Alternatively, you can use PARTITION clauses to name the
individual partitions and their tablespaces.
The only attribute you can specify for hash partitions is TABLESPACE. All of the hash partitions of a table
must share the same segment attributes (except TABLESPACE), which are inherited from the table level.
Using Reference Partitioning:
Hash partitioning is useful when there is no obvious range key, or range partitioning will cause uneven
distribution of data. The number of partitions must be a power of 2 (2, 4, 8, 16…) and can be specified by
the PARTITIONS…STORE IN clause.
Here an example:
CREATE TABLE clothes
(clo_number NUMBER NOT NULL,
clo_date DATE NOT NULL,
clo_note VARCHAR2(100)
)
TABLESPACE users COMPRESS NOLOGGING
PARTITION BY HASH (clo_date)
PARTITIONS 2
STORE IN (users, users );
Or specified individually:
CREATE TABLE clothes
(clo_number NUMBER NOT NULL,
clo_date DATE NOT NULL,
clo_note VARCHAR2(100)
)
TABLESPACE users COMPRESS NOLOGGING
PARTITION BY HASH (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_summer 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
);
Download the script that contains both scenarios seen:
HASH-PARTITIONED TABLES – script.sql
Around the scenery:
– Link to others type of partitions already discussed:
– RANGE-PARTITIONED TABLES (articolo di Novembre)
– LIST-PARTITIONED TABLES and REFERENCE-PARTITIONED TABLES (articolo di Ottobre)
– 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
– Hash-partitioned tables with global indexes