Go Creation and management of Oracle partitioned tables and indexes

Today we used the partition of Oracle table, and by the way I wrote a few examples to say the partition of this table:

First, create a partitioned table

1. Range Partitioning

Partitioning based on the range of data table field values

For example, partition the score table based on the students ' scores and create a partition table as follows:

CREATE TABLE Range_fraction  (  ID Number (8),  name varchar2,  fraction number (3),  grade number ( 2)) partition by range (fraction) (  partition fraction_60 values less than (60),--Partition fraction_80  values Less than (85),--pass  partition fraction_100 values less than (MaxValue)--Excellent)

Add some data to the table when you are finished creating the partition table:

DECLARE   name     VARCHAR2 (ten);  Fraction number (5);  Grade number    (5);  I number        (8): =1;begin for  i in 1..100000 LOOP  SELECT CHR (ROUND (dbms_random. VALUE (122))) into the NAME from DUAL;  SELECT ABS (MOD (dbms_random. RANDOM,101)) into the 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 table:

--Search for all, failed, Medium, excellent grades, 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 our query statement does not specify a partition, if the partition field appears after the Where condition, Oracle automatically scans the response partition based on the range of field values:

SELECT * FROM Range_fraction where fraction<30; This SQL execution will only scan for failed partitions.

SELECT * FROM Range_fraction where fraction<80; This SQL execution will scan for a failed and medium two partition

2. Hash partition

In a range partition, a contiguous value for a partitioned field typically occurs within a partition, whereas in a hash partition, continuous field values are not necessarily stored in the same partition. Hash partitioning distributes records across more partitions than the range partition, which reduces the likelihood of I/O contention.

In order to create a hash partition, the partition by hash statement should be used instead of the partition by range clause, as follows:

The first one specifies a different tablespace for each partition, and the number of tablespaces is not equal to the number of partitions, and the table spaces are recycled when the number of tablespaces is greater than the number of partitions:

CREATE TABLE Range_fraction1  (  ID Number (8),  name varchar2,  fraction number (3),  grade number (2)) Partition by hash (fraction) partitions 8store in (Users,tbs_haicheng)

The second is to specify a partition name for each partition and specify a tablespace for it:

CREATE TABLE Range_fraction1  (  ID Number (8),  name varchar2,  fraction number (3),  grade Number (1)) partition by hash (fraction) (   partition P1 tablespace Tbs_haicheng,   partition P2 tablespace users);

3. List partition

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

We put the 1, 2, 3, 4 class data in a partition, put 6, 7, 8 of the data in a partition, and then put the other in a partition, the table is as follows:

CREATE TABLE Range_fraction1  (  ID Number (8),  name varchar2,  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. Combining partitions (creating sub-partitions)

A partition that is partitioned. For example, you can partition a range first, and then create a list partition for each range partition.

For very large tables, this combination partitioning is an effective way to divide data into manageable and resizable components.

For example, partition the ID hash after partitioning by a fraction range:

CREATE TABLE Range_fraction1  (  ID Number (8),  name varchar2,  fraction number (3),  grade number (1)) Partition by range (fraction) Subpartition by hash (ID) subpartitions 4 (  partition fraction_60 values less than (60),-- Failed  partition fraction_80 values less than (85),--pass  partition fraction_100 values than (MaxValue)--Excellent)

Second, index partition

There are three types of indexes that can be built on partitioned tables: 1 as global indexes as normal tables, 2. Global Partition index; 3. Local Partition Index

1. Create a normal 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, where 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 (+), part_02 values less than (MAXVALUE));

Iii. Management of partitioned tables

1. Add partition

For a range partition, to add a partition, the partition must be delineated more than the original maximum limit, that is, only up and down, not to add. It is not possible to increase the partition for a range partition created with the MaxValue keyword

Example:

CREATE TABLE Range_fraction  (  ID Number (8),  name varchar2,  fraction number (3),  grade number ( 2)) partition by range (fraction) (  partition fraction_60 values less than (40),--Partition fraction_80  values Less than (60),--pass  partition fraction_100 values less than (80)--excellent)

For this partition we add a partition:

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

To add a partition to the list partition:

CREATE TABLE Range_fraction  (  ID Number (8),  name varchar2,  fraction number (3),  grade number ( 2)) partition by list (grade) (   partition P1 values (all in all) tablespace Tbs_haicheng,   partition P2 values (4,5,6) Tablespace users);

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

We'll add two additional table partition values for the P3 partition:

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

Then delete 10 of the table partition value of the P3 partition:

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

To add a sub-partition to a hash partition:

ALTER TABLE TABLENAME ADD PARTITION partname;

To add a sub-partition format:

ALTER TABLE TABLENAME MODIFY PARTITION partname ADD subpartition subpartname;

2. Delete a partition

Deleting a partition is simple and has the following format:

ALTER TABLE ... DROP PARTITION Part_name;

3. Partition merging

Merge parent partition format:

ALTER TABLE TABLENAME MERGE partitions p1-1, p1-2 into PARTITION p1 UPDATE INDEXES;

If you omit update INDEXES, you need to rebuild the index for the affected partition

Merge sub-partition formats:

ALTER TABLE tablenamemerge subpartitions part_1_sub_2, part_1_sub_3 into subpartition part_1_sub_2 UPDATE INDEXES;

4. Converting partitions

You can convert a partitioned table to a non-partitioned table, or to a conversion 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;

At this point, the data for the part02 partition in the partition table Hash_example will be transferred to the HASH_PART02 non-partitioned table.

Go Creation and management of Oracle partitioned tables and indexes