All about Partitions


When To Partition
There are two main reasons to use partitioning: management and performance improvement.

Partitioning offers:
- Management at the individual partition level for data loads, index creation and rebuilding, and backup/recovery. This can result in less down time because only individual partitions being actively managed are unavailable.
- Increased query performance by selecting only from the relevant partitions. This weeding out process eliminates the partitions that do not contain the data needed by the query through a technique called partition pruning.

The decision about exactly when to use partitioning is rather subjective. Some general guidelines that Oracle suggest are listed below. Use partitioning:
- When a table reaches a "large" size. Large being defined relative to your environment. Tables greater than 2GB should always be considered for partitioning.
- When performance benefits outweigh the additional management issues related to partitioning.
- When the archiving of data is on a schedule and is repetitive. For instance, data warehouses usually hold data for a specific amount of time (rolling window). Old data is then rolled off to be archived.

The main thing for an Oracle DBA or a developer is to take a decision on whether to partition a particular table or not. Here are some tips on making a decision for the same:
•  For “large” tables i.e. for tables  >= 2 Gigs
•  If Performance gain outweighs the management of partitioning.
•  If Archiving of data is on a schedule and repetitive.

Tip: SQL to identify the size of a table
SELECT B.OWNER,
       B.TABLESPACE_NAME,
       B.TABLE_NAME,
       ROUND (SUM (BYTES) / 1024 / 1024 / 1024, 6) GIGS
FROM   SYS.DBA_EXTENTS A, SYS.DBA_TABLES B
WHERE ((B.TABLESPACE_NAME = A.TABLESPACE_NAME)
                AND (B.OWNER = UPPER ('&OWNER')) AND (B.TABLE_NAME = '&TABLE'))
GROUP BY B.OWNER, B.TABLESPACE_NAME, B.TABLE_NAME;

A partitioned table can have both partitioned and non-partitioned indexes.
Likewise, a non-partitioned table can have both partitioned and non-partitioned indexes


Partition Keys
A partition key may or may not be the primary key of the table. In my experience, except for hash partitioning, it is rarely the primary key.
A partition key will be one or more columns in your table. In the same way a primary key identifies a distinct row of data in a table, a good partition key will be something that distinctly identifies a category of data.




Different Methods of Partitioning
Oracle offers several types of partitions and  composite partitions. A composite partition allows you to combine partition types into partitions and sub-partitions.
They are listed in the table below with a brief description:

Partitioning Method

Brief Description

Range Partitioning

Used when there are logical ranges of data. Possible usage: dates, part numbers, and serial numbers

Hash Partitioning

Used to spread data evenly over partitions. Possible usage: data has no logical groupings

List Partitioning

Used to list together unrelated data into partitions. Possible usage: a number of states list partitioned into a region
Interval Partitioning
 Interval partitioning is an extension to range partitioning in which, beyond a point in time, partitions are defined by an interval. Interval partitions are automatically created by the database when data is inserted into the partition.
Referential Partitioning
 Data is mapped to partitions based on values defined in a referential constraint (foreign key)
System Partition

Composite Range-Hash Partitioning

Used to range partition first, then spreads data into hash partitions. Possible usage: range partition by date of birth then hash partition by name; store the results into the hash partitions

Composite Range-List Partitioning

Used to range partition first, then spreads data into list partitions.Possible usage: range partition by date of birth then list partition by state, then store the results into the list partitions
Composite List- Hash Partitioning
Composite List- List Partitioning
Composite List- Range Partitioning
Composite Range - Range Partitioning

For partitioning of indexes, there are global and local indexes.

Local indexes (while less flexible than global) are easier to manage. The index is partitioned exactly as the table. Whenever possible, create local indexes. This one-to-one relationship between local index partitions and table partitions allows Oracle the ability to manage local indexes. When table partition is dropped, so is the index partition. A local index can be unique. However, in order for a local index to be unique, the partitioning key of the table must be part of the index's key columns. Unique local indexes are useful for OLTP environments. You cannot explicitly add a partition to a local index. Instead, new partitions are added to local indexes only when you add a partition to the underlying table. Example:
create index in_mytab on mytab (col1) local;

Global indexes provide greater flexibility by allowing indexes to be independent of the partition method used on the table. A single index covering all partitions. This allows for the global index to reference different partitions of a single table. Entries for all parts of the table are found all over the index. Usually used for unique indexes. When table part is dropped, the index needs to be rebuilt.Example
create index pk_mytab on mytab (col2) global;

The use of the ENABLE ROW MOVEMENT clause is included in all of the examples of table partitioning to allow row movement if the partition key is updated.
When you create (or alter) a partitioned table, a row movement clause, either ENABLE ROW MOVEMENT or DISABLE ROW MOVEMENT can be specified. This clause either enables or disables the migration of a row to a new partition if its key is updated. The default is DISABLE ROW MOVEMENT

 
Partitioning Of Tables

Range Partitioning
Range partitioning was the first partitioning method supported by Oracle in Oracle 8. For example, business transactions can be partitioned by various versions of date (start date, transaction date, close date, or date of payment). Range partitioning can also be performed on part numbers, serial numbers or any other ranges that can be discovered. The example provided for range partition will be on a table named PARTITION_BY_RANGE, the actual partitioning is on the following columns BIRTH_YYYY, BIRTH_MM, and BIRTH_DD. Each partition is assigned to its own tablespace. The last partition is the "catch all" partition. By using MAXVALUE the last partition will contain all the records with values over the second to last partition.
Like a list partition, all values must be enumerated. Unlike a list though, you specify the data as a range using a less than operator. Range partitioning allows a default partition to store any records that are greater than the highest partition.

-- RANGE_ME.SQL
-- PARTITION BY RANGE ON BIRTH DATES
CREATE TABLE PARTITION_BY_RANGE
( FIRST_NAME        VARCHAR2(10),
  MIDDLE_INIT        VARCHAR2(1),
  LAST_NAME        VARCHAR2(10),
  BIRTH_MM        INT NOT NULL,
  BIRTH_DD        INT NOT NULL,
  BIRTH_YYYY        INT NOT NULL)
PARTITION BY RANGE (BIRTH_YYYY, BIRTH_MM, BIRTH_DD)
(PARTITION DOBS_IN_1971_OR_BEFORE VALUES LESS THAN (1972, 01 ,01)             TABLESPACE TS01,
 PARTITION DOBS_IN_1972           VALUES LESS THAN (1973, 01 ,01)             TABLESPACE TS02,
 PARTITION DOBS_IN_1973           VALUES LESS THAN (1974, 01 ,01)             TABLESPACE TS03,
 PARTITION DOBS_IN_1974           VALUES LESS THAN (1975, 01 ,01)             TABLESPACE TS04,
 PARTITION DOBS_IN_1975_OR_LATER  VALUES LESS THAN (MAXVALUE, MAXVALUE, MAXVALUE)   TABLESPACE TS05)
ENABLE ROW MOVEMENT;

-- CHECK TO SEE IF THE PARTITIONS ARE CORRECTLY BUILT
select SUBSTR(PARTITION_NAME,1,30) PART_NAME, SUBSTR(tablespace_name,1,40) TABLESPACE, high_value
  from user_tab_partitions
  where table_name= 'PARTITION_BY_RANGE'
    ORDER BY TABLESPACE_NAME;

-- Insert Data Into Each Partition
INSERT INTO PARTITION_BY_RANGE VALUES ('FRED_1969', 'A', 'SMITH_1969', 09, 20, 1969);
INSERT INTO PARTITION_BY_RANGE VALUES ('FRED_1970', 'A', 'SMITH_1970', 09, 20, 1970);
INSERT INTO PARTITION_BY_RANGE VALUES ('FRED_1971', 'A', 'SMITH_1971', 09, 20, 1971);
INSERT INTO PARTITION_BY_RANGE VALUES ('FRED_1972', 'A', 'SMITH_1972', 09, 20, 1972);
INSERT INTO PARTITION_BY_RANGE VALUES ('FRED_1973', 'A', 'SMITH_1973', 09, 20, 1973);
INSERT INTO PARTITION_BY_RANGE VALUES ('FRED_1974', 'A', 'SMITH_1974', 09, 20, 1974);
INSERT INTO PARTITION_BY_RANGE VALUES ('FRED_1975', 'A', 'SMITH_1975', 09, 20, 1975);
INSERT INTO PARTITION_BY_RANGE VALUES ('FRED_1976', 'A', 'SMITH_1976', 09, 20, 1976);
COMMIT;

-- Confirmation that everything is in the proper place
SELECT * FROM PARTITION_BY_RANGE;
SELECT * FROM PARTITION_BY_RANGE PARTITION (DOBS_IN_1971_OR_BEFORE);
SELECT * FROM PARTITION_BY_RANGE PARTITION (DOBS_IN_1972);
SELECT * FROM PARTITION_BY_RANGE PARTITION (DOBS_IN_1973);
SELECT * FROM PARTITION_BY_RANGE PARTITION (DOBS_IN_1974);
SELECT * FROM PARTITION_BY_RANGE PARTITION (DOBS_IN_1975_OR_LATER);

Hash Partitioning
Oracle's hash partitioning distributes data by applying a proprietary hashing algorithm to the partition key and then assigning the data to the appropriate partition. By using hash partitioning, DBA's can partition data that may not have any logical ranges. Also, DBA's do not have to know anything about the actual data itself. Oracle handles all of the distribution of data once the partition key is identified.
Please note that the data may not appear to be distributed evenly because of the limited number of inserts applied to the table
A brief explanation of the code follows. The PARTITION BY HASH line is where the partition key is identified. In this example the partition key is AGE. Once the hashing algorithm is applied each record is distributed to a partition. Each partition is specifically assigned to its own tablespace

