Using Partition -2

[debasisdas]

SAMPLE CODE TO CREATE SUB PARTITIONS
=============== =============== =========
RANGE-HASH-9i
-------------------------

CREATE TABLE SUBPART
(
ID NUMBER(10) PRIMARY KEY,
NAME VARCHAR2(20)
)
PARTITION BY RANGE(ID)
SUBPARTITION BY HASH(NAME)
SUBPARTITIONS 3
(
PARTITION P1 VALUES LESS THAN(10),
PARTITION P2 VALUES LESS THAN(20),
PARTITION P3 VALUES LESS THAN(MAXVALUE)
);
 

in this case external partiton is by RANGE
and the internal (sub) partiton is by HASH

In the above example it creates 3 sub-partitions with in each partitons.


SAMPLE CODE TO CREATE SUB PARTITIONS
=============== =============== =========
RANGE-LIST-as per 10g
------------------------------------

CREATE TABLE RL
(
ID NUMBER(10) PRIMARY KEY,
NAME VARCHAR2(20)
)
PARTITION BY RANGE(ID)
SUBPARTITION BY LIST(NAME)
(
PARTITION P1 VALUES LESS THAN(10)
(
SUBPARTITION PP1 VALUES ('A','B'),
SUBPARTITION PP2 VALUES ('C','D')
),
PARTITION P2 VALUES LESS THAN(20)
(
SUBPARTITION PP3 VALUES ('A','B'),
SUBPARTITION PP4 VALUES ('C','D')
)
)
 

Try this code only in release 10.

In this case the main partition is by RANGE and the sub-partition is by LIST

Spliting Existing Partition
-------------------------------------------------

Alter Table <t.n.> Split Partition P1 At (10) Into 
(partition P3,partition P4);
 

Merging Existing Partitions
-------------------------------------------------------

Alter Table <t.n.>
Merge Partitions P1,p2 Into Partition P3;
 

------------------
Exchanging Partitions
-------------------------------------------

Alter Table <t.n.>
Exchange Partition P1 With Table<t.n.>including/excluding Indexes With/without Validation
 

restrictions For Partitioned Table
=============== =============== ==

1.partitions Can Be Merged Or Splitted Only To Same Type.

2.partition Can't Be Added To A Non-partitioned Table.

3.if A Partition Is Dropped It Will Be Dropped Along With All Its Records.

4.the Last Existing Partition Can't Be Removed From A Partitioned Table. For That Drop The Table Itself.

5.if Maxvalue Is Specified In The Last Partition At The Time Of Table Creation, Another Partition Can't Be Added To The Table Having Range More That The Last Existing Partition.

6.if A Table Is Created By Copying The Structure Of The Table,the Partitions Of The Source Table Will Not Be Copied To The New Table.