Create and manage Oracle partition tables and Indexes

I used Oracle table partitions today. Let's write a few examples to explain the partitions in this table:

I. Create a partition table

1. Range partitioning

Partitions Based on the range of data table Field Values

For example, you can partition a score table based on different scores of students and create a partition table as follows:

Create table range_fraction (id number (8), name varchar2 (20), fraction number (3), grade number (2) partition by range (fraction) (partition fraction_60 values less than (60), -- fail partition fraction_80 values less than (85), -- Pass partition fraction_100 values less than (maxvalue) -- excellent)

After creating a partition table, add some data to the table:

declare   name     varchar2(10);  fraction number(5);  grade    number(5);  i        number(8):=1;begin  for i in 1..100000 LOOP  SELECT CHR (ROUND (DBMS_RANDOM.VALUE (97, 122))) INTO NAME FROM DUAL;  SELECT ABS(MOD(DBMS_RANDOM.RANDOM,101)) into fraction FROM DUAL;  SELECT ABS(MOD(DBMS_RANDOM.RANDOM,10))+1 into grade FROM DUAL;  insert into range_fraction values(seq_range_fraction.nextval ,name,fraction,grade);  END LOOP;end;

Query partition tables:

-- Query all, fail, medium, and excellent results respectively. select * from range_fraction; select * from range_fraction partition (fraction_60); select * from range_fraction partition (fraction_80 ); select * from range_fraction partition (fraction_100 );

When the query statement does not specify a partition, if the partition field appears in the where condition, Oracle will automatically scan the response partition based on the range of the field value:

Select * from range_fraction where fraction <30; when this SQL statement is executed, only fail partitions are scanned.

Select * from range_fraction where fraction <80; when this SQL statement is executed, the system scans for less than two partitions and the middle partitions.

2. Hash partitions

In a range partition, the continuous values of partition fields usually appear in one partition. In a hash partition, continuous field values are not necessarily stored in the same partition. Hash partitions distribute records in more partitions than range partitions, which reduces the possibility of I/O contention.

To create a hash partition, replace the partition by range clause with the partition by hash statement, as shown below:

The first option is to specify different tablespaces for each partition. The number of tablespaces does not need to be equal to the number of partitions. When the number of tablespaces exceeds the number of partitions, they are repeatedly written to each tablespace:

create table range_fraction1  (  id number(8),  name varchar2(20),  fraction number(3),  grade number(2))partition by hash(fraction)partitions 8store in (users,tbs_haicheng)

Second, specify a partition name and a tablespace for each partition:

create table range_fraction1  (  id number(8),  name varchar2(20),  fraction number(3),  grade number(1))partition by hash(fraction)(   partition p1 tablespace tbs_haicheng ,   partition p2 tablespace users);

3. List partitions

You can also use list partitions instead of range partitions and hash partitions. In the list partition, tell Oracle all possible values, and specify the partition where the corresponding row should be inserted.

We put the data of Class 1, class 2, Class 3, and Class 4 in one partition, data of Class 6, Class 7, and Class 4 in one partition, and other data in another partition. The table creation is as follows:

create table range_fraction1  (  id number(8),  name varchar2(20),  fraction number(3),  grade number(2))partition by list(grade)(   partition p1 values(1,2,3,4) tablespace tbs_haicheng ,   partition p2 values(5,6,7,8) tablespace users,

   partition p3 values(default)

);

4. Combine partitions (create subpartitions)

That is, the partition. For example, you can create a list partition for each range partition first.

For a very large table, this combined partition is an effective way to divide the data into manageable and adjustable components.

For example, partition by score range before partitioning by ID hash:

Create table range_fraction1 (id number (8), name varchar2 (20), fraction number (3), grade number (1) partition by range (fraction) subpartition by hash (id) subpartitions 4 (partition fraction_60 values less than (60), -- fail partition fraction_80 values less than (85), -- Pass partition fraction_100 values less than (maxvalue) -- excellent)

Ii. Index partitioning

Three types of indexes can be created for a partitioned table: 1. Global indexes are the same as normal tables; 2. Global partition indexes; 3. Local partition Indexes

1. Create a Common Index

create index index_fraction on range_fraction(fraction);

2. Create a local partition index (that is, an index partition can only correspond to one table partition)

create index  local_index_fraction on range_fraction(fraction) local;

3. Create a global partition index (a hash index Partition means that an index partition may point to multiple table partitions)

create index global_index_fraction on range_fraction(fraction)GLOBAL partition by  range(fraction)( part_01 values less than(1000), part_02 values less than(MAXVALUE));

3. Manage partition tables

1. Add partitions

For a range partition, to add a partition, the boundary defined by the partition must be higher than the original maximum boundary. That is to say, the partition can only be added up, but not down. The range partition created with the maxvalue keyword cannot be added.

Example:

Create table range_fraction (id number (8), name varchar2 (20), fraction number (3), grade number (2) partition by range (fraction) (partition fraction_60 values less than (40), -- fail partition fraction_80 values less than (60), -- Pass partition fraction_100 values less than (80) -- excellent)

Add a partition for this partition:

ALTER TABLE range_fraction ADD PARTITION fraction_100 VALUES LESS THAN (100);

Add a partition to the list partition:

create table range_fraction  (  id number(8),  name varchar2(20),  fraction number(3),  grade number(2))partition by list(grade)(   partition p1 values(1,2,3) tablespace tbs_haicheng ,   partition p2 values(4,5,6) tablespace users);

ALTER TABLE range_fraction ADD partition p3 VALUES (7,8);

We have added two table partition values for partition p3:

ALTER TABLE range_fraction MODIFY PARTITION p3 ADD VALUES(9,10);

Delete 10 from the table partition value of partition p3:

ALTER TABLE range_fraction MODIFY PARTITION p3 DROP VALUES(10);

Add a subpartition to the hash partition:

ALTER TABLE TABLENAME ADD PARTITION PARTNAME;

Format of adding a subpartition:

ALTER TABLE TABLENAME MODIFY PARTITION PARTNAME ADD SUBPARTITION SUBPARTNAME;

2. delete partitions

It is relatively simple to delete a partition. The format is as follows:

ALTER TABLE ... DROP PARTITION part_name;

3. merge partitions

Format of parent partition merging:

ALTER TABLE TABLENAME MERGE PARTITIONS p1-1, p1-2 INTO PARTITION p1 UPDATE INDEXES;

If update indexes is omitted, re-index the affected partition.

Format of merging subpartitions:

ALTER TABLE TABLENAMEMERGE SUBPARTITIONS part_1_sub_2, part_1_sub_3 INTO SUBPARTITION part_1_sub_2 UPDATE INDEXES; 

4. 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.