Oracle Base Table Partitioning

First, table partition

　　(i) Classification of table partitions

1. Range partition (range)

2. Hash partition (hash)

3. List partition

4. Compound partition: Range-Hash (range-hash), Range-list (range-list)

　　(b) Scope Zoning:

A range partition is a wide-ranging partitioning method that uses the range of columns as a partitioning criterion to store records in a range partition where column values reside.

Example: Create a table partition by time.

--Creating a partitioned table when you create a tableCREATE TABLEdrawlist (DrawnameVARCHAR2( -), Drawtime DATE not NULL) PARTITION byRange (Drawtime) (--Create a table partition to Drawtime as a partition rangePARTITION part_1VALUESLess THAN (To_date ('2010-1-1','YYYY-MM-DD')),--defines the partitions that were saved for data prior to 2010-1-1, not including 2010-1-1PARTITION part_2VALUESLess THAN (To_date ('2011-1-1','YYYY-MM-DD')),--defining data partitions prior to 2011-1-1PARTITION Part_3VALUESLess THAN (To_date ('2012-1-1','YYYY-MM-DD')), PARTITION Part_4VALUESLess THAN (MaxValue)--Other values Save the partition)--inserting data, the system automatically saves the data to the corresponding partition table. INSERT  intodrawlistSELECT 'AAA', To_date ('2009-10-20','YYYY-MM-DD') fromDualUNION SELECT 'BBB', To_date ('2009-11-20','YYYY-MM-DD') fromDualUNION SELECT 'CCC', To_date ('2009-12-20','YYYY-MM-DD') fromDualUNION SELECT 'DDD', To_date ('2010-10-20','YYYY-MM-DD') fromDualUNION SELECT 'Eee', To_date ('2010-10-20','YYYY-MM-DD') fromDualUNION SELECT 'FFF', To_date ('2011-10-20','YYYY-MM-DD') fromDualUNION SELECT 'GGG', To_date ('2012-10-20','YYYY-MM-DD') fromDual--Query partition TableSELECT *  fromdrawlist PARTITION (part_1);SELECT *  fromdrawlist PARTITION (part_2);SELECT *  fromdrawlist PARTITION (part_3);SELECT *  fromDrawlist PARTITION (Part_4);

　　(iii) hash partition:

Hash partitioning can be used for tables that do not have a valid range of partitions. The hash partition distributes the data evenly to a specified number of partitioned tables, because the data is evenly distributed to different partitions, reducing the competition for data blocks when querying, so that there is some help in improving performance, the partition is located in a partition column of the hash value is automatically assigned, and therefore cannot be controlled, Also do not know which record is placed in which partition, hash partition can also support multiple dependencies. It is best to use a 2-second partition table to create a hash partition. such as 2,4,8,16 and so on.

Cases:

--Create a tableCREATE TABLEdrawlist (draw_id Number, Draw_nameVARCHAR2( -))--Create a hash partitionPARTITION byHASH (draw_name) (PARTITION p1 tablespace users, PARTITION p2 tablespace users, PARTITION P3 Tablespa CE users, PARTITION P4 tablespace users);--generate 1000 rows of data--query the data in each partition table. SELECT COUNT(*) fromdrawlist PARTITION (p1);SELECT COUNT(*) fromdrawlist PARTITION (p2);SELECT COUNT(*) fromdrawlist PARTITION (p3);SELECT COUNT(*) fromDrawlist PARTITION (p4);

　　(iv) List partitioning:

The list partition also needs to specify the value of the column, its partition must be explicitly specified, the partition column can only have one, and cannot be specified as a range or hash partition as a partition dependent column, but its individual partition corresponding value can be multiple. Using a list partition, you must determine the possible values for the partition column, and once the inserted column values are not within the partition range, the Insert/update fails, so it is generally recommended to create a default partition that stores records that are not in the specified range when using the list partition. Similar to the MaxValue partition in the range partition.

CREATE TABLEArea (CODE Number, NAMEVARCHAR2( -))--Create a list partitionPARTITION byLIST (CODE) (PARTITION p1VALUES(Ten, -, -),--specifies that when the code value is 10,20,30 as the first partitionPARTITION P2VALUES( +, -, -),--specifies that when the code value is 40,50,60 for the second partitionPARTITION P_otherVALUES(DEFAULT)--the other value is the third partition)Select *  fromArea PARTITION (p1);Select *  fromArea PARTITION (p2);Select *  fromArea PARTITION (P_other);

　　

　　(v) Composite partitioning

If a table is still large after a column partition, or if there are some other requirements, you can also partition the partition by re-partitioning it within the partition, that is, how the partition is combined.

The combined partitions are divided into:

　　1. Range-hash (range-hash)

Grammar:

PARTITION by RANGE (column 1) subpartition by HASH (column 2) (

PARTITION partition Name values less THAN (value)

Tablespace table Space

)

　　

　　2. Scope-List (range-list)

Grammar:

PARTITION by RANGE (column 1) subpartition by LIST (column 2) (

PARTITION partition Name values less THAN (value)

Tablespace Table Space (

Subpartition Sub-partition name values (list specified value ... ) Tablespace table Space

）

)

Second, partition maintenance

　　1. Add partition

Grammar:

ALTER tbale table name ADD PARTITION partition table name values less THAN (value)

For example:

-- Increase interval Partitioning ALTER TABLE ADD VALUES  then To_date ('2013-1-1','yyyy-mm-dd') Tablespace USERS; -- Note: The inserted interval data value cannot be less than the value of the original partition table. If Maxvalues is included, the original partition table must be deleted

　　2. Delete a partition

Grammar:

ALTER table name DROP PARTITION partition table name

Cases:

-- Delete interval partition ALTER TABLE DROP  - after deleting the partition table, the data in the partition table will be deleted together.

　　3. Truncate partition

Deletes data from the current partition, but it does not affect other partitions.

Grammar:

ALTER table name TRUNCATE PARTITION partition table name

Cases:

-- Stage Partition Table P3, clearing data from partitioned tables ALTER TABLE TRUNCATE

　　4. Merging partitions

Merging data from two partitioned tables into a single partition, the merged partition will no longer exist. Note: Partitions with high boundaries cannot be merged into the lower boundary of the partition. For example, data less than 2009 is merged into a partition smaller than 2010, which in turn is not.

Grammar:

ALTER table name MERGE partitions partition table 1, partition table 2 into PARTITION partition table 2

Cases:

-- merge partitioned table P1 into partitioned table P2 ALTER TABLE  into PARTITION P2

　　

Oracle Base Table Partitioning