Wednesday, 30 April 2014

Hash Partitioning and Range Partitioning in Oracle

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.

SQL exercises for beginners one

SQL Exercises for Beginners / Simple SQL Exercises with Answers / Solved SQL exercises / SQL solved exercises with simple conditions / So...