************************************************************************* * * * Original:blog.csdn.net/clark_xu Xu Changliang's Column************************************************************************--(1) The partition table contains multiple partitions, each of which is on a separate segment and can hold different table spaces;
--(1.1) The official recommendation of more than 2G table, need to partition table;
--(1.2) The table contains historical data, new data is added to the latest partition, for example 11 months of data is only read, the latest 1 months of data for modification;
--(1.3) Oracle 10g supports 1024K-1 partitions;
--(2) Advantages of partitioned tables
--2.1 can be backed up and restored on separate partitions
--2.2 Decentralized IO
--(3) partitions supported by Oracle 10g
--Range Partition range
--Hashed partition hash
--List partition lists
--range hash compound partition Range-hash
--range list compound partition range-list
--(4) Range partition
CREATE TABLE rangetable (ID number (9), time date) partition by range (time)
(
Partition P1 values less than (to_date (' 2010-2-1 ', ' yyyy-mm-dd ')),
Partition P2 values less than (to_date (' 2010-3-1 ', ' yyyy-mm-dd ')),
Partition P3 values less than (to_date (' 2010-4-1 ', ' yyyy-mm-dd ')),
Partition P4 values less than (MaxValue)
);
--View the basic information of the partition table
SELECT * from User_part_tables;
Select Table_name,partitioning_type,partition_count
From User_part_tables where table_name= ' rangetable ';
--View partition information for table: partition name, default number of extents
SELECT * from user_tab_partitions where table_name= ' rangetable ';
Select Table_name,high_value,partition_name from User_tab_partitions
--Split partition table: Split maxvale/default partition table;
ALTER TABLE rangetable split partition P4 at (to_date (' 2010-5-1 ', ' yyyy-mm-dd '))
Into (partition p5,partition P6)
ALTER TABLE rangetable split partition P6 at (to_date (' 2010-7-1 ', ' yyyy-mm-dd '))
Into (partition p6,partition P7)
--Inserting data
INSERT into rangetable values (1,to_date (' 2010-01-01 ', ' yyyy-mm-dd '));
INSERT into rangetable values (1,to_date (' 2010-02-01 ', ' yyyy-mm-dd '));
INSERT into rangetable values (3,to_date (' 2010-03-01 ', ' yyyy-mm-dd '));
SELECT * from Rangetable;
--Statistical segments
Select Partition_name,count (*) from user_extents where Segment_name= ' rangetable ' GROUP by Partition_name;
--(5) List partition
The--list partition must have a column value, and the column must be clear; To create a default partition to store ambiguous values;
CREATE TABLE Listtable
(
ID Number (TEN) is not NULL,
AreaCode VARCHAR2 (20)
)
Partition by list (AreaCode)
(
Partition list_025 values (' 025 '),
Partition list_035 values (' 035 '),
Partition list_045 values (' 045 '),
Partition list_055 values (' 055 '),
Partition List_other values (default)
)
--Inserting data
INSERT into listtable values (1, ' 025 ');
INSERT into listtable values (2, ' 035 ');
INSERT into listtable values (3, ' 045 ');
INSERT into listtable values (4, ' 055 ');
INSERT into listtable values (5, ' 075 ');
--View partition information
SELECT * from User_part_tables;
SELECT * from Listtable;
SELECT * from user_tab_partitions where table_name= ' listtable ';
--Statistical segments
Select Partition_name,count (*) from user_extents where Segment_name= ' listtable ' GROUP by Partition_name;
--(6) hash partition
CREATE TABLE Hashtable
(
ID Number (9),
AreaCode VARCHAR2 (10)
)
Partition by hash (AreaCode)
Partitions 5;
--Inserting data
INSERT into Hashtable values (1, ' 025 ');
INSERT into Hashtable values (2, ' 035 ');
INSERT into Hashtable values (3, ' 045 ');
INSERT into Hashtable values (4, ' 055 ');
INSERT into Hashtable values (5, ' 075 ');
Commit
--Statistical segments
Select Partition_name,count (*) from user_extents where Segment_name= ' HASHTABLE ' GROUP by Partition_name;
SELECT * from dba_extents where segment_name= ' HASHTABLE ';
************************************************************************* * * * Original:blog.csdn.net/clark_xu Xu Changliang's Column************************************************************************
"Oracle" Partition table: Range partition, list partition, hash partition
