ORACLE Partition Table types and Partition Table Creation

Partitioned table, as the name suggests. A large table is divided into smaller pieces that are easier to manage according to certain rules. Each small table corresponds to a small segment for storage, that is, a partition. The features of a partition table: Each partition has the same logical structure, each partition has different physical attributes, and each partition can

Partitioned table, as the name suggests. A large table is divided into smaller pieces that are easier to manage according to certain rules. Each small table corresponds to a small segment for storage, that is, a partition. The features of a partition table: Each partition has the same logical structure, each partition has different physical attributes, and each partition can

Partitioned table, as the name suggests. It is to divide a large table into smaller pieces that are easier to manage according to certain rules;
Each small part corresponds to a segment for storage; each small part is also a partition;
Partition Table features:
Each partition has the same logical structure;
Each partition has different physical attributes;
Each partition can be placed in different tablespaces;
Partition Rules for oracle10g partition tables: 5
1. range
2. list
3. hash
4. range list
5. range hash
This section only describes how to create these partition tables. When to use the table, follow-up analysis
1. RANGE PARTITION
Create:
Before creating a partition table, we recommend that you create partitions in different tablespaces in the Partition Table to facilitate data management:

 sql>create table obj_range  2  (object_id number,  3   object_name varchar2(128),  4   created date,  5   object_type varchar2(19)  6  )  7  partition by range(object_id)  8  (  9   partition range_p1 values less than(3000) tablespace part1, 10   partition range_p2 values less than(6000) tablespace part2, 11   partition range_p3 values less than(maxvalue) tablespace part3 12  );Table created.

Therefore, before creating a partition table, you must create three tablespaces: part1, part2, and part3 to store each partition in the partition table! Note that maxvalue is required when creating a partitioned table with a range partition. Otherwise, an error is returned when data overflow occurs!
The method for inserting data to query data is the same as that for normal tables, as shown in figure

 sql>insert into obj_range   2  select object_id,object_name,created,object_type   3  from obj where object_id < 10000;9580 rows created. sql>select count(*) from obj_range partition (range_p1);  COUNT(*)----------      2953 

2. LIST PARTITION

sql>create table obj_list  2  (object_id number,  3   object_name varchar2(128),  4   created date,  5   object_type varchar2(19)  6  )  7  partition by list(object_type)  8  (  9   partition list_p1 values('TABLE','VIEW') tablespace part1, 10   partition list_p2 values('SEQUENCE','SYNONYM') tablespace part2, 11   partition list_p3 values(default) tablespace part3 12  );Table created.sql> insert into obj_list  2  select object_id,object_name,created,object_type   3  from obj where object_id < 10000;9580 rows created.PU@orcl10g> commit;Commit complete.sql> select count(*) from obj_list partition (list_p1);  COUNT(*)----------      3734

3. HASH PARTITION

sql>  1  create table obj_hash  2  (object_id number,  3   object_name varchar2(128),  4   created date,  5   object_type varchar2(19)  6  )  7  partition by hash(object_name)  8  (  9   partition hash_p1 tablespace part1, 10   partition hash_p2 tablespace part2, 11   partition hash_p3 tablespace part3 12* )sql> /Table created.sql> insert into obj_hash select object_id,object_name,created,object_type from all_objects where rownum <=10000;10000 rows created.SQL> commit;Commit complete.sql> select count(*) from obj_hash partition(hash_p1);  COUNT(*)----------      2430

There are two matching partitions in the preceding three types, which are complex to create and may be less useful in the actual production database!
4. RANGE and LIST composite partitions
The range list partition rule first partitions according to the range, and then nest the list partition in each range:

sql>  create table obj_range_list  2  (  3  object_id number,  4  object_name varchar2(128),  5  created date,  6  object_type varchar2(19)  7  )  8  partition by range(object_id) subpartition by list(object_type)  9  ( 10   partition range_list_p1 values less than(3000) 11   ( 12     subpartition rlp_sub1 values('TABLE','VIEW') tablespace part1, 13     subpartition rlp_sub2 values('SEQUENCE','SYNONYM') tablespace part2, 14     subpartition rlp_sub3 values(default) tablespace part3 15   ), 16   partition range_list_p2 values less than(6000) 17   ( 18     subpartition rlp2_sub1 values('TABLE','VIEW') tablespace part1, 19     subpartition rlp2_sub2 values('SEQUENCE','SYNONYM') tablespace part2, 20     subpartition rlp2_sub3 values(default) tablespace part3 21   ), 22   partition range_list_p3 values less than(maxvalue) 23   ( 24     subpartition rlp3_sub1 values('TABLE','SYNONYM') tablespace part1, 25     subpartition rlp3_sub2 values(default) tablespace part2 26   ) 27* )sql> /Table created.sql> insert into obj_range_list  2  select object_id,object_name,created,object_type  3  from obj where object_id < 10000;9580 rows created.PU@orcl10g> commit;sql> select count(*) from obj_range_list partition(range_list_P1);  COUNT(*)----------      2953

5. RANG and HASH partitions
First, partition by range, and then hash partition within each range.

  sql> create table obj_range_hash  2  (  3   object_id number,  4   object_name varchar2(128),  5   created date,  6   object_type varchar2(19)  7  )  8  partition by range(object_id) subpartition by hash(object_name) subpartitions 3 store in(part1,part2,part3)  9  ( 10   partition rh_p1 values less than(3000), 11   partition rh_p2 values less than(6000), 12   partition rh_p3 values less than(maxvalue) 13* )sql> /Table created.sql> insert into obj_range_hash  2  select object_id,object_name,created ,object_type  3  from obj where object_id < 10000;9580 rows created.sql> commit;Commit complete.sql> select count(*) from obj_range_hash;  COUNT(*)----------     10000sql> select count(*) from obj_range_hash partition(rh_p1);  COUNT(*)----------      2952

As long as the partition table is created, try to create one!