Partition Table Problems

Function: distributes data in a large table to multiple table partition segments. different partitions are independent of each other, which improves table availability and performance.
Type: range partition, hash partition (hash algorithm used most often), list partition, range/HASH combination partition, range/list combination Partition

Range Partition Table
Create a range Partition Table
Create Table T (V Number, B number)
Partition by range (V )(
Partition P1 values less than ('11') tablespace test1,
Partition P2 values less than ('21') tablespace Test2 );

Add and delete partitions
# Add partitions
Alter table t add partition P3 values less than ('31') tablespace test3;
Alter table t drop partition p3

Example of a time partition
Alter session set nls_data_lanage = American;
Alter session set nls_data_format = 'dd-MON-YYYY'
Create Table T (v_date date, B number)
Partition by range (v_date )(
Partition P1 values less than ('01-APR-2009 ') tablespace test1,
Partition P2 values less than ('01-JUN-2009 ') tablespace Test2 );

2. Hash Partition Table (most commonly used)
Create
Create Table T1 (V Number, B number)
Partition by hash (V )(
Partition P1 tablespace test1,
Partition P2 tablespace Test2 );

Add Partition
Alter table t add partition P3 tablespace test3;

Delete Partition
Alter table t drop coalesce partition;

3. List partitions
Create list Partition
Create Table T (V varchar2 (10), B number)
Partition by list (V )(
Partition P1 values ('A', 'B') tablespace test1,
Partition P2 values ('C', 'D') tablespace Test2 );

# Insert data
SQL> insert into T values ('A', 10 );
SQL> insert into T values ('D', 20 );

# Note: when inserting data, the first field can only be a, B, c, d. Otherwise, an error is returned.

# Query
Select * from T;
Select * from t partition (P1 );
Select * from t partition (P2 );
Select * from t where V = xxx

Add Partition
Alter table t add partition P3 values ('31', '32') tablespace test3;

Delete Partition
Alter table t drop partition p3

4. Range/HASH combined partitioning
Create hash composite partitions
Create Table T (V Number, B number)
Partition by range (v) subpartition by hash (B)
Subpartitions 2 store in (test1, Test2 )(
Partition P1 values less than ('11 '),
Partition P2 values less than ('21 '));

Query
Select * from T;
Select * from t partition (P1 );

Add primary and subpartitions
Alter table t add partition P3 values less than ('31') tablespace test3;
Alter table t modify partition P3 add subpartition;

Delete Partition
Alter table t coalesce partition;
Alter table t modify partition P1 coalesce subpartition;

5. Range/list combined partitions

Create
Create Table T (V Number, B number)
Partition by range (V)
Subpartition by list (B)
(Partition P1 values less than ('11') tablespace test1 (
Subpartition p1_1 values ('1', '3 '),
Subpartition p1_2 values ('5', '6 ')
),
Partition P2 values less than ('21') tablespace Test2 (
Subpartition p2_1 values ('13', '14 '),
Subpartition p2_2 values ('15', '16 ')
));

Query
Select * from t
Select * from t partition (P1)
Select * from t subpartition (p1_1)
Select segment_name, partition_name, tablespace_name
From user_segments where segment_name = 'T ';

Add partitions and subpartitions
Alter table t add partition P3 values less than ('31') tablespace test3 (
Subpartition p3_1 values ('25', '26 '),
Subpartition p3_2 values ('22', '23 '));
Alter table t modify partition R3 add subpartition r3_3 tablespace test3 values ('28', '29 ');

Delete Partition
Alter table t modify partition P1 coalesce subpartition;

Swap partition data
Alter table t exchange partition P1 with table tt;

Load-disconnected Partition
Alter table t truncate partition P1;

Modify partition name
Alter table t rename partition p2_1 to P2;

Merge partitions
Alter table t merge partitions p1, p2 into partition p01

Reorganizing partitions
Alter table t move partition P1 tablespace test04

Value added to list partitions and subpartitions
Alter table t modify partition P1 add values ('20140901 ');
Alter table t modify subpartition p3_1 add values ('20140901 ');

Delete values from list partitions and subpartitions
Alter table t modify partition P1 drop values ('20140901 ')
Alter table t modify subpartition p3_1 drop values ('20140901 ')

Partition Table information: dba_part_tables
Display partition: dba_tab_partitions
Show sub-partitions: dba_tab_subpartitions
Show partition columns: dba_part_key_columns
Show the subpartition column: dba_subpart_dey_columns
Display partition index: dba_part_indexes
Show index partitions: dba_ind_partitions