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
