Use and Management of Oracle partition tables

Document directory

• Range Partition Table
• List Partition Table
• Hash Partition Table
• Combined Partition Table
• Merge parent partitions
• Merge subpartitions

1. Create and use a partition table

Oracle partition tables are divided into four types: range partition tables, list partition tables, hash partition tables, and combination partition tables.

Range Partition Table

Create a table partitioned by the field data range. The partitions are placed in different specified tablespaces.

 

Sample Code:

-- Prepare independent tablespaces for each partition

Create tablespace test_space01 datafile 'd:/tbs01.dbf' size 50 m

Create tablespace test_space02 datafile 'd:/tbs02.dbf 'size 50 m

Create tablespace test_space03 datafile 'd:/tbs03.dbf' size 50 m

Create tablespace test_space04 datafile 'd:/tbs04.dbf 'size 50 m

 

-- Create a 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-01 00:00:00 ', 'yyyy-mm-dd hh24: MI: ss') tablespacetest_space01,

Partition part02 values less than (to_date ('2017-08-01 00:00:00 ', 'yyyy-mm-dd hh24: MI: ss') tablespace test_space02,

Partition part03 values less than (to_date ('2017-09-01 00:00:00 ', 'yyyy-mm-dd hh24: MI: ss') tablespace test_space03,

Partition part04 values less than (maxvalue) tablespace test_space04

);

 

-- Insert Test Data

Insert into range_examplevalues (to_date ('1970-06-10 00:00:00 ', 'yyyy-mm-dd hh24: MI: ss'), '123', 1 );

Insert into range_examplevalues (to_date ('1970-07-20 00:00:00 ', 'yyyy-mm-dd hh24: MI: ss'), '123', 2 );

Insert into range_examplevalues (to_date ('1970-08-25 00:00:00 ', 'yyyy-mm-dd hh24: MI: ss'), '123', 3 );

Commit;

 

-- Query a table

Select*FromRange_example;

 

-- Execute a query on the table Partition

Select*FromRange_examplePartition(Part01 );

List Partition Table

Create a table with fixed enumerated values partitioned by the field data list. The value of the insert Record Partition field must be in the list; otherwise, it cannot be inserted.

Sample Code:

Create Table list_example (

Dnamevarchar2 (10 ),

Data varchar2 (20)

)

Partition by list (dname)

(

Partition part01values ('initial registration', 'transfer registration '),

Partition part02 values ('name change registration', 'House change '),

Partition part03 values ('mortgage registration '),

Partition part03 values ('restrict registration ')

);

Hash Partition Table

Create a table partitioned by hash value of field data

Sample Code:

Create Table hash_example (

Hash_key_column date,

Data varchar2 (20)

)

Partition by hash (hash_key_cloumn)

(

Partition part01,

Partition part02

);

Combined Partition Table

You can create a subpartition in the partition to implement the partition combination. You can combine all the preceding partitions as needed.

In this example, a table is created to implement partitions by combining range partitions and hash partitions.

Sample Code:

Create Table range_hash_example (

Range_column_key int,

Hash_column_key int,

Datavarchar2 (20)

)

Partition by range (range_column_key)

Subpartition by hash (hash_column_key) subpartitions 2

(

Partition part_1 values less than (100000000)

(

Subpartition part_partition sub_1,

Subpartition part_1_sub_2,

Subpartition part_1_sub_3

),

Part_2 values less than (200000000)

(

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. 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 Table partitions

-- Range partitioned table

Alter table range_example add partitionpart04 values less than (to_date ('2017-10-1 00:00:00 ', 'yyyy-mm-ddhh24: MI: ss '));

 

-- List partitioned table

Alter table list_example add partitionpart04 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 partitionpart03;

 

-- Added subpartition.

Alter table range_hash_example modify partitionpart_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.

3. delete a partition

Alter table... drop partition part_name;

4. merge partitions and parent partitions

Alter table range_example merge partitionspart01_1, part01_2 into partition part01 update indexes;

-- If the update indexes clause is omitted, the index of the affected partition must be rebuilt;

Alter table range_example modify partitionpart02 rebuild unusable local indexes;

Merge subpartitions

Alter table composite_example

Merge subpartitions part_1_sub_2, part_1_sub_3into subpartition part_1_sub_2 update indexes;

5. Partition Conversion

You can convert a partitioned table into a non-partitioned table, or between several different partitioned tables.

As follows:

Create Table hash_part02 as select * fromhash_example where 1 = 2;

Alter table hash_example exchange partitionpart02 with table hash_part02;

In this case, the part02 partition data in the hash_example partition table will be transferred to the hash_part02 non-partition table.

6. Partition tables and Indexes

You can create three types of indexes on a partitioned table: 1. Global indexes are the same as normal tables; 2. Global partitions; 3. Local partition indexes.

The differences between them are as follows:

 

The following uses the range_example table as an example.

1. Create a Common Index

Create index com_index_range_example_id onrange_example (ID );

 

2. Create a local partition Index

Create index local_index_range_example_id onrange_example (ID) local;

 

3. Create a global partition Index

Create index gidx_range_exampel_id onrange_example (ID)

Global partition by range (ID)

(

Part_01 values less than (1000 ),

Part_02 values less than (maxvalue)

);

 

To delete a partition index, you cannot specify a partition name for the local index.

The partition corresponding to the local index is deleted along with the deletion of the Data Partition. Globalpartition index can specify a partition name to delete a partition. Note that if the partition is not empty, the index partition at the higher level will be set to unusable.

Alter index gidx_range_exampel_id drop partition part_01; this statement will cause the status of part_02 to unusable