Partition Table: range partition, list partition, hash Partition

Partition Table: range partition, list partition, hash Partition
(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 d ate) 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 Partition Table Basic Information select * from user_part_tables; select table_name, partitioning_type, partition_countfrom user_part_tables where table_name = 'rangetable'; -- View 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 Partition Table: Split 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 into rangetable values (1, to_date ('2017-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 ('1970-03-01 ', 'yyyy-mm-dd'); select * from rangetable; -- count segmentsselect partition_name, count (*) from user_extents where segment_name = 'rangetable' group by partition_name; -- (5) list partition -- 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 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'; -- count segmentsselect partition_name, count (*) from user_extents where segment_name = 'listtable' group by partition_name; -- (6) create table hashtable (id number (9), areacode varchar2 (10) partition by hash (areacode) partitions 5; -- insert data 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; -- count segmentsselect partition_name, count (*) from user_extents where segment_name = 'hashtable' group by partition_name; select * from dba_extents where segment_name = 'hashtable ';