Range Partition :- Range Partitioning maps data to partitions based on ranges of partition key values that you establish for each partition. It is the most common type of partitioning and is often used with dates.
Create a range partition table,
SQL> create table details(order_id number,order_date date) partition by range (order_date) (partition p1 values less than ('01-jan-2018') tablespace t1,partition p2 values less than ('01-mar-2018') tablespace t2,partition p3 values less than ('01-aug-2018') tablespace t3,partition p4 values less than ('01-dec-2018') tablespace t4 );
Table created.
Insert data into partition 1
SQL> insert into details values(1,'10-dec-2017'); 1 row created. SQL> select * from details partition(p1); ORDER_ID ORDER_DAT ---------- --------- 1 10-DEC-17
Insert data into partition 2
SQL> insert into details values(2,'10-feb-2018'); 1 row created. SQL> select * from details partition(p2); ORDER_ID ORDER_DAT ---------- --------- 2 10-FEB-18
Insert data into partition 3
SQL> insert into details values(3,'13-jul-2018'); 1 row created. SQL> select * from details partition(p3); ORDER_ID ORDER_DAT ---------- --------- 3 13-JUL-18
Insert data into partition 4
SQL> insert into details values(4,'1-nov-2018'); 1 row created. SQL> commit; Commit complete. SQL> select * from details partition(p4); ORDER_ID ORDER_DAT ---------- --------- 4 01-NOV-18 SQL> commit;
Partition table data :
SQL> select * from details; ORDER_ID ORDER_DAT ---------- --------- 1 10-DEC-17 2 10-FEB-18 3 13-JUL-18 4 01-NOV-18
LIST PARTITION :- List Partitioning is used to list together unrelated data into partitions. It is a technique where you specify a list of discrete values for the partitioning key in the description for each partition.
Create a list partition table
SQL> CREATE TABLE sales_list (salesman_id NUMBER(5),sales_state VARCHAR2(20)) PARTITION BY LIST(sales_state)(PARTITION south VALUES ('py','tn','ap','kl','ka') tablespace t1,PARTITION east VALUES ('cac','or','bi') tablespace t2,PARTITION west VALUES ('pu','go') tablespace t3,PARTITION north VALUES ('dl','ja') tablespace t4);
Table created.
Insert data into partition table
SQL> insert into sales_list values(1,'py'); 1 row created. SQL> insert into sales_list values(2,'cac'); 1 row created. SQL> insert into sales_list values(3,'pu'); 1 row created. SQL> insert into sales_list values(4,'dl'); 1 row created. SQL> commit; Commit complete.
Partition table data :
SQL> select * from sales_list; SALESMAN_ID SALES_STATE ----------- ------------ 1 py 2 cac 3 pu 4 dl
SQL> select * from sales_list partition(south); SALESMAN_ID SALES_STATE ----------- ----------- 1 py SQL> select * from sales_list partition(east); SALESMAN_ID SALES_STATE ----------- ----------- 2 cac SQL> select * from sales_list partition(west); SALESMAN_ID SALES_STATE ----------- ----------- 3 pu SQL> select * from sales_list partition(north); SALESMAN_ID SALES_STATE ----------- ----------- 4 dl
HASH PARTITION :
Hash partitioning based on a hash algorithm. Hash partitioning enables partitioning of data that does not lend itself to range or list partitioning. The records in a table, are partitions based on Hash value found in the value of the column, which is used for partitioning. Hash partitioning does not have any logical meaning to the partitions as do the range partitioning.
Syntax:-
CREATE TABLE table-name (...col-list...) .... PARTITION BY HASH (col-names) PARTITIONS number-of-partitions STORE IN (tbs-name1,tbs-name2,...);
Create a hash partition table
SQL> CREATE TABLE emp (empno NUMBER(4),sal NUMBER) PARTITION BY HASH(empno) PARTITIONS 3 STORE IN (t1,t2,t3);
Table created.
Check the partition under EMP table
SQL> select partition_name from user_tab_partitions where table_name='EMP';
PARTITION_NAME --------------- SYS_P581 SYS_P582 SYS_P583
Insert data into partition table
SQL> insert into emp values(1,11); 1 row created. SQL> insert into emp values(2,22); 1 row created. SQL> insert into emp values(4,44); 1 row created. SQL> insert into emp values(3,33); 1 row created. SQL> insert into emp values(5,55); 1 row created. SQL> insert into emp values(6,66); 1 row created. SQL> select * from emp; EMPNO SAL ------- ---- 6 66 1 11 4 44 3 33 2 22 5 55 6 rows selected.
SQL> select * from emp partition(SYS_P581); EMPNO SAL ---------- ----- 6 66 SQL> select * from emp partition(SYS_P582); EMPNO SAL ---------- ---- 1 11 4 44 3 33 SQL> select * from emp partition(SYS_P583); EMPNO SAL ------- ---- 2 22 5 55
INTERVAL PARTITION :
Interval partitioning is an enhancement to range partitioning in Oracle 11g and interval partitioning automatically creates time-based partitions as new data is added.The PARTITION BY RANGE clause is used in the normal way to identify the transition point for the partition, then the new INTERVAL clause used to calculate the range for new partitions when the values go beyond the existing transition point.
Create a table with range partition and interval clause
create table order_details(order_id number,order_date date) partition by range (order_date) INTERVAL (NUMTOYMINTERVAL(1,'MONTH')) (partition p1 values less than (TO_DATE('01-NOV-2007','DD-MON-YYYY'))) tablespace t1;
Table created.
Gather the stats
SQL> EXEC DBMS_STATS.gather_table_stats('HARI', 'ORDER_DETAILS');
PL/SQL procedure successfully completed.
Check the partition created and NUM_ROWS under USER_TAB_PARTITIONS
COLUMN table_name FORMAT A20
COLUMN partition_name FORMAT A20
COLUMN high_value FORMAT A40
SELECT table_name, partition_name, high_value, num_rows FROM user_tab_partitions where table_name='ORDER_DETAILS';
TABLE_NAME PARTITION HIGH_VALUE NUM_ROWS
--------------- ---------- --------------------------- --------
ORDER_DETAILS P1 TO_DATE(' 2007-11-01 00:00:00' 0
Insert data with a ORDER_DATE less than ’01-NOV-2007′ the data will be placed in the existing partition p1 and no new partitions will be created.
INSERT INTO order_details VALUES (1, TO_DATE('16-OCT-2007', 'DD-MON-YYYY'));
1 row created.
COMMIT;
Commit complete.
EXEC DBMS_STATS.gather_table_stats('HARI', 'ORDER_DETAILS');
PL/SQL procedure successfully completed.
Check the NUM_ROWS count
SELECT table_name, partition_name, high_value, num_rows FROM user_tab_partitions where table_name='ORDER_DETAILS';
TABLE_NAME PARTITION HIGH_VALUE NUM_ROWS
--------------- ---------- --------------------------- --------
ORDER_DETAILS P1 TO_DATE(' 2007-11-01 00:00:00' 1
If we add data beyond the range of the existing partition, a new partition is created ie., 01-JAN-2008
If we insert data for two months after the current largest transition point, only the required partition is created, not the intervening partitions ie. 2008-02-01
SQL> INSERT INTO order_details VALUES (5, TO_DATE('01-JAN-2008', 'DD-MON-YYYY'));
1 row created.
SQL> INSERT INTO order_details VALUES (6, TO_DATE('31-JAN-2008', 'DD-MON-YYYY'));
1 row created.
SQL> commit;
Commit complete.
SQL> EXEC DBMS_STATS.gather_table_stats('HARI', 'ORDER_DETAILS');
PL/SQL procedure successfully completed.
SQL> SELECT table_name,partition_name,high_value,interval from user_tab_partitions where table_name='ORDER_DETAILS'; TABLE_NAME PARTI HIGH_VALUE INT ---------- ----- ---------------------------------------- --- ORDER_DETAILS P1 TO_DATE(' 2007-11-01 00:00:00', 'SYYYY-M NO M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA ORDER_DETAILS SYS_P TO_DATE(' 2008-02-01 00:00:00', 'SYYYY-M YES 625 M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
Restrictions in Interval partitioning :
- Interval partitioning is restricted to a single partition key that must be a numerical or date range.
- At least one partition must be defined when the table is created.
- Interval partitioning is not supported for index-organized tables.
- You cannot create a domain index on an interval partitioned table.
- Interval partitioning can be used as the primary partitioning mechanism in composite partitioning, but it can’t be used at the subpartition level.
- A MAXVALUE partition cannot be defined for an interval partitioned table.
- NULL values are not allowed in the partition column.
SYSTEM PARTITIONING :
There are scenarios where a database developer or database designer is not able to make a logical way to partition a huge table. Oracle 11g has provided us a way to define partitions in an intelligent manner by System Partitioning, where application needs to control destination partition for a specific record. The DBA just needs to define the partitions.
Table Creation
SQL> create table system_order_details(order_id number,order_date date) partition by SYSTEM (PARTITION p1,PARTITION p2) tablespace t1; Table created.
The partition must be explicitly defined in all insert statements or an error is produced.
SQL> INSERT INTO system_order_details VALUES (1, SYSDATE); INSERT INTO system_order_details VALUES (1, SYSDATE) * ERROR at line 1: ORA-14701: partition-extended name or bind variable must be used for DMLs on tables partitioned by the System method
The PARTITION clause is used to define which partition the row should be placed in.
SQL> INSERT INTO system_order_details PARTITION (p1) VALUES (1, SYSDATE);
1 row created.
SQL> INSERT INTO system_order_details PARTITION (p2) VALUES (2, SYSDATE);
1 row created.
SQL> commit;
Commit complete.
SQL> EXEC DBMS_STATS.gather_table_stats('HARI', 'system_order_details');
PL/SQL procedure successfully completed.
Check the created system table partitions
SQL> COLUMN table_name FORMAT A25
COLUMN partition_name FORMAT A20
COLUMN high_value FORMAT A10
SELECT table_name, partition_name, high_value, num_rows FROM user_tab_partitions where table_name='SYSTEM_ORDER_DETAILS';
TABLE_NAME PARTITION_NAME HIGH_VALUE NUM_ROWS
------------------------- --------------- ---------- --------
SYSTEM_ORDER_DETAILS P1 1
SYSTEM_ORDER_DETAILS P2 1
Notice that the HIGH_VALUE for the partitions is blank.
The PARTITION clause is optional for update and delete statements, but omitting this clause will force all partitions to be scanned, since there is no way perform automatic partition pruning when the database has no control over row placement. When the PARTITION clause is used, you must be sure to perform the operation against the correct partition.
For Delete and Update statements,
SQL> DELETE FROM SYSTEM_ORDER_DETAILS PARTITION (p2) WHERE order_id = 1; 0 rows deleted. SQL> UPDATE SYSTEM_ORDER_DETAILS PARTITION (p1) SET order_id =3 WHERE order_id=2; 0 rows updated.
The PARTITION clause can also be used to check specific partitions.
SQL> SELECT * FROM SYSTEM_ORDER_DETAILS PARTITION (p1); ORDER_ID ORDER_DAT ---------- --------- 1 24-SEP-18
When selecting, updating, or deleting data from a system partitioned object, the best practice is to always specify the partition to avoid scanning all partitions. The use of system partitioning should be viewed with scrutiny by a DBA since it transfers responsibility of optimal use of partitions away from the DBA and to the developers and applications.
REFERENCE PARTITIONING :-
Reference partitioning is for a child table is inherited from the parent table through a primary key – foreign key relationship. The partitioning keys are not stored in actual columns in the child table.
The child table is partitioned using the same partitioning key as the parent table without having to duplicate the key columns.Partition maintenance operations performed on the parent table are reflected on the child table, but no partition maintenance operations are allowed on the child table.
Parent table creation
CREATE TABLE parent_tab (
id NUMBER NOT NULL,
code VARCHAR2(10) NOT NULL,
description VARCHAR2(50),
created_date DATE,
CONSTRAINT parent_tab_pk PRIMARY KEY (id)
)
PARTITION BY RANGE (created_date)
(
PARTITION part_2007 VALUES LESS THAN (TO_DATE('01-JAN-2008','DD-MON-YYYY')),
PARTITION part_2008 VALUES LESS THAN (TO_DATE('01-JAN-2009','DD-MON-YYYY'))
);
Child table creation
CREATE TABLE child_tab (
id NUMBER NOT NULL,
parent_tab_id NUMBER NOT NULL,
code VARCHAR2(10),
description VARCHAR2(50),
created_date DATE,
CONSTRAINT child_tab_pk PRIMARY KEY (id),
CONSTRAINT child_parent_tab_fk FOREIGN KEY (parent_tab_id)
REFERENCES parent_tab (id))
PARTITION BY REFERENCE (child_parent_tab_fk);
Insert data in parent table
INSERT INTO parent_tab VALUES (1, 'ONE', '1 ONE', SYSDATE); INSERT INTO parent_tab VALUES (2, 'TWO', '2 TWO', SYSDATE); INSERT INTO parent_tab VALUES (3, 'THREE', '3 THREE',ADD_MONTHS(SYSDATE,12));
Insert data in child table
INSERT INTO child_tab VALUES (1, 1, 'ONE', '1 1 ONE', SYSDATE); INSERT INTO child_tab VALUES (2, 3, 'TWO', '2 3 TWO', SYSDATE); INSERT INTO child_tab VALUES (3, 3, 'THREE', '3 3 THREE', SYSDATE); COMMIT;
Child records that foreign key to rows in the first partition of the parent table should be placed in the first partition of the child table.So we insert two rows into the first partition
Gather stats for both tables
SQL> exec dbms_stats.gather_table_stats(ownname =>user,tabname=>’PARENT_TAB’); Procédure PL/SQL successfully completed. SQL> exec dbms_stats.gather_table_stats(ownname =>user,tabname=>’CHILD_TAB’); Procédure PL/SQL successfully completed.
We now expect the parent table to have 2 records in the 2007 partition and 1 in the 2008 partition, while the child table should have 1 row in the 2007 partition and 2 rows in the 2008 partition. The following query confirms out expectation.
SELECT table_name, partition_name, high_value, num_rows FROM user_tab_partitions ORDER BY table_name, partition_name; TABLE_NAME PARTITION_NAME HIGH_VALUE NUM_ROWS ------------ -------------- -------------------------------- ---------- CHILD_TAB PART_2007 1 1 CHILD_TAB PART_2008 2 2 PARENT_TAB PART_2007 TO_DATE(' 2008-01-01 00:00:00', 2 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA PARENT_TAB PART_2008 TO_DATE(' 2009-01-01 00:00:00', 1 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 4 rows selected.
COMPOSITE PARTITIONING :-
Composite partitioning is a combination of the basic partitioning techniques of Range, List, Hash, and Interval Partitionings.
Composite Partitioning:
RANGE-HASH PARTITION:
This is basically a combination of range and hash partitions. The data is divided using the range partition and then each range partitioned data is further subdivided into a hash partition using hash key values. All sub partitions, together, represent a logical subset of the data.
SQL> CREATE TABLE rng_hash (cust_id NUMBER(10),time_id DATE) PARTITION BY RANGE(time_id) SUBPARTITION BY HASH(cust_id) SUBPARTITION TEMPLATE(SUBPARTITION sp1 TABLESPACE t1,SUBPARTITION sp2 TABLESPACE t2,SUBPARTITION sp3 TABLESPACE t3)(PARTITION R1 VALUES LESS THAN ('01-apr-2010'),PARTITION R2 VALUES LESS THAN ('01-aug-2010'),PARTITION R3 VALUES LESS THAN ('01-dec-2010'),PARTITION R4 VALUES LESS THAN(MAXVALUE)); Table created.
RANGE-LIST PARTITION:
This is a combination of Range and List partitions, first the data is divided using the Range partition and then each Range partitioned data is further subdivided into List partitions using list key values. Each subpartition individually represents logical subset of the data not like composite Range-Hash Partition.
SQL> CREATE TABLE rng_list (cust_state VARCHAR2(2),time_id DATE)PARTITION BY RANGE(time_id)SUBPARTITION BY LIST (cust_state)SUBPARTITION TEMPLATE(SUBPARTITION south VALUES ('tn','py') TABLESPACE t1,SUBPARTITION east VALUES ('wb','bi') TABLESPACE t2,SUBPARTITION north VALUES ('jm','dl') TABLESPACE t3)(PARTITION a1 VALUES LESS THAN ('01-apr-2010'),PARTITION a2 VALUES LESS THAN ('01-aug-2010'),PARTITION a3 VALUES LESS THAN ('01-dec-2010'),PARTITION a4 VALUES LESS THAN(MAXVALUE)); Table created.
COMPRESS :-
Create a COMPRESS table,
SQL> CREATE TABLE sales (region VARCHAR2(10)) COMPRESS PARTITION BY LIST (region) (PARTITION southwest VALUES ('SOUTHWEST'),PARTITION northeast VALUES ('NORTHEAST') NOCOMPRESS,PARTITION western VALUES ('WESTERN')); Table created.
Check the COMPRESS and NOCOMPRESS partition
SELECT table_name, partition_name, compression, compress_for FROM user_tab_partitions where table_name='SALES'; TABLE_NAME PARTITION_NAME COMPRESS COMPRESS_FOR ---------- -------------------- -------- ------------ SALES NORTHEAST DISABLED SALES SOUTHWEST ENABLED BASIC SALES WESTERN ENABLED BASIC
Catch Me On:- Hariprasath Rajaram Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/ Facebook:https://www.facebook.com/HariPrasathdba FB Group:https://www.facebook.com/groups/894402327369506/ FB Page: https://www.facebook.com/dbahariprasath/? Twitter: https://twitter.com/hariprasathdba
