Table Partition:
SQL>select * from user_part_tables;
SQL>select * from user_tab_partitions;
SQL> select table_name,table_owner,partition_name,subpartition_count from dba_tab_partitions where table_name='<table_name>’;
Oracle you can partition a table by
This type of partitioning is useful when dealing with data that has logical ranges into which it can be distributed;
for example, value of year. Performance is best when the data evenly distributes across the range
To create a partition table give the following statement
SQL> create table sale (year number(4), product varchar2(10), amt number(10,2))
partition by range (year)
(
partition p1 values less than (1992) tablespace tbs5,
partition p2 values less than (1993) tablespace tbs5,
partition p3 values less than (1994) tablespace tbs5,
partition p4 values less than (1995) tablespace tbs5,
partition p5 values less than (MAXVALUE) tablespace tbs5);
SQL> select * from sale partition (p1);
SQL> select * from sale where year=1991;
In the above example sales table is created with 5 partitions. Partition p1 will contain rows of year 1991 and
it will be stored in tablespace u1. Partition p2 will contain rows of year 1992 and it will be stored in tablespace u2.
Similarly p3 and p4.
In the above example if you don’t specify the partition p4 with values less than MAVALUE, then you will not be
able to insert any row with year above 1994.
Use hash partitioning if your data does not easily lend itself to range partitioning, but you would like to partition for performance and manageability reasons. Hash partitioning provides a method of evenly distributing data across a specified number of partitions. Rows are mapped into partitions based on a hash value of the partitioning key
The following example shows how to create a hash partition table.
The following example creates a hash-partitioned table. The partitioning column is partno, four partitions are created
and assigned system generated names, and they are placed in four named tablespaces (tab1,tab2, …).
CREATE TABLE products
(partno NUMBER,
description VARCHAR2 (60))
PARTITION BY HASH (partno)
PARTITIONS 4
STORE IN (tab1, tab2, tab3, tab4); (or)
CREATE TABLE products(pro_no NUMBER,name CHAR(20)) PARTITION BY HASH (pro_no) (PARTITION c1,PARTITION c2,PARTITION c3,PARTITION c4);
Use list partitioning when you require explicit control over how rows map to partitions. You can specify a list of discrete
values for the partitioning column in the description for each partition. This is different from range partitioning, where a
range of values is associated with a partition, and from hash partitioning, where the user has no control of the row to
partition mapping.
List partitioning allows unordered and unrelated sets of data to be grouped and organized together very naturally
The following example creates a table with list partitioning
SQL> create table customer (custcode number(5),Name varchar2(20),Addr varchar2(10),city char(20),Bal number(10,2))
Partition by list (city)
(
Partition north_India values (‘DELHI’),
Partition east_India values (‘KOLKOTA’),
Partition south_India values(‘CHENNAI’),
Partition west_India values (‘BOMBAY’));
The following statement creates a composite-partitioned table. In this example, three range partitions are created, each
containing eight subpartitions. Because the subpartitions are not named, system generated names are assigned, but the
STORE IN clause distributes them across the 4 specified tablespaces (tab1, …,tab4).
SQL> CREATE TABLE PRODUCT (pro_no NUMBER,name CHAR(32),costprice NUMBER)
PARTITION BY RANGE (pro_no)
SUBPARTITION BY HASH(name) SUBPARTITIONS 4 STORE IN (tbs6,tbs6,tbs6,tbs6)
(
PARTITION p1 VALUES LESS THAN (100),
PARTITION p2 VALUES LESS THAN (200),
PARTITION p3 VALUES LESS THAN (MAXVALUE));
To check view:
SQL>SET LINESIZE 120
COLUMN table_name FORMAT A20
COLUMN partition_name FORMAT A20
COLUMN subpartition_name FORMAT A20
COLUMN read_only FORMAT A9
SQL> SELECT table_name,partition_name,subpartition_name,read_only FROM user_tab_subpartitions
WHERE table_name = ‘PRODUCT’ ORDER BY 1, 2;
SQL> select * from product SUBPARTITION (SYS_SUBP3787);
(or)
CREATE TABLE t1 (
id NUMBER,
code VARCHAR2(10),
description VARCHAR2(50),
created_date DATE,
CONSTRAINT t1_pk PRIMARY KEY (id)
)
PARTITION BY LIST (code)
SUBPARTITION BY RANGE (created_date) (
PARTITION part_gbr VALUES (‘GBR’) (
SUBPARTITION subpart_gbr_2016 VALUES LESS THAN (TO_DATE(’01-JUL-2017′, ‘DD-MON-YYYY’)),
SUBPARTITION subpart_gbr_2017 VALUES LESS THAN (TO_DATE(’01-JUL-2018′, ‘DD-MON-YYYY’)),
SUBPARTITION subpart_gbr_2018 VALUES LESS THAN (TO_DATE(’01-JUL-2019′, ‘DD-MON-YYYY’))
),
PARTITION part_ire VALUES (‘IRE’) (
SUBPARTITION subpart_ire_2016 VALUES LESS THAN (TO_DATE(’01-JUL-2017′, ‘DD-MON-YYYY’)) ,
SUBPARTITION subpart_ire_2017 VALUES LESS THAN (TO_DATE(’01-JUL-2018′, ‘DD-MON-YYYY’)),
SUBPARTITION subpart_ire_2018 VALUES LESS THAN (TO_DATE(’01-JUL-2019′, ‘DD-MON-YYYY’))
)
);
Sql>SET LINESIZE 120
COLUMN table_name FORMAT A20
COLUMN partition_name FORMAT A20
COLUMN subpartition_name FORMAT A20
COLUMN read_only FORMAT A9
Sql>SELECT table_name,partition_name,subpartition_name,read_only FROM user_tab_subpartitions
WHERE table_name = ‘T1’ ORDER BY 1, 2;
5)interval-partition:
Interval partitioning is a partitioning method introduced in Oracle 11g. This is a helpful addition to range partitioning where Oracle automatically creates a partition when the inserted value exceeds all other partition ranges.
Sql>CREATE TABLE interval_sales
( prod_id NUMBER(6)
, cust_id NUMBER
, time_id DATE
)
PARTITION BY RANGE (time_id)
INTERVAL(NUMTOYMINTERVAL(1, ‘MONTH’))
( PARTITION p0 VALUES LESS THAN (TO_DATE(‘1-1-2008’, ‘DD-MM-YYYY’)),
PARTITION p1 VALUES LESS THAN (TO_DATE(‘1-1-2009’, ‘DD-MM-YYYY’)),
PARTITION p2 VALUES LESS THAN (TO_DATE(‘1-7-2009’, ‘DD-MM-YYYY’)),
PARTITION p3 VALUES LESS THAN (TO_DATE(‘1-1-2010’, ‘DD-MM-YYYY’)) );
SQL> insert into interval_sales values(1,22,’12-may-2010′);
SQL> select table_name,table_owner,partition_name,subpartition_count from dba_tab_partitions where table_name=’INTERVAL_SALES’;