Partitions Oracle

list partition

List partitioning is a partitioning technique where you specify a list of discrete

values for the partitioning key in the description for each partition.

CREATE TABLE myemp_work (

       emp#   NUMBER PRIMARY KEY,

       ename    VARCHAR2(30),

       salary   NUMBER(8,2),

       deptno   NUMBER)

  PARTITION BY LIST (deptno) ( 

       PARTITION p10 VALUES (10),

       PARTITION p20 VALUES (20),

       PARTITION p30 VALUES (30,40));

                  

range partition                         can be done on varchar, number and date fields

Range partitioning is a partitioning technique where ranges of data is stored separately in different sub-tables.

CREATE TABLE emp (

   empno NUMBER(4),

   ename VARCHAR2(30),

   sal   NUMBER

)

PARTITION BY RANGE(empno) (

  partition e1 values less than (1000)     tablespace ts1,

  partition e2 values less than (2000)     tablespace ts2,

  partition e3 values less than (MAXVALUE) tablespace ts3

);

hash partition this can be done on unique value columns

Hash partitioning is a partitioning technique where a hash key is used to distribute rows evenly across the different partitions (sub-tables).

This is typically used where ranges aren't appropriate, i.e. employee number, productID, etc.

create table emp2 (

   empno number(4),

   ename varchar2(30),

   sal   number

)

partition by hash(empno) (

  partition e1 tablespace emp1,

  partition e2 tablespace emp2,

  partition e3 tablespace emp3,

  partition e4 tablespace emp4

);

sub partition: Partitions created within partitions. They are just partitions themselves and there is nothing special about them.

Advantages of using partitions in table

1. Smaller and more manageable pieces of data (partitions).

2. Reduced recovery time.

3. Failure impact is less.

4. Export/Import can be done at the partition level.

5. Faster access of data.

6. Partitions work independent of the other partitions.

7. Very easy to use.

Ref: Source