-- HASH_ME.SQL
-- PARTITION BY HASH ON AGE
CREATE TABLE PARTITION_BY_HASH
(FIRST_NAME        VARCHAR2(10),
 MIDDLE_INIT        VARCHAR2(1),
 LAST_NAME        VARCHAR2(10),   
 AGE            INT NOT NULL)
PARTITION BY HASH (AGE)
(PARTITION P1_AGE TABLESPACE TS01,
 PARTITION P2_AGE TABLESPACE TS02,
 PARTITION P3_AGE TABLESPACE TS03,
 PARTITION P4_AGE TABLESPACE TS04)
ENABLE ROW MOVEMENT;

-- CHECK TO SEE IF THE PARTITIONS ARE CORRECTLY BUILT
SELECT TABLE_NAME, PARTITION_NAME, TABLESPACE_NAME
FROM USER_TAB_PARTITIONS
WHERE TABLE_NAME = 'PARTITION_BY_HASH'
ORDER BY TABLESPACE_NAME;

-- Insert Data Into Each Partition
INSERT INTO PARTITION_BY_HASH VALUES ('MIKE', 'F', 'SMITH',  45);
INSERT INTO PARTITION_BY_HASH VALUES ('JANE', 'R', 'SMITH',  50);
INSERT INTO PARTITION_BY_HASH VALUES ('NICK', 'R', 'SMITH',  55);
INSERT INTO PARTITION_BY_HASH VALUES ('KIMM', 'B', 'SMITH',  60);
INSERT INTO PARTITION_BY_HASH VALUES ('FRED', 'A', 'SMITH',  70);
INSERT INTO PARTITION_BY_HASH VALUES ('BILL', 'B', 'SMITH',  80);
INSERT INTO PARTITION_BY_HASH VALUES ('JOHN', 'C', 'SMITH',  90);
INSERT INTO PARTITION_BY_HASH VALUES ('DAVE', 'D', 'SMITH', 100);
COMMIT;

-- Confirmation that data is being spread between the partitions
SELECT * FROM PARTITION_BY_HASH ORDER BY AGE;
SELECT * FROM PARTITION_BY_HASH PARTITION (P1_AGE);
SELECT * FROM PARTITION_BY_HASH PARTITION (P2_AGE);
SELECT * FROM PARTITION_BY_HASH PARTITION (P3_AGE);
SELECT * FROM PARTITION_BY_HASH PARTITION (P4_AGE);

List Partitioning
List partitioning was added as a partitioning method in Oracle 9i. List partitioning allows for partitions to reflect real-world groupings (e.g.. business units and territory regions). List partitioning differs from range partition in that the groupings in list partitioning are not side-by-side or in a logical range. List partitioning gives the DBA the ability to group together seemingly unrelated data into a specific partition. The LIST_ME.SQL script provides an example of a list partition table. Note the last partition with the DEFAULT value. This DEFAULT value is new in Oracle 9i, Release 2.
A brief explanation of the code follows. The PARTITION BY LIST line is where the partition key is identified. In this example, the partition key is STATE. Each partition is explicitly named, contains a specific grouping of VALUES and is contained in its own tablespace. The last partition with the DEFAULT is the "catch all" partition. This catch all partition should be queried periodically to make sure that proper data is being entered.
A list partition is probably the simplest partition type to implement and understand. If your data has a column that would make sense looking at it as a list (or as a look up table), it would probably make a good list partition. State, country, color, product type, sic code, or other industry codes all would make good list partition keys.
For a list partition, you must enumerate the list. By that I mean you must specifically say what the elements in the list are. You can create a default partition to accept all values not included as values.

-- LIST_ME.SQL
-- PARTITION BY LIST ON STATE
CREATE TABLE PARTITION_BY_LIST
(DEPTID            NUMBER,
 DEPTNAME        VARCHAR2(15),
 STATE            VARCHAR2(2) ,
 CONSTRAINT PARTITION_BY_LIST_PK PRIMARY KEY (DEPTID))
PARTITION BY LIST (STATE)
(PARTITION DEPTS_IN_NORTH VALUES ('AK')                    TABLESPACE TS01,
 PARTITION DEPTS_IN_EAST  VALUES ('NY', 'NJ', 'VA', 'CT')  TABLESPACE TS02,
 PARTITION DEPTS_IN_SOUTH VALUES ('TX', 'MS', 'GA', 'KY')  TABLESPACE TS03,
 PARTITION DEPTS_IN_WEST  VALUES ('CA', 'AZ', 'OR', 'NV')  TABLESPACE TS04,
 PARTITION DEPTS_WITH_NO_REGION VALUES (DEFAULT)           TABLESPACE TS05)
ENABLE ROW MOVEMENT;

-- CHECK TO SEE PARTITIONS ARE CORRECTLY BUILT
SELECT TABLE_NAME, PARTITION_NAME, HIGH_VALUE, TABLESPACE_NAME
FROM USER_TAB_PARTITIONS
WHERE TABLE_NAME = 'PARTITION_BY_LIST'
ORDER BY TABLESPACE_NAME;

-- Insert Data Into Each Partition
INSERT INTO PARTITION_BY_LIST (DEPTID, DEPTNAME, STATE) VALUES (1,'ANCHORAGE'  , 'AK');
INSERT INTO PARTITION_BY_LIST (DEPTID, DEPTNAME, STATE) VALUES (2,'NEW YORK'   , 'NY');
INSERT INTO PARTITION_BY_LIST (DEPTID, DEPTNAME, STATE) VALUES (3,'DALLAS'     , 'TX');
INSERT INTO PARTITION_BY_LIST (DEPTID, DEPTNAME, STATE) VALUES (4,'LOS ANGELES', 'CA');
INSERT INTO PARTITION_BY_LIST (DEPTID, DEPTNAME, STATE) VALUES (5,'WAIKIKI'    , 'HI');
COMMIT;

-- Confirmation that data is in the proper partitions
select * from PARTITION_BY_LIST;
select * from PARTITION_BY_LIST partition (depts_in_north);
select * from PARTITION_BY_LIST partition (depts_in_east);
select * from PARTITION_BY_LIST partition (depts_in_south);
select * from PARTITION_BY_LIST partition (depts_in_west);
select * from PARTITION_BY_LIST partition (depts_with_no_region);


Interval Partitioning

