当前位置:Gxlcms > mysql > ORACLE分区表种类以及分区表的创建

ORACLE分区表种类以及分区表的创建

时间:2021-07-01 10:21:17 帮助过:69人阅读

分区表,顾名思义。就是将一张大表,按照一定的规则分散成更小型的更容易管理的小片; 每一个小片,对于存储来说,就对应一个段;每个小片,也就是一个分区; 分区表的特点: 每个分区,拥有相同的逻辑结构; 每个分区,拥有不同的物理属性; 每个分区,可以

分区表,顾名思义。就是将一张大表,按照一定的规则分散成更小型的更容易管理的小片;
每一个小片,对于存储来说,就对应一个段;每个小片,也就是一个分区;
分区表的特点:
每个分区,拥有相同的逻辑结构;
每个分区,拥有不同的物理属性;
每个分区,可以放置到不同的表空间上去;
oracle10g的分区表的分区规则:5种
1.range
2.list
3.hash
4.range list
5.range hash
这里只说明这几种分区表的创建,至于什么时候使用,后续分析
1、RANGE PARTITION
创建:
分区表创建之前,为了后续方便管理数据,建议分区表的各个分区创建在不同的表空间中,如下:

 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.

所以创建这个分区表之前你得创建 part1, part2, part3三个表空间来存储分区表各个分区!这里需要注意一下,建立范围分区的分区表时,一定需要有maxvalue值,否则数据溢出会报错!
插入数据查询数据的方法和普通表一样,如

 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

后面还有2中是前面三种的符合分区,创建比较复杂,实际生产库中可能也比较少用!
4、RANGE、LIST 复合分区
range list分区规则,首先按照range进行分区,然后,每个range里面再作嵌套list分区:

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、HASH分区
首先,按照range分区,在每个range范围内,再作hash分区

  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(*)
----------
     10000

sql> select count(*) from obj_range_hash partition(rh_p1);

  COUNT(*)
----------
      2952

分区表创建就写那么多了,快去试试创建一个吧!

人气教程排行