Oracle Base-Table Partitioning

One: Application Scenarios for table partitioning

Used to manage tables that contain large amounts of data.

Two: Advantages of table partitioning

1. Improve the availability of data

2. Reduce management burden

3. Improve the performance of the statement

Three: The Way of partitioning: (interval partition, hash partition, list partition, combined partition)

1. Interval partition (range zone)

syntax for creating interval partitions:

PARTITION by RANGE (column name) --range How partitions are represented

(

Partition partition table name values less than (value)

[Tablespace table space]

)

Example: Create a table partition by time.

--Creating a partitioned table when you create a tableCREATETABLEDrawlist (drawnameVARCHAR2 (20), Drawtime DATENotNull) PARTITIONBy Range (Drawtime) (--Create a table partition with Drawtime as the partition range PARTITION part_1VALUES less THAN (to_date (‘2010-1-1‘,‘Yyyy-mm-dd‘)),--Defines the partitions that were saved for data prior to 2010-1-1, excluding 2010-1-1 PARTITION part_2VALUES less THAN (to_date (‘2011-1-1‘,‘Yyyy-mm-dd‘)),--Define data partitions prior to 2011-1-1 PARTITION Part_3VALUES less THAN (to_date (‘2012-1-1‘,‘Yyyy-mm-dd‘)), PARTITION Part_4VALUES less THAN (maxvalue)--Other values Save the partition)--Inserting data, the system automatically saves the data to the corresponding partition table.INSERTIntoDrawlistSELECT‘Aaa', To_date (‘2009-10-20‘,‘Yyyy-mm-dd‘)From dualUNIONSELECT‘Bbb', To_date (‘2009-11-20‘,‘Yyyy-mm-dd‘)From dualUNIONSELECT‘Ccc', To_date (‘2009-12-20‘,‘Yyyy-mm-dd‘)From dualUNIONSELECT‘Ddd', To_date (‘2010-10-20‘,‘Yyyy-mm-dd‘)From dualUNIONSELECT‘Eee', To_date (‘2010-10-20‘,‘Yyyy-mm-dd‘)From dualUNIONSELECT‘Fff', To_date (‘2011-10-20‘,‘Yyyy-mm-dd‘)From dualUNIONSELECT‘Ggg', To_date (‘2012-10-20‘,‘yyyy-mm-ddfrom< Span style= "color: #000000;" > dual -- query partition table select Span style= "color: #808080;" >* from Drawlist PARTITION (part_1); select * from drawlist PARTITION (part_2); select * from Drawlist PARTITION (part_3); select * from drawlist PARTITION (part_4);

2. Hash partition (hash): You can use hash partitioning for tables that do not have a valid partition range

Hash partition Syntax:

Partition by Hash ()

(

Partition partition table name tablespace table space name

)

Cases:

--Create a tableCREATETABLEDrawlist (draw_idNumber, Draw_nameVARCHAR2 (20))--Create a hash partition partitionByHASH (Draw_name) (PARTITION p1 tablespace users, PARTITION p2 tablespace users, PARTITION P3 tablespace users, PARTITION P4 Tablespace USERS); --Generate 1000 rows of data-- Query the data in each partition table. select count (*) from Drawlist PARTITION (p1); count (*) from< Span style= "color: #000000;" > Drawlist PARTITION (p2); select count (*) from Drawlist PARTITION (p3); count (*) from Drawlist PARTITION (p4);

3. List partitioning: You can classify data by column values

List partition Syntax:

Partition by List ()

(

Partition partition Table name values (Specify a value of 1, specify a value of 2 ...)

)

[Tablespace table space name]

Cases:

CREATETABLEArea (CODENumber, NAMEVARCHAR2 (20))--Create a list partition partitionByLIST (CODE) (PARTITION p1VALUES (10,20,30),--Specifies that when the code value is 10,20,30 for the first partition PARTITION P2VALUES (40,50,60), -- Specifies that when the code value is 40,50,60 for the second partition PARTITION p_other values ( Span style= "color: #0000ff;" >default) -- other values are third partition ) select * from area PARTITION (p1); select * from area PARTITION (p2); select * from area PARTITION (p_other);               

　　

4. Combined partitioning: The first three partitions

Advantage: Make partitioned tables more flexible

(1). Interval-Hash combination partitioning syntax:

Partition by range (column 1) subpartition by hash(column 2)

(

Partition partition table name values less than (value)

tablespace table space name;

)

(2). Interval-list combination partitioning syntax:　　　　

Partition by range (column 1) subpartition by list(column 2)

(

Partition partition table name values less than (value)

Tablespace table space name (

Subpartition from partition name values (list specified value 1 ...)

Tablespace table Space

　　　　　　　　　　　　　　)

)

Four: Partition maintenance: (Increase partition, delete partition, truncate partition, merge partition)

1. Adding partitions

Grammar:

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

-- increase the interval partition 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. Deleting a partition

Grammar:

ALTER table name DROP PARTITION partition table name

-- Delete interval partition 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 already existing partition table name

--Truncate the partition table P3, clear the data in the partitioned table TRUNCATE PARTITION P3 

4. Merge partitions: --high-bound partitions cannot be merged into lower-bound partitions

Grammar:

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

-- Merge partition table P1 into partition table P2 intoPARTITION p2 

Oracle Base-Table Partitioning