An interval partition is not really a different partition type as much as it is an extension to a range partition. Because we need to list all of the potential ranges, we have regular maintenance on range partitioned tables. If we partition by month, we may create 12 months in advance but when those run out, we need to create more.
Rather than list all of the potential ranges, interval partitions let us tell Oracle how we want the ranges created and we let Oracle figure out when we need a new partition.
A pseudo declaration of an interval partition might look like:
   Partition by RANGE (sale_date
     Interval of 1 year
     Start with partition 1 sale_date < 01-jan-2008

If we insert a record outside of 2008, Oracle will automatically create a new partition.


Another example:
create table SALES ( sales_id number, sales_dt date )
partition by range (sales_dt)
interval (numtoyminterval(1,'MONTH'))    -- Specifies one partition per month
store in (TS1,TS2,TS3)
( partition SEP08 values less than (to_date('2008-10-01','yyyy-mm-dd')) );  -- This is the first partition. The subsequent partition names are system generated

Creates a partition automatically when a new row comes in


Interval-Numeric Range Partitioned Table

Also possible are Interval-Hash and Interval-List		 CREATE TABLE interval_part (
person_id   NUMBER(5) NOT NULL,
first_name  VARCHAR2(30),
last_name   VARCHAR2(30))
PARTITION BY RANGE (person_id)
INTERVAL (100) STORE IN (part1) (
PARTITION p1 VALUES LESS THAN (101))
TABLESPACE uwdata;

desc interval_part

SELECT table_name, tablespace_name, partitioned
FROM user_tables
ORDER BY 3;

col high_value format a20

SELECT table_name, partition_name, tablespace_name, high_value
FROM user_tab_partitions
ORDER BY 1, 2;

INSERT INTO interval_part
(person_id, first_name, last_name)
VALUES
(100, 'Dan', 'Morgan');

SELECT partition_name, tablespace_name, high_value
FROM user_tab_partitions
WHERE table_name = 'INTERVAL_PART';

INSERT INTO interval_part
(person_id, first_name, last_name)
VALUES
(101, 'Heli', 'Helskyaho');

SELECT partition_name, tablespace_name, high_value
FROM user_tab_partitions
WHERE table_name = 'INTERVAL_PART';

INSERT INTO interval_part
(person_id, first_name, last_name)
VALUES
(567, 'Tara', 'Havemeyer');

SELECT partition_name, tablespace_name, high_value
FROM user_tab_partitions
WHERE table_name = 'INTERVAL_PART';
Interval-Date Range Partitioned Table CREATE TABLE interval_date (
person_id NUMBER(5) NOT NULL,
last_name VARCHAR2(30),
dob       DATE)
PARTITION BY RANGE (dob)
INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
STORE IN (part2, part4, uwdata) (
PARTITION p1 VALUES LESS THAN (TO_DATE('2008-03-15','YYYY-MM-DD')));

INSERT INTO interval_date
(person_id, last_name, dob)
VALUES
(1, 'Morgan', SYSDATE-365);

INSERT INTO interval_date
(person_id, last_name, dob)
VALUES
(2, 'Lofstrom', SYSDATE-365);

INSERT INTO interval_date
(person_id, last_name, dob)
VALUES
(3, 'Havemeyer', SYSDATE-200);

INSERT INTO interval_date
(person_id, last_name, dob)
VALUES
(4, 'Catz', SYSDATE-60);

INSERT INTO interval_date
(person_id, last_name, dob)
VALUES
(5, 'Ellison', SYSDATE+60);

col partition_name format a14
col tablespace_name format a15
col high_value format a85

SELECT partition_name, tablespace_name, high_value
FROM user_tab_partitions
WHERE table_name = 'INTERVAL_DATE';

conn sys@pdbdev as sysdba

SELECT * FROM sys.insert_tsn_list$;
Interval-Interval Range Partitioned Table with new partitions created every six months CREATE TABLE interval_interval (
program_id    NUMBER,
line_number   NUMBER,
order_date    DATE)
PARTITION BY RANGE (order_date)
INTERVAL (NUMTOYMINTERVAL(6,'MONTH'))
STORE IN (uwdata) (
PARTITION p2000 VALUES LESS THAN (TO_DATE('01-JAN-2025', 'DD-MON-YYYY')))
ENABLE ROW MOVEMENT;

INSERT INTO interval_interval
SELECT program_id, line_number, order_Date
FROM airplanes;

COMMIT;

SELECT partition_name, high_value
FROM user_tab_partitions
WHERE table_name = 'INTERVAL_INTERVAL';
Interval-Interval Range Partitioned Table with new partitions created every hour using OLTP compression CREATE TABLE hourly_interval (
datetime  DATE,
some_data NUMBER)
PARTITION BY RANGE (datetime)
INTERVAL (NUMTODSINTERVAL(1,'HOUR'))
STORE IN (part1, part2, part3) (
PARTITION p1 VALUES LESS THAN (TO_DATE('01-JAN-2015 00:00:00', 'DD-MON-YYYY HH24:MI:SS')))
COMPRESS FOR OLTP;

INSERT INTO hourly_interval
(datetime, some_data)
VALUES
(SYSDATE, 1);

INSERT INTO hourly_interval
(datetime, some_data)
VALUES
(SYSDATE+(1/24), 1);

INSERT INTO hourly_interval
(datetime, some_data)
VALUES
(SYSDATE+(1/24), 1);

INSERT INTO hourly_interval
(datetime, some_data)
VALUES
(SYSDATE+(3/24), 1);

COMMIT;

exec dbms_stats.gather_table_stats(USER,'HOURLY_INTERVAL', CASCADE=>TRUE);

col table_name format a11
col partition_name format a9
col compress_for format a8

SELECT table_name, partition_name, high_value, compression, compress_for
FROM user_tab_partitions
WHERE table_name = 'HOURLY_INTERVAL';



Referential Partitioning

One of the most significant enhancements in Oracle 11g is the addition of reference partitions. A reference partition is a partition based on a foreign key.
A good example is an order entry system. Our orders table may be partitioned by order date. Let's say we have 1 billion rows in our table (we are exceptionally successful). Now, we have a child table, order lines, that has 10 times the number of rows but does not have a good partition key. We normally join orders to order_lines on order ID.
In the past we would add order date to the child table so that we could partition using the same key. The benefit of this, in addition to breaking the table into smaller chunks, is that we can do a partition-wise join. When we do partition pruning, we would know to only join with matching child partitions. The downside is that we make our table bigger by adding order date and we now have to maintain order date in two places.
In Oracle 11g, we can declare a reference partition. As the developer, you no longer need to maintain the order date in the order lines table. As long as both tables have active primary keys and an enforced foreign key constraint, you can define the parent partition using the best method available and then define the child constraint as a reference. Oracle will maintain the metadata of the relationship and allow you to do partition-wise joins.
A pseudo declaration of a reference partition might look like:
   Partition by reference (foreign_key_name)
  
Referential Partitioned Table SELECT MIN(num_rows), MAX(num_rows)
FROM all_tables
WHERE num_rows IS NOT NULL;

CREATE TABLE ref_parent (
table_name   VARCHAR2(30),
order_date   DATE,
num_rows     NUMBER)
PARTITION BY RANGE(num_rows) (
PARTITION num_rows1 VALUES LESS THAN (100) TABLESPACE part1,
PARTITION num_rows2 VALUES LESS THAN (1000) TABLESPACE part2,
PARTITION num_rows3 VALUES LESS THAN (10000) TABLESPACE part3,
PARTITION num_rows4 VALUES LESS THAN (MAXVALUE) TABLESPACE part4);

ALTER TABLE ref_parent
ADD CONSTRAINT pk_ref_parent
PRIMARY KEY (table_name)
USING INDEX;

desc ref_parent

SELECT table_name, tablespace_name, partitioned
FROM user_tables
WHERE table_name = 'REF_PARENT';

SELECT partition_name, tablespace_name
FROM user_tab_partitions
WHERE table_name = 'REF_PARENT';

CREATE TABLE ref_child (
table_name VARCHAR2(30) NOT NULL,
index_name VARCHAR2(30) NOT NULL,
CONSTRAINT fk_ref_child_parent
FOREIGN KEY(table_name) REFERENCES ref_parent(table_name))
PARTITION BY REFERENCE(fk_ref_child_parent);

col ref_ptn_constraint_name format a30

SELECT table_name, partitioning_type, ref_ptn_constraint_name
FROM user_part_tables
WHERE table_name LIKE 'REF%';




Systems Partitions
System partitioning allows third party development access to partitioning. That means that it allows an application to control data placement within a partition.

CREATE TABLE syst_part (
tx_id   NUMBER(5),
begdate DATE)
PARTITION BY SYSTEM (
PARTITION p1 TABLESPACE part1,
PARTITION p2 TABLESPACE part2,
PARTITION p3 TABLESPACE part3);

INSERT INTO syst_part VALUES (1, SYSDATE-10);
            *
ERROR at line 1:
ORA-14701: partition-extended name or bind variable must be used for DMLs on tables partitioned by the System method

INSERT INTO syst_part PARTITION (p1) VALUES (1, SYSDATE-10);
INSERT INTO syst_part PARTITION (p2) VALUES (2, SYSDATE);
INSERT INTO syst_part PARTITION (p3) VALUES (3, SYSDATE+10);

SELECT * FROM syst_part PARTITION(p2);


Composite Range-Hash Partitioning
Composite range-hash partitioning combines both the ease of range partitioning and the benefits of hashing for data placement, striping, and parallelism. Range-hash partitioning is slightly harder to implement. But, with the example provided and a detailed explanation of the code one can easily learn how to use this powerful partitioning method.
I suggest that, when you actually try to build a range-hash partition table, you do it in the following steps:
1.    Determine the partition key for the range.
2.    Design a range partition table.
3.    Determine the partition key for the hash.
4.    Create the SUBPARTITION BY HASH clause.
5.    Create the SUBPARTITION TEMPLATE.
Do Steps 1 and 2 first. Then you can insert the code created in Steps 3 -5 in the range partition table syntax.

The RANGE_HASH_ME.SQL script provides an example of a composite range-hash partition table. A brief explanation of the code follows. The PARTITION BY RANGE clause is where we shall begin. The partition key is (BIRTH_YYYY, BIRTH_MM, BIRTH_DD) for the partition. Next, the SUBPARTITION BY HASH clause indicates what the partition key is for the subpartition (in this case FIRST_NAME, MIDDLE_INIT, LAST_NAME). A SUBPARTITION TEMPLATE then defines the subpartition names and their respective tablespace. Subpartitions are automatically named by Oracle by concatenating the partition name, an underscore, and the subpartition name from the template. Remember that the total length of the subpartition name should not be longer than thirty characters including the underscore.

-- RANGE_HASH_ME.SQL
-- PARTITION BY RANGE HASH
CREATE TABLE PARTITION_BY_RANGE_HASH
( FIRST_NAME        VARCHAR2(10),
  MIDDLE_INIT        VARCHAR2(1),
  LAST_NAME        VARCHAR2(10),
  BIRTH_MM        INT NOT NULL,
  BIRTH_DD        INT NOT NULL, 
  BIRTH_YYYY        INT NOT NULL)
TABLESPACE USERS
PARTITION BY RANGE (BIRTH_YYYY, BIRTH_MM, BIRTH_DD) 
 SUBPARTITION BY HASH(FIRST_NAME, MIDDLE_INIT, LAST_NAME)   
 SUBPARTITION TEMPLATE(
    SUBPARTITION SP1 TABLESPACE TS01,
    SUBPARTITION SP2 TABLESPACE TS02,
    SUBPARTITION SP3 TABLESPACE TS03,
    SUBPARTITION SP4 TABLESPACE TS04,
    SUBPARTITION SP5 TABLESPACE TS05)
(PARTITION DOBS_IN_1971_OR_BEFORE VALUES LESS THAN (1972, 01 ,01),
 PARTITION DOBS_IN_1972           VALUES LESS THAN (1973, 01 ,01),
 PARTITION DOBS_IN_1973           VALUES LESS THAN (1974, 01 ,01),
 PARTITION DOBS_IN_1974           VALUES LESS THAN (1975, 01 ,01),
 PARTITION DOBS_IN_1975_OR_LATER  VALUES LESS THAN (MAXVALUE, MAXVALUE, MAXVALUE))
ENABLE ROW MOVEMENT;

-- CHECK TO SEE IF THE PARTITIONS ARE CORRECTLY BUILT AN IN THE PROPER TABLESPACE
SELECT TABLE_NAME, PARTITION_NAME, SUBPARTITION_NAME, TABLESPACE_NAME
FROM USER_TAB_SUBPARTITIONS
WHERE TABLE_NAME = 'PARTITION_BY_RANGE_HASH';


-- INSERT DATA INTO EACH PARTITION
-- DATA FOR PARTITION DOBS_IN_1971_OR_BEFORE
INSERT INTO PARTITION_BY_RANGE_HASH VALUES ('FRED_1966', 'A', 'SMITH_1966', 09, 20, 1966);
INSERT INTO PARTITION_BY_RANGE_HASH VALUES ('FRED_1967', 'A', 'SMITH_1967', 09, 20, 1967);
INSERT INTO PARTITION_BY_RANGE_HASH VALUES ('FRED_1968', 'A', 'SMITH_1968', 09, 20, 1968);
INSERT INTO PARTITION_BY_RANGE_HASH VALUES ('FRED_1969', 'A', 'SMITH_1969', 09, 20, 1969);
INSERT INTO PARTITION_BY_RANGE_HASH VALUES ('FRED_1970', 'A', 'SMITH_1970', 09, 20, 1970);
INSERT INTO PARTITION_BY_RANGE_HASH VALUES ('FRED_1971', 'A', 'SMITH_1971', 05, 16, 1971);
INSERT INTO PARTITION_BY_RANGE_HASH VALUES ('FRED_1971', 'A', 'SMITH_1971', 06, 17, 1971);
INSERT INTO PARTITION_BY_RANGE_HASH VALUES ('FRED_1971', 'A', 'SMITH_1971', 07, 18, 1971);
INSERT INTO PARTITION_BY_RANGE_HASH VALUES ('FRED_1971', 'A', 'SMITH_1971', 08, 19, 1971);
INSERT INTO PARTITION_BY_RANGE_HASH VALUES ('FRED_1971', 'A', 'SMITH_1971', 09, 20, 1971);


-- DATA FOR PARTITION DOBS_IN_1972
INSERT INTO PARTITION_BY_RANGE_HASH VALUES ('BILL_1972', 'B', 'SMITH_1972', 05, 16, 1972);
INSERT INTO PARTITION_BY_RANGE_HASH VALUES ('BILL_1972', 'B', 'SMITH_1972', 06, 17, 1972);
INSERT INTO PARTITION_BY_RANGE_HASH VALUES ('BILL_1972', 'B', 'SMITH_1972', 07, 18, 1972);
INSERT INTO PARTITION_BY_RANGE_HASH VALUES ('BILL_1972', 'B', 'SMITH_1972', 08, 19, 1972);
INSERT INTO PARTITION_BY_RANGE_HASH VALUES ('BILL_1972', 'B', 'SMITH_1972', 09, 20, 1972);

-- DATA FOR PARTITION DOBS_IN_1973
INSERT INTO PARTITION_BY_RANGE_HASH VALUES ('JOHN_1973', 'C', 'SMITH_1973', 05, 16, 1973);
INSERT INTO PARTITION_BY_RANGE_HASH VALUES ('JOHN_1973', 'C', 'SMITH_1973', 06, 17, 1973);
INSERT INTO PARTITION_BY_RANGE_HASH VALUES ('JOHN_1973', 'C', 'SMITH_1973', 07, 18, 1973);
INSERT INTO PARTITION_BY_RANGE_HASH VALUES ('JOHN_1973', 'C', 'SMITH_1973', 08, 19, 1973);
INSERT INTO PARTITION_BY_RANGE_HASH VALUES ('JOHN_1973', 'C', 'SMITH_1973', 09, 20, 1973);

-- DATA FOR PARTITION DOBS_IN_1974
INSERT INTO PARTITION_BY_RANGE_HASH VALUES ('DAVE_1974', 'D', 'SMITH_1974', 05, 16, 1974);
INSERT INTO PARTITION_BY_RANGE_HASH VALUES ('DAVE_1974', 'D', 'SMITH_1974', 06, 17, 1974);
INSERT INTO PARTITION_BY_RANGE_HASH VALUES ('DAVE_1974', 'D', 'SMITH_1974', 07, 18, 1974);
INSERT INTO PARTITION_BY_RANGE_HASH VALUES ('DAVE_1974', 'D', 'SMITH_1974', 08, 19, 1974);
INSERT INTO PARTITION_BY_RANGE_HASH VALUES ('DAVE_1974', 'D', 'SMITH_1974', 09, 20, 1974);

-- DATA FOR PARTITION DOBS_IN_1975_OR_LATER
INSERT INTO PARTITION_BY_RANGE_HASH VALUES ('DAVE_1975', 'D', 'SMITH_1975', 09, 20, 1975);
INSERT INTO PARTITION_BY_RANGE_HASH VALUES ('DAVE_1976', 'D', 'SMITH_1976', 09, 20, 1976);
INSERT INTO PARTITION_BY_RANGE_HASH VALUES ('DAVE_1976', 'D', 'SMITH_1976', 09, 20, 1977);
INSERT INTO PARTITION_BY_RANGE_HASH VALUES ('DAVE_1976', 'D', 'SMITH_1976', 09, 20, 1978);
INSERT INTO PARTITION_BY_RANGE_HASH VALUES ('DAVE_1976', 'D', 'SMITH_1976', 09, 20, 1979);
COMMIT;

-- CONFIRMATION THAT DATA IS BEING SPREAD BETWEEN THE PARTITIONS
SELECT * FROM PARTITION_BY_RANGE_HASH;
SELECT * FROM PARTITION_BY_RANGE_HASH PARTITION (DOBS_IN_1971_OR_BEFORE) ORDER BY BIRTH_YYYY;
SELECT * FROM PARTITION_BY_RANGE_HASH PARTITION (DOBS_IN_1972)           ORDER BY BIRTH_YYYY;
SELECT * FROM PARTITION_BY_RANGE_HASH PARTITION (DOBS_IN_1973)           ORDER BY BIRTH_YYYY;
SELECT * FROM PARTITION_BY_RANGE_HASH PARTITION (DOBS_IN_1974)           ORDER BY BIRTH_YYYY;
SELECT * FROM PARTITION_BY_RANGE_HASH PARTITION (DOBS_IN_1975_OR_LATER)  ORDER BY BIRTH_YYYY;

Composite Range-List Partitioning
Composite range-list partitioning combines both the ease of range partitioning and the benefits of list partitioning at the subpartition level. Like range-hash partitioning, range-list partitioning needs to be carefully designed. The time used to properly design a range-list partition table pays off during the actual creation of the table. The RANGE_LIST_ME.SQL script provides an example of a composite range-list partition table. A brief explanation of the code follows. The PARTITION BY RANGE clause identifies the partition key (BIRTH_YYYY, BIRTH_MM, BIRTH_DD). A SUBPARTITION TEMPLATE then defines the subpartition names and their respective tablespace. Subpartitions are automatically named by Oracle by concatenating the partition name, an underscore, and the subpartition name from the template. Remember that the total length of the subpartition name should not be longer than thirty characters including the underscore.

When building a range-list partition table you may want to refer to the steps mentioned at the end of the Composite Range-List section. The only difference is in Step 4. Instead of "Create the SUBPARTITION BY HASH clause" it would read, "Create the SUBPARTITION BY LIST clause" for the range-list partition table.

-- RANGE_LIST_ME.SQL
-- PARTITION BY RANGE LIST (RANGE ON BIRTH DATES LIST BY STATE)
CREATE TABLE PARTITION_BY_RANGE_LIST
( FIRST_NAME        VARCHAR2(10),
  MIDDLE_INIT        VARCHAR2(1),
  LAST_NAME        VARCHAR2(10),
  BIRTH_MM        INT NOT NULL,
  BIRTH_DD        INT NOT NULL,
  BIRTH_YYYY        INT NOT NULL,
  STATE            VARCHAR2(2) NOT NULL)
TABLESPACE USERS
PARTITION BY RANGE (BIRTH_YYYY, BIRTH_MM, BIRTH_DD)
 SUBPARTITION BY LIST (STATE)
 SUBPARTITION TEMPLATE
  (SUBPARTITION IN_NORTH VALUES ('AK')                   TABLESPACE TS01,
   SUBPARTITION IN_EAST  VALUES ('NY', 'NJ', 'VA', 'CT') TABLESPACE TS02,
   SUBPARTITION IN_SOUTH VALUES ('TX', 'MS', 'GA', 'KY') TABLESPACE TS03,
   SUBPARTITION IN_WEST  VALUES ('CA', 'AZ', 'OR', 'NV') TABLESPACE TS04,
   SUBPARTITION NO_STATE VALUES (DEFAULT)                TABLESPACE TS05)
 (PARTITION DOBS_IN_1971_OR_B4   VALUES LESS THAN (1972, 01 ,01), 
  PARTITION DOBS_IN_1972         VALUES LESS THAN (1973, 01 ,01),
  PARTITION DOBS_IN_1973         VALUES LESS THAN (1974, 01 ,01), 
  PARTITION DOBS_IN_1974         VALUES LESS THAN (1975, 01 ,01),
  PARTITION DOBS_IN_1975_TO_MAX  VALUES LESS THAN (MAXVALUE, MAXVALUE, MAXVALUE))
ENABLE ROW MOVEMENT;


-- CHECK TO SEE IF THE PARTITIONS ARE CORRECTLY BUILT AN IN THE PROPER TABLESPACE
SELECT TABLE_NAME, PARTITION_NAME, SUBPARTITION_NAME, TABLESPACE_NAME 
FROM USER_TAB_SUBPARTITIONS
WHERE TABLE_NAME = 'PARTITION_BY_RANGE_LIST';

-- Insert Data Into Each Partition
-- DATA FOR DOBS_IN_1971_OR_BEFORE
INSERT INTO PARTITION_BY_RANGE_LIST VALUES ('FN', 'M', 'LN', 01, 01, 1970, 'AK');
INSERT INTO PARTITION_BY_RANGE_LIST VALUES ('FN', 'M', 'LN', 02, 02, 1970, 'NY');
INSERT INTO PARTITION_BY_RANGE_LIST VALUES ('FN', 'M', 'LN', 03, 03, 1970, 'TX');
INSERT INTO PARTITION_BY_RANGE_LIST VALUES ('FN', 'M', 'LN', 04, 04, 1970, 'CA');
INSERT INTO PARTITION_BY_RANGE_LIST VALUES ('FN', 'M', 'LN', 05, 05, 1970, 'HI');
INSERT INTO PARTITION_BY_RANGE_LIST VALUES ('FN', 'M', 'LN', 01, 01, 1971, 'AK');
INSERT INTO PARTITION_BY_RANGE_LIST VALUES ('FN', 'M', 'LN', 02, 02, 1971, 'NY');
INSERT INTO PARTITION_BY_RANGE_LIST VALUES ('FN', 'M', 'LN', 03, 03, 1971, 'TX');
INSERT INTO PARTITION_BY_RANGE_LIST VALUES ('FN', 'M', 'LN', 04, 04, 1971, 'CA');
INSERT INTO PARTITION_BY_RANGE_LIST VALUES ('FN', 'M', 'LN', 05, 05, 1971, 'HI');

-- DATA FOR DOBS_IN_1972
INSERT INTO PARTITION_BY_RANGE_LIST VALUES ('FN', 'M', 'LN', 01, 01, 1972, 'AK');
INSERT INTO PARTITION_BY_RANGE_LIST VALUES ('FN', 'M', 'LN', 02, 02, 1972, 'NY');
INSERT INTO PARTITION_BY_RANGE_LIST VALUES ('FN', 'M', 'LN', 03, 03, 1972, 'TX');
INSERT INTO PARTITION_BY_RANGE_LIST VALUES ('FN', 'M', 'LN', 04, 04, 1972, 'CA');
INSERT INTO PARTITION_BY_RANGE_LIST VALUES ('FN', 'M', 'LN', 05, 05, 1972, 'HI');

-- DATA FOR DOBS_IN_1973
INSERT INTO PARTITION_BY_RANGE_LIST VALUES ('FN', 'M', 'LN', 01, 01, 1973, 'AK');
INSERT INTO PARTITION_BY_RANGE_LIST VALUES ('FN', 'M', 'LN', 02, 02, 1973, 'NY');
INSERT INTO PARTITION_BY_RANGE_LIST VALUES ('FN', 'M', 'LN', 03, 03, 1973, 'TX');
INSERT INTO PARTITION_BY_RANGE_LIST VALUES ('FN', 'M', 'LN', 04, 04, 1973, 'CA');
INSERT INTO PARTITION_BY_RANGE_LIST VALUES ('FN', 'M', 'LN', 05, 05, 1973, 'HI');

-- DATA FOR DOBS_IN_1974
INSERT INTO PARTITION_BY_RANGE_LIST VALUES ('FN', 'M', 'LN', 01, 01, 1974, 'AK');
INSERT INTO PARTITION_BY_RANGE_LIST VALUES ('FN', 'M', 'LN', 02, 02, 1974, 'NY');
INSERT INTO PARTITION_BY_RANGE_LIST VALUES ('FN', 'M', 'LN', 03, 03, 1974, 'TX');
INSERT INTO PARTITION_BY_RANGE_LIST VALUES ('FN', 'M', 'LN', 04, 04, 1974, 'CA');
INSERT INTO PARTITION_BY_RANGE_LIST VALUES ('FN', 'M', 'LN', 05, 05, 1974, 'HI');

-- DATA FOR DOBS_IN_1975_OR_LATER
INSERT INTO PARTITION_BY_RANGE_LIST VALUES ('FN', 'M', 'LN', 01, 01, 1975, 'AK');
INSERT INTO PARTITION_BY_RANGE_LIST VALUES ('FN', 'M', 'LN', 02, 02, 1975, 'NY');
INSERT INTO PARTITION_BY_RANGE_LIST VALUES ('FN', 'M', 'LN', 03, 03, 1975, 'TX');
INSERT INTO PARTITION_BY_RANGE_LIST VALUES ('FN', 'M', 'LN', 04, 04, 1975, 'CA');
INSERT INTO PARTITION_BY_RANGE_LIST VALUES ('FN', 'M', 'LN', 05, 05, 1975, 'HI');
INSERT INTO PARTITION_BY_RANGE_LIST VALUES ('FN', 'M', 'LN', 01, 01, 1976, 'AK');
INSERT INTO PARTITION_BY_RANGE_LIST VALUES ('FN', 'M', 'LN', 02, 02, 1976, 'NY');
INSERT INTO PARTITION_BY_RANGE_LIST VALUES ('FN', 'M', 'LN', 03, 03, 1976, 'TX');
INSERT INTO PARTITION_BY_RANGE_LIST VALUES ('FN', 'M', 'LN', 04, 04, 1976, 'CA');
INSERT INTO PARTITION_BY_RANGE_LIST VALUES ('FN', 'M', 'LN', 05, 05, 1976, 'HI');
COMMIT;

-- Confirmation that data is being spread between the partitions
SELECT * FROM PARTITION_BY_RANGE_LIST ORDER BY STATE, BIRTH_YYYY;               
SELECT * FROM PARTITION_BY_RANGE_LIST PARTITION (DOBS_IN_1971_OR_B4) ORDER BY BIRTH_YYYY;       
SELECT * FROM PARTITION_BY_RANGE_LIST PARTITION (DOBS_IN_1972);               
SELECT * FROM PARTITION_BY_RANGE_LIST PARTITION (DOBS_IN_1973);               
SELECT * FROM PARTITION_BY_RANGE_LIST PARTITION (DOBS_IN_1974);               
SELECT * FROM PARTITION_BY_RANGE_LIST PARTITION (DOBS_IN_1975_TO_MAX) ORDER BY BIRTH_YYYY;  

 


Composite Partitioned Table - By List And Hash CREATE TABLE composite_list_hash (
cust_id     NUMBER(10),
cust_name   VARCHAR2(25),
cust_state  VARCHAR2(2),
time_id     DATE)
PARTITION BY LIST(cust_state)
SUBPARTITION BY HASH (cust_id)
SUBPARTITION TEMPLATE(
SUBPARTITION sp1 TABLESPACE part1,
SUBPARTITION sp2 TABLESPACE part2,
SUBPARTITION sp3 TABLESPACE part3,
SUBPARTITION sp4 TABLESPACE part4) (
PARTITION west VALUES ('OR', 'WA'),
PARTITION east VALUES ('NY', 'CT'),
PARTITION cent VALUES ('IL', 'MN'));
Composite Partitioned Table - By List And List CREATE TABLE composite_list_list (
cust_id     NUMBER(10),
cust_name   VARCHAR2(25),
cust_state  VARCHAR2(2),
time_id     DATE)
PARTITION BY LIST(cust_state)
SUBPARTITION BY LIST (cust_id)
SUBPARTITION TEMPLATE(
SUBPARTITION beg VALUES (1,3,5) TABLESPACE part1,
SUBPARTITION mid VALUES (2,4,6) TABLESPACE part2,
SUBPARTITION end VALUES (7,8,9,0) TABLESPACE part3) (
PARTITION west VALUES ('OR', 'WA'),
PARTITION east VALUES ('NY', 'CT'),
PARTITION cent VALUES ('IL', 'MN'));
Composite Partitioned Table - By List And Range CREATE TABLE composite_list_rng (
cust_id     NUMBER(10),
cust_name   VARCHAR2(25),
cust_state  VARCHAR2(2),
time_id     DATE)
PARTITION BY LIST(cust_state)
SUBPARTITION BY RANGE (cust_id)
SUBPARTITION TEMPLATE(
SUBPARTITION original VALUES LESS THAN (1001) TABLESPACE part1,
SUBPARTITION acquired VALUES LESS THAN (8001) TABLESPACE part2,
SUBPARTITION recent VALUES LESS THAN (MAXVALUE) TABLESPACE part3) (
PARTITION west VALUES ('OR', 'WA'),
PARTITION east VALUES ('NY', 'CT'),
PARTITION cent VALUES ('IL', 'MN'));
Composite Partitioned Table - By Range And Range CREATE TABLE composite_rng_rng (
cust_id     NUMBER(10),
cust_name   VARCHAR2(25),
cust_state  VARCHAR2(2),
time_id     DATE)
PARTITION BY RANGE(time_id)
SUBPARTITION BY RANGE (cust_id)
SUBPARTITION TEMPLATE(
SUBPARTITION original VALUES LESS THAN (1001) TABLESPACE part1,
SUBPARTITION acquired VALUES LESS THAN (8001) TABLESPACE part2,
SUBPARTITION recent VALUES LESS THAN (MAXVALUE) TABLESPACE part3) (
PARTITION per1 VALUES LESS THAN (TO_DATE('01/01/2000','DD/MM/YYYY')),
PARTITION per2 VALUES LESS THAN (TO_DATE('01/01/2010','DD/MM/YYYY')),
PARTITION per3 VALUES LESS THAN (TO_DATE('01/01/2020','DD/MM/YYYY')),
PARTITION future VALUES LESS THAN (MAXVALUE));

desc composite_rng_rng

SELECT table_name, partition_name, composite, high_value
FROM user_tab_partitions;

SELECT table_name, partition_name, subpartition_name, num_rows
FROM user_tab_subpartitions;

Partitioning Of Indexes

Locally Partitioned Indexes
Locally partitioned indexes are for the most part very straightforward. A local index maps one-to-one with the partitions of the table.
This enables Oracle to automatically keep the index partitions in sync with the table partitions, and makes each table-index pair independent. Any actions that make one partition's data invalid or unavailable only affect a single partition. Whenever possible, create local indexes.
You cannot explicitly add a partition to a local index. Instead, new partitions are added to local indexes only when you add a partition to the underlying table. Likewise, you cannot explicitly drop a partition from a local index. Instead, local index partitions are dropped only when you drop a partition from the underlying table.
A local index can be unique. However, in order for a local index to be unique, the partitioning key of the table must be part of the index's key columns. Unique local indexes are useful for OLTP environments.

The following script shows examples of this type of index. In the script, locally partitioned indexes are created on three differently partitioned tables (range, hash, and list).
CREATE INDEX PARTITION_BY_RANGE_LI ON PARTITION_BY_RANGE (LAST_NAME) LOCAL
(PARTITION RANGE_LIP_10 TABLESPACE ITS01,
 PARTITION RANGE_LIP_20 TABLESPACE ITS02,
 PARTITION RANGE_LIP_30 TABLESPACE ITS03,
 PARTITION RANGE_LIP_40 TABLESPACE ITS04,
 PARTITION RANGE_LIP_50 TABLESPACE ITS05);

CREATE INDEX PARTITION_BY_HASH_LI ON PARTITION_BY_HASH (LAST_NAME) LOCAL
(PARTITION HASH_LIP_10 TABLESPACE ITS01,
 PARTITION HASH_LIP_20 TABLESPACE ITS02,
 PARTITION HASH_LIP_30 TABLESPACE ITS03,
 PARTITION HASH_LIP_40 TABLESPACE ITS04);

CREATE INDEX PARTITION_BY_LIST_LI ON PARTITION_BY_LIST (STATE) LOCAL
(PARTITION  LIST_LIP_10  TABLESPACE ITS01,
 PARTITION  LIST_LIP_20  TABLESPACE ITS02,
 PARTITION  LIST_LIP_30  TABLESPACE ITS03,
 PARTITION  LIST_LIP_40  TABLESPACE ITS04,
 PARTITION  LIST_LIP_50  TABLESPACE ITS05);

Extra time should be allocated when creating locally partitioned indexes on range-hash or range-list partitioned tables. There are a couple reasons that extra time is needed for this type of index. One of the reasons is a decision needs to be made on what the index will be referencing in regards to a range-hash or range-list partitioned tables. A locally partitioned index can be created to point to either partition level or subpartition level.

The following script is an example for the creation of two locally partitioned indexes. This scripts show how to create a locally partitioned index on both a range-hash and range-list partitioned tables at the partition level. Each of the partitions of the locally partitioned indexes is assigned to its own tablespace for improved performance
-- INDEXES REFERCING THE PARTITION LEVEL
-- USE EITHER LOCAL PARTITIONED INDEX ON THE PARTITION LEVEL OR SUBPARTITION LEVEL
-- BUT NOT BOTH ON THE SAME INDEX COLUMN
-- FOR COMPOSITE RANGE_HASH LOCAL PARTITIONED INDEX ON THE PARTITION LEVEL
CREATE INDEX PARTITION_BY_RANGE_HASH_LIP ON PARTITION_BY_RANGE_HASH (LAST_NAME) LOCAL
(PARTITION RANGE_HASH_LIP_10 TABLESPACE ITS01,
 PARTITION RANGE_HASH_LIP_20 TABLESPACE ITS02,
 PARTITION RANGE_HASH_LIP_30 TABLESPACE ITS03,
 PARTITION RANGE_HASH_LIP_40 TABLESPACE ITS03,
 PARTITION RANGE_HASH_LIP_50 TABLESPACE ITS04);

-- USE EITHER LOCAL PARTITIONED INDEX ON THE PARTITION LEVEL OR SUBPARTITION LEVEL
-- BUT NOT BOTH ON THE SAME INDEX COLUMN
-- FOR COMPOSITE RANGE_LIST LOCAL PARTITIONED INDEX ON THE PARTITION LEVEL
CREATE INDEX PARTITION_BY_RANGE_LIST_LIP ON PARTITION_BY_RANGE_LIST (LAST_NAME) LOCAL
(PARTITION RANGE_LIST_LIP_01 TABLESPACE ITS01,
 PARTITION RANGE_LIST_LIP_02 TABLESPACE ITS02,
 PARTITION RANGE_LIST_LIP_03 TABLESPACE ITS03,
 PARTITION RANGE_LIST_LIP_04 TABLESPACE ITS04,
 PARTITION RANGE_LIST_LIP_05 TABLESPACE ITS05
);

When creating a locally partitioned index one needs to keep in mind the number of subpartitions of the range-hash or range-list partitioned table being indexed. Reason being, is that the locally partitioned index will need to reference each subpartition of the range-hash or range-list partitioned table. So, for the locally partitioned index created by LPI4CPT2_ME.SQL, this mean that one index references twenty-five different subpartitions. For a visual representation of this refer to Figure 4. Script LPI4CPT3_ME.SQL is provided as an example of locally partitioned index on a range-list partition table.

-- LOCAL COMPOSITE INDEXES REFERCING THE SUBPARTITION LEVEL
-- USE EITHER LOCAL PARTITIONED INDEX ON THE PARTITION LEVEL OR SUBPARTITION LEVEL
-- BUT NOT BOTH ON THE SAME INDEX COLUMN
-- FOR COMPOSITE RANGE_HASH LOCAL PARTITIONED INDEX ON THE SUBPARTITION LEVEL
CREATE INDEX PARTITION_BY_RANGE_HASH_LISP ON PARTITION_BY_RANGE_HASH (LAST_NAME) LOCAL
(PARTITION RANGE_HASH_LISP_10          TABLESPACE ITS05
 (SUBPARTITION RANGE_HASH_LISP_10_SP10 TABLESPACE ITS01 ,
  SUBPARTITION RANGE_HASH_LISP_10_SP20 TABLESPACE ITS02 ,
  SUBPARTITION RANGE_HASH_LISP_10_SP30 TABLESPACE ITS03 ,
  SUBPARTITION RANGE_HASH_LISP_10_SP40 TABLESPACE ITS04 ,
  SUBPARTITION RANGE_HASH_LISP_10_SP50 TABLESPACE ITS05        
  ),
 PARTITION RANGE_HASH_LISP_20          TABLESPACE ITS04
 (SUBPARTITION RANGE_HASH_LISP_20_SP10 TABLESPACE ITS01 ,
  SUBPARTITION RANGE_HASH_LISP_20_SP20 TABLESPACE ITS02 ,
  SUBPARTITION RANGE_HASH_LISP_20_SP30 TABLESPACE ITS03 ,
  SUBPARTITION RANGE_HASH_LISP_20_SP40 TABLESPACE ITS04 ,
  SUBPARTITION RANGE_HASH_LISP_20_SP50 TABLESPACE ITS05        
  ), 
 PARTITION RANGE_HASH_LISP_30          TABLESPACE ITS03
 (SUBPARTITION RANGE_HASH_LISP_30_SP10 TABLESPACE ITS01 ,
  SUBPARTITION RANGE_HASH_LISP_30_SP20 TABLESPACE ITS02 ,
  SUBPARTITION RANGE_HASH_LISP_30_SP30 TABLESPACE ITS03 ,
  SUBPARTITION RANGE_HASH_LISP_30_SP40 TABLESPACE ITS04 ,
  SUBPARTITION RANGE_HASH_LISP_30_SP50 TABLESPACE ITS05        
  ),
 PARTITION RANGE_HASH_LISP_40          TABLESPACE ITS02
 (SUBPARTITION RANGE_HASH_LISP_40_SP10 TABLESPACE ITS01 ,
  SUBPARTITION RANGE_HASH_LISP_40_SP20 TABLESPACE ITS02 ,
  SUBPARTITION RANGE_HASH_LISP_40_SP30 TABLESPACE ITS03 ,
  SUBPARTITION RANGE_HASH_LISP_40_SP40 TABLESPACE ITS04 ,
  SUBPARTITION RANGE_HASH_LISP_40_SP50 TABLESPACE ITS05        
  ),
 PARTITION RANGE_HASH_LISP_50          TABLESPACE ITS01
 (SUBPARTITION RANGE_HASH_LISP_50_SP10 TABLESPACE ITS01 ,
  SUBPARTITION RANGE_HASH_LISP_50_SP20 TABLESPACE ITS02 ,
  SUBPARTITION RANGE_HASH_LISP_50_SP30 TABLESPACE ITS03 ,
  SUBPARTITION RANGE_HASH_LISP_50_SP40 TABLESPACE ITS04 ,
  SUBPARTITION RANGE_HASH_LISP_50_SP50 TABLESPACE ITS05        
  )
);


-- INDEXES REFERCING THE SUBPARTITION LEVEL
-- FOR COMPOSITE RANGE_LIST LOCAL PARTITIONED INDEX ON THE SUBPARTITION LEVEL
CREATE INDEX PARTITION_BY_RANGE_LIST_LISP ON PARTITION_BY_RANGE_LIST (LAST_NAME) LOCAL
(PARTITION RANGE_LIST_LISP_10          TABLESPACE ITS05
 (SUBPARTITION RANGE_LIST_LISP_10_SP10 TABLESPACE ITS01 ,
  SUBPARTITION RANGE_LIST_LISP_10_SP20 TABLESPACE ITS02 ,
  SUBPARTITION RANGE_LIST_LISP_10_SP30 TABLESPACE ITS03 ,
  SUBPARTITION RANGE_LIST_LISP_10_SP40 TABLESPACE ITS04 ,
  SUBPARTITION RANGE_LIST_LISP_10_SP50 TABLESPACE ITS05        
  ),
 PARTITION RANGE_LIST_LISP_20          TABLESPACE ITS04
 (SUBPARTITION RANGE_LIST_LISP_20_SP10 TABLESPACE ITS01 ,
  SUBPARTITION RANGE_LIST_LISP_20_SP20 TABLESPACE ITS02 ,
  SUBPARTITION RANGE_LIST_LISP_20_SP30 TABLESPACE ITS03 ,
  SUBPARTITION RANGE_LIST_LISP_20_SP40 TABLESPACE ITS04 ,
  SUBPARTITION RANGE_LIST_LISP_20_SP50 TABLESPACE ITS05        
  ), 
 PARTITION RANGE_LIST_LISP_30          TABLESPACE ITS03
 (SUBPARTITION RANGE_LIST_LISP_30_SP10 TABLESPACE ITS01 ,
  SUBPARTITION RANGE_LIST_LISP_30_SP20 TABLESPACE ITS02 ,
  SUBPARTITION RANGE_LIST_LISP_30_SP30 TABLESPACE ITS03 ,
  SUBPARTITION RANGE_LIST_LISP_30_SP40 TABLESPACE ITS04 ,
  SUBPARTITION RANGE_LIST_LISP_30_SP50 TABLESPACE ITS05        
  ),
 PARTITION RANGE_LIST_LISP_40          TABLESPACE ITS02
 (SUBPARTITION RANGE_LIST_LISP_40_SP10 TABLESPACE ITS01 ,
  SUBPARTITION RANGE_LIST_LISP_40_SP20 TABLESPACE ITS02 ,
  SUBPARTITION RANGE_LIST_LISP_40_SP30 TABLESPACE ITS03 ,
  SUBPARTITION RANGE_LIST_LISP_40_SP40 TABLESPACE ITS04 ,
  SUBPARTITION RANGE_LIST_LISP_40_SP50 TABLESPACE ITS05        
  ),
 PARTITION RANGE_LIST_LISP_50          TABLESPACE ITS01
 (SUBPARTITION RANGE_LIST_LISP_50_SP10 TABLESPACE ITS01 ,
  SUBPARTITION RANGE_LIST_LISP_50_SP20 TABLESPACE ITS02 ,
  SUBPARTITION RANGE_LIST_LISP_50_SP30 TABLESPACE ITS03 ,
  SUBPARTITION RANGE_LIST_LISP_50_SP40 TABLESPACE ITS04 ,
  SUBPARTITION RANGE_LIST_LISP_50_SP50 TABLESPACE ITS05        
  )
);

Note: At this time Oracle has not implemented a SUBPARTITION TEMPLATE clause for the creation of locally partitioned indexes on range-hash or range-list partition tables. This means that you need to type everything out as in the examples in LPI4CPT2_ME.SQL and LPI4CPT3_ME.SQL.
Maintenance of locally partitioned indexes is much easier than the maintenance of globally partitioned indexes. Whenever there is DDL activity on the underlying indexed table Oracle rebuilds the locally partitioned index.
This automatic rebuilding of locally partitioned indexes is one reason why most DBAs prefer locally partitioned indexes.


Globally Partitioned Indexes
There are two types of global indexes, non-partitioned and partitioned. Global non-partitioned indexes are those that are commonly used in OLTP databases.
A global non-partitioned index is essentially one big index on the partitioned table.
Global partitioned indexes are flexible in that the degree of partitioning and the partitioning key are independent from the table's partitioning method. They are commonly used for OLTP environments and offer efficient access to any individual record.
The highest partition of a global index must have a partition bound, all of whose values are MAXVALUE. This ensures that all rows in the underlying table can be represented in the index. Global prefixed indexes can be unique or nonunique.
These indexes can be maintained by appending the clause UPDATE GLOBAL INDEXES to the SQL statements for the operation. The two advantages to maintaining global indexes:
    * The index remains available and online throughout the operation. Hence no other applications are affected by this operation.
    * The index doesn't have to be rebuilt after the operation.

The syntax for a globally non-partitioned index is the exactly same syntax used for a "regular" index on a non-partitioned table. Example:
-- GLOBAL NONPARTITION INDEX ON PARTITION_BY_RANGE
-- (SIMILAR TO REGULAR INDEX USED ON NONPARTITION TABLES) IN MOST OLTP ENVIRONMENTS
CREATE INDEX PARTITION_BY_RANGE_GNPI ON PARTITION_BY_RANGE (LAST_NAME) TABLESPACE ITS01;

The other type of global index is the one that is partitioned.
Globally partitioned indexes at this time can only be ranged partitioned and has similar syntactical structure to that of a range-partitioned table.
A global partitioned index can be based on a different key and even have a different number of partitions.
Note that a globally partitioned index can be applied to any type of partitioned table. Each partition of the globally partitioned index can and may refer to one or more partitions at the table level. Example:
-- GLOBAL PARTITION INDEX ON PARTITION_BY_RANGE
CREATE INDEX PARTITION_BY_RANGE_GPI ON PARTITION_BY_RANGE (BIRTH_YYYY, BIRTH_MM, BIRTH_DD)
GLOBAL PARTITION BY RANGE (BIRTH_YYYY, BIRTH_MM, BIRTH_DD)
(PARTITION DOBS_IN_1971_OR_BEFORE_GPI VALUES LESS THAN (1972, 01 ,01)                    TABLESPACE ITS01,
 PARTITION DOBS_IN_1972_GPI           VALUES LESS THAN (1973, 01 ,01)                    TABLESPACE ITS02,
 PARTITION DOBS_IN_1973_GPI           VALUES LESS THAN (1974, 01 ,01)                    TABLESPACE ITS03,
 PARTITION DOBS_IN_1974_GPI           VALUES LESS THAN (1975, 01 ,01)                    TABLESPACE ITS04,
 PARTITION DOBS_IN_1975_OR_LATER_GPI  VALUES LESS THAN (MAXVALUE, MAXVALUE, MAXVALUE)   TABLESPACE ITS05);

The maintenance on globally partitioned indexes is a little bit more involved compared to the maintenance on locally partitioned indexes. Global indexes need to be rebuilt when there is DDL activity on the underlying table. The reason why they must be rebuilt is that DDL activity often causes the global indexes to be usually marked as UNUSABLE. To correct this problem there are two options to choose from:
- Use ALTER INDEX <index_name> REBUILD;
- Or use UPDATE GLOBAL INDEX clause when using ALTER TABLE.

The syntax for the ALTER INDEX statement is relatively straightforward so we will only focus on the UPDATE GLOBAL INDEX clause of the ALTER TABLE statement. The UPDATE GLOBAL INDEX is between the partition specification and the parallel clause. The partition specification can be any of the following:
- ADD PARTITION | SUBPARTITION (hash only)
- COALESCE PARTITION | SUBPARTITION
- DROP PARTITION
- EXCHANGE PARTITION | SUBPARTITION
- MERGE PARTITION
- MOVE PARTITION | SUBPARTITION
- SPLIT PARTITION
- TUNCATE PARTITION | SUBPARTITION

For example:
ALTER TABLE <TABLE_NAME>
<PARTITION SPECIFICATION>
UPDATE GLOBAL INDEX
PARALLEL (DEGREE #)

NOTE!!! Unless you specify UPDATE INDEXES, any global indexes are marked UNUSABLE and must be rebuilt when you TRUNCATE or SPLIT a partition.


More Examples:
alter table mypart drop partition p1 update indexes;
alter table mypart drop partition p1 update global indexes;

Global-vs-Local Index
• Whenever possible, use local index
• In Primary Key (or Unique) Indexes:
    – If part column is a part of the PK – local is possible and should be used
    – e.g. TXN table. PK – (TXN_DT, TXN_ID) and part key is (TXN_DT)
• If not, try to include the column in PKs
    – E.g. if TXN_ID was the PK of TXN, can you make it (TXN_DT, TXN_ID)?
• Ask some hard design questions
    – Do you really need a PK constraint in the DW?



When to Use Which Partitioning Method
There are five different table partitioning methods (range, hash, list, range-hash and range-list) and three for indexes (global non-partitioned, global partitioned and locally partitioned). So, the obvious question is: "When do I use which combination of table and index partitioning?" There is no concrete answer for that question. However, here are some general guidelines on mixing and matching table and index partitioning.
- First determine if you need to partition the table.
- Next decide which table partitioning method is right for your situation.
- Determine how volatile the data is.
        o    How often are there inserts, updates and deletes?
- Choose your indexing strategy: global or local partitioned indexes.
        o    Each type has its own maintenance consideration.

In identifying tables which would benefit from partitioning, consider the following:
- Choose very large tables which grow rapidly, become fragmented quickly, and present maintenance challenges which could be alleviated by separate partition maintenance
- Tables which have new data loaded regularly but are static thereafter
- Summary tables, historical tables used in Decision Support Systems
- Tables with data which has a logical partition column (date, code, type, etc)

When to Use the Range Partitioning Method
Use range partitioning to map rows to partitions based on ranges of column values. This type of partitioning is useful when dealing with data that has logical ranges into which it can be distributed; for example, months of the year. Range partitioning is a convenient method for partitioning historical data. Performance is best when the data evenly distributes across the range. If partitioning by range causes partitions to vary dramatically in size because of unequal distribution, you may want to consider one of the other methods of partitioning.
When creating range partitions, you must specify:

When to Use the Hash Partitioning Method
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. Creating and using hash partitions gives you a highly tunable method of data placement, because you can influence availability and performance by spreading these evenly sized partitions across I/O devices (striping).

To create hash partitions you specify the following:

The following example creates a hash-partitioned table. The partitioning column is id, four partitions are created and assigned system generated names, and they are placed in four named tablespaces (gear1, gear2, ...).

CREATE TABLE scubagear
     (id NUMBER,
      name VARCHAR2 (60))
   PARTITION BY HASH (id)
   PARTITIONS 4 
   STORE IN (gear1, gear2, gear3, gear4);

When to Use the List Partitioning Method
se 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. The list partitioning method is specifically designed for modeling data distributions that follow discrete values. This cannot be easily done by range or hash partitioning because:

Further, list partitioning allows unordered and unrelated sets of data to be grouped and organized together very naturally.

Unlike the range and hash partitioning methods, multi-column partitioning is not supported for list partitioning. If a table is partitioned by list, the partitioning key can consist only of a single column of the table. Otherwise all columns that can be partitioned by the range or hash methods can be partitioned by the list partitioning method. When creating list partitions, you must specify:

When to Use the Composite Range-Hash Partitioning Method
Range-hash partitioning partitions data using the range method, and within each partition, subpartitions it using the hash method. These composite partitions are ideal for both historical data and striping, and provide improved manageability of range partitioning and data placement, as well as the parallelism advantages of hash partitioning.

When creating range-hash partitions, you specify the following:

The following statement creates a range-hash 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 (ts1, ...,ts4).

CREATE TABLE scubagear (equipno NUMBER, equipname VARCHAR(32), price NUMBER)
  PARTITION BY RANGE (equipno) SUBPARTITION BY HASH(equipname)
    SUBPARTITIONS 8 STORE IN (ts1, ts2, ts3, ts4)
      (PARTITION p1 VALUES LESS THAN (1000),
       PARTITION p2 VALUES LESS THAN (2000),
       PARTITION p3 VALUES LESS THAN (MAXVALUE));

The partitions of a range-hash partitioned table are logical structures only, as their data is stored in the segments of their subpartitions. As with partitions, these subpartitions share the same logical attributes. Unlike range partitions in a range-partitioned table, the subpartitions cannot have different physical attributes from the owning partition, although they are not required to reside in the same tablespace.

When to Use the Composite Range-List Partitioning Method
Like the composite range-hash partitioning method, the composite range-list partitioning method provides for partitioning based on a two level hierarchy. The first level of partitioning is based on a range of values, as for range partitioning; the second level is based on discrete values, as for list partitioning. This form of composite partitioning is well suited for historical data, but allows you to further group the rows of data based on unordered or unrelated column values.

When creating range-list partitions, you specify the following:


Managing Partitions

* Delete a tablespace with partitions:
alter table xxx truncate partition part_name
alter table xxx drop partition part_name
drop tablespace xxx

* Rename a Partition:
alter table TEST_PRT rename partition OLD_NAME to NEW_NAME;

* Selecting from a Partition
select * from xxx partition (part_name)

* Moving Partitions
Alter table xxx move partition part_name tablespace tbscpc_name

* Adding Partitions in the middle
Alter table xxx split partition part_name

* Split Partitions
Alter table xxx split partition part_vieja as (values) into (partition new_name, partition new_name);
 
* Convert a Partition in a full table
alter table xx exchange partition part_name with table_new_no_part;

* The views USER_TAB_PARTITIONS and USER_IND_PARTITIONS will show the range of partitions

* To Analyze a partition table or a partition index::
analyze table xxx partition (part_name) compute statistics;
analyze index xxx partitio (part_name)compute;

* It's better to analyze the tables by partitions

* To import/export partition you should use table_name:part_name

* See Data Dictionary
select SUBSTR(PARTITION_NAME,1,30) PART_NAME, NUM_ROWS,  SUBSTR(tablespace_name,1,40) TABLESPACE, high_value
  from user_tab_partitions
  where table_name= 'table_name';

* Some operations will make the INDEX UNUSABLE (IU)
   1. IMPORT PARTITION or conventional path SQL*Loader
   2. Direct-path SQL*Loader leaves affected local index partitions and  global indexes in an IU state if it does not complete successfully
   3. Partition maintenance operations like ALTER TABLE MOVE PARTITION
   4. Partition maintenance operations like ALTER TABLE TRUNCATE PARTITION
   5. Partition maintenance operations like ALTER TABLE SPLIT PARTITION
   6. Index maintenance operations like ALTER INDEX SPLIT PARTITION
Each of these operations may cause index partitions to be marked IU which will  require the index partitions to be rebuilt.


Partitioning an Existing Table using EXCHANGE PARTITION

This article presents a simple method for partitioning an existing table using the EXCHANGE PARTITION syntax.
Create a Sample Schema

-- Create and populate a small lookup table.
CREATE TABLE lookup (
  id            NUMBER(10),
  description   VARCHAR2(50)
);

ALTER TABLE lookup ADD (CONSTRAINT lookup_pk PRIMARY KEY (id));

INSERT INTO lookup (id, description) VALUES (1, 'ONE');
INSERT INTO lookup (id, description) VALUES (2, 'TWO');
INSERT INTO lookup (id, description) VALUES (3, 'THREE');
COMMIT;

-- Create and populate a larger table that we will later partition.
CREATE TABLE big_table (
  id            NUMBER(10),
  created_date  DATE,
  lookup_id     NUMBER(10),
  data          VARCHAR2(50)
);

DECLARE
  l_lookup_id    lookup.id%TYPE;
  l_create_date  DATE;
BEGIN
  FOR i IN 1 .. 1000000 LOOP
    IF MOD(i, 3) = 0 THEN
      l_create_date := ADD_MONTHS(SYSDATE, -24);
      l_lookup_id   := 2;
    ELSIF MOD(i, 2) = 0 THEN
      l_create_date := ADD_MONTHS(SYSDATE, -12);
      l_lookup_id   := 1;
    ELSE
      l_create_date := SYSDATE;
      l_lookup_id   := 3;
    END IF;
    
    INSERT INTO big_table (id, created_date, lookup_id, data)
    VALUES (i, l_create_date, l_lookup_id, 'This is some data for ' || i);
  END LOOP;
  COMMIT;
END;
/

-- Apply some constraints to the table.
ALTER TABLE big_table ADD (CONSTRAINT big_table_pk PRIMARY KEY (id));

CREATE INDEX bita_created_date_i ON big_table(created_date);

CREATE INDEX bita_look_fk_i ON big_table(lookup_id);

ALTER TABLE big_table ADD (CONSTRAINT bita_look_fk FOREIGN KEY (lookup_id) REFERENCES lookup(id));

-- Gather statistics on the schema objects
EXEC DBMS_STATS.gather_table_stats(USER, 'LOOKUP', cascade => TRUE);
EXEC DBMS_STATS.gather_table_stats(USER, 'BIG_TABLE', cascade => TRUE);

Create a Partitioned Destination Table
Next we create a new table with the appropriate partition structure to act as the destination table. The destination must have the same constraints and indexes defined.

-- Create partitioned table.
CREATE TABLE big_table2 (
  id            NUMBER(10),
  created_date  DATE,
  lookup_id     NUMBER(10),
  data          VARCHAR2(50)
)
PARTITION BY RANGE (created_date)
(PARTITION big_table_2007 VALUES LESS THAN (MAXVALUE));

-- Add new keys, FKs and triggers.
ALTER TABLE big_table2 ADD (CONSTRAINT big_table_pk2 PRIMARY KEY (id));

CREATE INDEX bita_created_date_i2 ON big_table2(created_date) LOCAL;

CREATE INDEX bita_look_fk_i2 ON big_table2(lookup_id) LOCAL;

ALTER TABLE big_table2 ADD (CONSTRAINT bita_look_fk2 FOREIGN KEY (lookup_id) REFERENCES lookup(id));


With this destination table in place we can start the conversion.

EXCHANGE PARTITION
We now switch the segments associated with the source table and the partition in the destination table using the EXCHANGE PARTITION syntax.

ALTER TABLE big_table2
  EXCHANGE PARTITION big_table_2007
  WITH TABLE big_table
  WITHOUT VALIDATION
  UPDATE GLOBAL INDEXES;

The exchange operation should not be affected by the size of the segments involved.
Once this is complete we can drop the old table and rename the new table and all it's constraints.

DROP TABLE big_table;
RENAME big_table2 TO big_table;

ALTER TABLE big_table RENAME CONSTRAINT big_table_pk2 TO big_table_pk;
ALTER TABLE big_table RENAME CONSTRAINT bita_look_fk2 TO bita_look_fk;
ALTER INDEX big_table_pk2 RENAME TO big_table_pk;
ALTER INDEX bita_look_fk_i2 RENAME TO bita_look_fk_i;
ALTER INDEX bita_created_date_i2 RENAME TO bita_created_date_i;

SPLIT PARTITION
Next, we split the single large partition into smaller partitions as required. Start with the lowest value FIRST!!

ALTER TABLE big_table
  SPLIT PARTITION big_table_2007 AT (TO_DATE('31-DEC-2005 23:59:59', 'DD-MON-YYYY HH24:MI:SS'))
  INTO (PARTITION big_table_2005,
        PARTITION big_table_2007)
  UPDATE GLOBAL INDEXES;

ALTER TABLE big_table
  SPLIT PARTITION big_table_2007 AT (TO_DATE('31-DEC-2006 23:59:59', 'DD-MON-YYYY HH24:MI:SS'))
  INTO (PARTITION big_table_2006,
        PARTITION big_table_2007)
  UPDATE GLOBAL INDEXES;

EXEC DBMS_STATS.gather_table_stats(USER, 'BIG_TABLE', cascade => TRUE);

The following queries show that the partitioning was successful.

SELECT partitioned
  FROM user_tables
  WHERE table_name = 'BIG_TABLE';

PAR
---
YES


SELECT partition_name, num_rows
  FROM user_tab_partitions
  WHERE table_name = 'BIG_TABLE';

PARTITION_NAME                   NUM_ROWS
------------------------------ ----------
BIG_TABLE_2005                     335326
BIG_TABLE_2006                     332730
BIG_TABLE_2007                     334340

Referential Partitioning
Let's say that you want to partition the CUSTOMERS table over the ACC_REP column, but that column is not not present on child tables. Before 11g you had to add the column to all tables and update it.

Table CUSTOMERS            Table SALES
CUST_ID        <------|    SALES_ID
ACC_REP               |--  CUST_ID  (FK)
                           TOT_AMT


Now 11g has referential partitioning.

Partition CUSTOMERS as usual
create table SALES (
SALES_ID number not null,
CUST_ID number not null,
TOT_AMT number
constraint fk_sales_01
foreign key (cust_id)
references customers)
partition by reference (fk_sales_01);

Partitions of SALES are created with data from CUSTOMERS.


Good Information an Examples:
https://www.morganslibrary.org/reference/partitions.html