Shulou(Shulou.com)06/01 Report--

Oracle partition table Partition Table creation and management is what kind of, I believe that many inexperienced people are helpless about this, this article summarizes the causes of the problem and solutions, through this article I hope you can solve this problem.

1. Create partition table

Partition tables are divided into four categories: 1. Range partition table 2. List partition table 3. Hash partition table 4. Combination partition table

The following four types of partition tables are created.

1. Scope 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('2008-07-1 00:00:00','yyyy-mm-dd hh34:mi:ss')) TABLESPACE tbs01,

PARTITION part02 VALUES LESS THAN (TO_DATE('2008-08-1 00:00:00','yyyy-mm-dd hh34:mi:ss')) TABLESPACE tbs02,

PARTITION part03 VALUES LESS THAN (TO_DATE('2008-09-1 00:00:00','yyyy-mm-dd hh34:mi:ss')) TABLESPACE tbs03

);

2. List partition table

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. Combined partition table

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('2008-08-01','yyyy-mm-dd'))(

SUBPARTITION part_1_sub_1,

SUBPARTITION part_1_sub_2,

SUBPARTITION part_1_sub_3

),

PARTITION part_2 VALUES LESS THAN (TO_DATE('2008-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 subpartitions must be 2. In fact, the number of subpartitions in each partition can be different. What exactly is the function of the subpartitions keyword? If you do not specify the subpartition details, the system generates subpartitions according to the number of subpartitions specified by the value of subpartitions, and the name is defined by the system.

II. Additional subdivisions

-- range partitioned table

ALTER TABLE range_example ADD PARTITION part04 VALUES LESS THAN (TO_DATE('2008-10-1 00:00:00','yyyy-mm-dd hh34: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;

--Increase subpartition

ALTER TABLE range_hash_exampleMODIFYPARTITION

part_1 ADD SUBPARTITION part_1_sub_4;

Note: When a partition is added to a hash partitioned table, all data in the existing table has hash values recalculated and then reallocated to partitions. So the indexes of the reallocated partition need to be rebuilt.

Third, delete the partition

You can drop partitions from range, list, or composite range-list partitioned tables.

ALTER TABLE ... DROP PARTITION part_name;

For hash-partitioned tables, or hash subpartitions of range-hash partitioned tables, you must perform. a coalesce operation instead.

Reduce the number of hash partitions one at a time. You cannot specify the name of a reduced partition.

ALTER TABLE hash_example COALESCE PARTITION ;

--Subpartition syntax for the following

ALTER TABLE divingMODIFYPARTITION us_locations

COALESCE SUBPARTITION;

IV. Consolidation of zones

1. Merge parent partitions

ALTER TABLE range_example

MERGE PARTITIONS part01_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 PARTITION part02 REBUILD UNUSABLE LOCAL INDEXES;

2. merge subpartition

ALTER TABLE composite_example

MERGE SUBPARTITIONS part_1_sub_2,part_1_sub_3 INTO SUBPARTITION part_1_sub_2

UPDATE INDEXES;

Hash partitions or subpartitions cannot be partitioned. If the specified partition contains any data, the corresponding indexes can be identified as UNUSABLE. If it is a generic Heap Table, the new partition indexes are UNUSABLE , and all gloabl indexes are identified as UNUSABLE. If the index of the new partition is UNUSABLE, the global index is still USABLE.

1. Partition of range type partition

ALTER TABLE range_example

SPLIT PARTITION part01

AT (TO_DATE('2008-06-01 00:00:00','yyyy-mm-dd hh34:mi:ss'))

INTO ( PARTITION part01_1,PARTITION part01_2

);

A partition can only be divided into two partitions at a time. The value specified after the at keyword is the range of the first partition. The default is less than.

2. Partition of list type partition

ALTER TABLE list_example

SPLIT PARTITION part01 VALUES('ME','PE')

INTO ( PARTITION part01_1, PARTITION part01_2

);

3. Partition of Range_Hash type partition The new partition will rehash the subpartition of the original partition. If the number of subpartitions is specified in the partition, the subpartition is rehashed according to the new rule; if not specified, the number of original subpartitions is kept unchanged.

ALTER TABLE range_hash_example SPLIT PARTITION part_1

AT (TO_DATE('2008-07-01 00:00:00','yyyy-mm-dd hh34:mi:ss')) INTO (

PARTITION part_1_1 SUBPARTITIONS 2 STORE IN (tbs01,tbs02),

PARTITION part_1_2

);

subpartitions2--Specifies the number of subpartitions for the new partition, the store in clause specifies the tablespace for the subpartition store

Exchanging Partitions

Partitioned tables can be converted to non-partitioned tables, or between several different partitioned tables.

As follows:

CREATE TABLE hash_part02 AS SELECT * FROM hash_example WHERE 1=2;

ALTER TABLE hash_example EXCHANGE PARTITION part02 WITH TABLE hash_part02;

At this time, the partition table hash_example part02 partition data will be transferred to hash_part02 this non-partition table.

VII. INDEX

There are three types of indexes that can be established on partitioned tables: 1. Global indexes that are the same as ordinary tables. 2. Global partition index. Local partition index.

The differences between them are illustrated below.

Take the table range_example as an example.

1. Create a common index

create index com_index_range_example_id on range_example(id);

2. Create local partition index

create index local_index_range_example_id on range_example(id)local;

3. Create global partition index

create index gidx_range_exampel_id on range_example(id)

GLOBAL partition by range(id)

(

part_01 values less than(1000),

part_02 values less than(MAXVALUE)

);

For deletion of partition indexes, local index cannot specify partition names, separate delete partition indexes. The partition corresponding to local index is deleted along with the deletion of data partition.

global partition index can specify the partition name and delete a partition. Note, however, that if this partition is not empty, it causes the index partition at a higher level to be set to UNUSABLE.

ALTER INDEX gidx_range_example_id drop partition part_01 ; This sentence causes part_02 to have a status of UNUSABLE.

After reading the above contents, do you know how to create and manage Oracle Partition Table? If you still want to learn more skills or want to know more related content, welcome to pay attention to the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.