Create and manage Oracle Partition tables

Create and manage Oracle Partition tables

I. Create a partition table

Partition tables are divided into four types: 1. Range Partition Table 2. List Partition Table 3. Hash Partition Table 4. Combined Partition Table

The following table creates four types of partition tables.

1. Range Partition Table

Create table range_ Example ( Range_key_column DATE, DATA VARCHAR2 (20 ), ID integer ) Partition by range (range_key_column) ( PARTITION part01 values less than (TO_DATE ('2017-07-1 00:00:00 ', 'yyyy-mm-dd hh24: mi: ss') TABLESPACE tbs01, PARTITION part02 values less than (TO_DATE ('2017-08-1 00:00:00 ', 'yyyy-mm-dd hh24: mi: ss') TABLESPACE tbs02, PARTITION part03 values less than (TO_DATE ('2017-09-1 00:00:00 ', 'yyyy-mm-dd hh24: mi: ss') TABLESPACE tbs03 );

2. List partition tables

Create table list_example ( Dname VARCHAR2 (10 ), DATA VARCHAR2 (20) ) Partition by list (dname) ( PARTITION part01 VALUES ('me', 'pe', 'qc, 'RD '), PARTITION part02 VALUES ('smt ', 'sale ') );

3. Hash Partition Table

Create table hash_example ( Hash_key_column DATE, DATA VARCHAR2 (20) ) Partition by hash (hash_key_cloumn) ( PARTITION part01, PARTITION part02 );

4. Combining Partitioned Tables

Create table range_hash_example ( Range_column_key DATE, Hash_column_key INT, DATA VARCHAR2 (20) ) Partition by range (range_column_key) Subpartition by hash (hash_column_key) SUBPARTITIONS 2 ( PARTITION part_1 values less than (TO_DATE ('2017-08-01 ', 'yyyy-mm-dd '))( SUBPARTITION part_partition sub_1, SUBPARTITION part_1_sub_2, SUBPARTITION part_1_sub_3 ), PARTITION part_2 values less than (TO_DATE ('2017-09-01 ', 'yyyy-mm-dd '))( SUBPARTITION part_2_sub_1, SUBPARTITION part_2_sub_2 ) );

-- Note that subpartitions 2 does not specify that the number of subpartition must be 2. In fact, the number of subpartitions in each partition can be different. What is the role of the subpartitions keyword? If you do not specify the details of subpartition, the system generates a subpartition based on the number of subpartition specified by the value of subpartitions. The name is defined by the system.

2. Add partitions

-- Range partitioned table Alter table range_example add partition part04 values less than (TO_DATE ('2017-10-1 00:00:00 ', 'yyyy-mm-dd hh24: mi: ss ')); -- List partitioned table Alter table list_example add partition part04 VALUES ('te '); -- Adding Values for a List Partition Alter table list_example modify partition part04 add values ('mis '); -- Dropping Values from a List Partition Alter table list_example modify partition part04 drop values ('mis '); -- Hash partitioned table Alter table hash_example add partition part03; -- Added subpartition. Alter table range_hash_example modify partition part_1 add subpartition part_1_sub_4;

Note: When a new partition is added to a hash partitioned table, all data in the existing table recalculates the hash value and re-allocates it to the partition. Therefore, you need to rebuild the indexes of the re-allocated partition.

1 2 Next Page