Hash Partitioning in Oracle:
How does Oracle manage Hash partitioning / How does Oracle manage Range Partitioning / Range and Hash Partition Oracle example
CREATE
TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
job_code INT,
store_id INT
)
PARTITION
BY HASH(store_id)
PARTITIONS
4;
The
above CREATE TABLE statement will create the table employees by partitioning
the records based on the Store_id
attribute. Also, PARTITIONS clause defines the number of partitions, in the
above example it is 4. If you don’t include PARTITIONS clause, then it takes
the default value which is 1.
Range Partitioning in Oracle
CREATE TABLE sales_range
(salesman_id NUMBER(5),
salesman_name VARCHAR2(30),
sales_amount NUMBER(10),
sales_date DATE)
PARTITION BY RANGE(sales_date)
(
PARTITION sales_jan2000 VALUES LESS THAN(TO_DATE('02/01/2000','DD/MM/YYYY')),
PARTITION sales_feb2000 VALUES LESS THAN(TO_DATE('03/01/2000','DD/MM/YYYY')),
PARTITION sales_mar2000 VALUES LESS THAN(TO_DATE('04/01/2000','DD/MM/YYYY')),
PARTITION sales_apr2000 VALUES LESS THAN(TO_DATE('05/01/2000','DD/MM/YYYY'))
);
The above statement uses the sales_date attribute as the partitioning
attribute to partition the table into monthly partitions. Using the PARTITION
partitionname VALUES LESS THAN() clause, it mentions the number of partitions
along with unique partition names.
CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
job_code INT NOT NULL,
store_id INT NOT NULL
)
PARTITION BY RANGE (store_id) (
PARTITION p0 VALUES LESS THAN (6),
PARTITION p1 VALUES LESS THAN (11),
PARTITION p2 VALUES LESS THAN (16),
PARTITION p3 VALUES LESS THAN (21)
);
In this example, the table is partitioned into 4 partitions on store_id attribute. Here, the first
partition P0 stores the records with store_id
value less that 6, i.e, 1 to 5, p1 in the range 6 to 10, and so on.
These examples taken from Oracle online documentation.
You can refer to the theory and discussion on various data partitioning techniques here.
You can refer to the theory and discussion on various data partitioning techniques here.