[Oracle] Partition Table: range partition, list partition, hash partition, rangehash

[Oracle] Partition Table: range partition, list partition, hash partition, rangehash
 ************************************** *********************************** *** Original article: blog.csdn.net/clark_xu Xu changliang's column**************************************** ********************************-- (1) a partition table contains multiple partitions. Each partition is on an independent segment and can store different tablespaces;
-- (1.1) officially recommends tables larger than 2 GB, requiring partitioned tables;
-- (1.2) The table contains historical data and the new data is added to the latest partition. For example, the data for 11 months is only read, and the data for the last 1 month is modified. The partition table is required;
-- (1.3) oracle 10 Gb supports 1024K-1 partitions;
-- (2) Advantages of Partitioned Tables
-- 2.1 you can back up and restore individual partitions.
-- 2.2 scattered IO
-- (3) Partitions supported by oracle 10 GB
-- Range partition range
-- Hash partition hash
-- List partition list
-- Range hash composite partition range-hash
-- Range list composite 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 ('1970-2-1 ', 'yyyy-mm-dd ')),
Partition p2 values less than (to_date ('1970-3-1 ', 'yyyy-mm-dd ')),
Partition p3 values less than (to_date ('2017-4-1 ', 'yyyy-mm-dd ')),
Partition p4 values less than (maxvalue)
);
-- View basic information about a partition table
Select * from user_part_tables;
Select table_name, partitioning_type, partition_count
From user_part_tables where table_name = 'rangetable ';
-- View the partition information of the 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 a partition table: Split a maxvale/default partition table;
Alter table rangetable split partition p4 at (to_date ('1970-5-1 ', 'yyyy-mm-dd '))
Into (partition p5, partition p6)
Alter table rangetable split partition p6 at (to_date ('1970-7-1 ', 'yyyy-mm-dd '))
Into (partition p6, partition p7)
-- Insert data
Insert into rangetable values (1, to_date ('1970-01-01 ', 'yyyy-mm-dd '));
Insert into rangetable values (1, to_date ('1970-02-01 ', 'yyyy-mm-dd '));
Insert into rangetable values (3, to_date ('2017-03-01 ', 'yyyy-mm-dd '));
Select * from rangetable;
-- Segments statistics
Select partition_name, count (*) from user_extents where segment_name = 'rangetable' group by partition_name;
-- (5) list partitions
-- Column values must be specified for list partitions, and column planting must be clear. You must create a default partition to store ambiguous values;
Create table listtable
(
Id number (10) 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)
)
-- Insert 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 ';
-- Segments statistics
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;
-- Insert 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;
-- Segments statistics
Select partition_name, count (*) from user_extents where segment_name = 'hashtable' group by partition_name;
Select * from dba_extents where segment_name = 'hashtable ';
**************************************** ********************************* *** Original article: blog.csdn.net/clark_xu Xu changliang's column**************************************** ********************************