关闭

Oracle——分区表

标签: OracleOracle分区Oracle分区表分区表
4228人阅读 评论(2) 收藏 举报
分类:

转载请注明出处:http://blog.csdn.net/anxpp/article/details/51869335,谢谢!

    下例中的表设计并不一定,仅作演示使用。


1、概述

    即使业务拆分,有时还是不能避免某张表的数据(行数)过多。

    当单表数据过多时,会影响整体的性能,查询将变得更慢,数据的备份恢复也会花费更多时间。

    However,数据库一般都提供了一个将单表拆分成多张表的机制,以更容易的管理表和解决性能问题。这种方式(把表的行分为多个部分)划分表数据的方法就是对表的分区。被分区的表是分区表,拆分后的每一部分叫一个分区。

    分区是解决大型表问题的有效方法:

  •     改善表的查询性能。
  •     表更容易管理。
  •     导入导出会更好的执行。

2、分区表的创建

    有多种方式创建分区表。

    2.1、范围分区

    按值的范围对表进行分区,就是范围分区。

    创建范围分区的分区表命令为:PARTITION BY RANGE。

    下面看一个示例:

  1. create table book(
  2. title nvarchar2(50) primary key,
  3. publisher nvarchar2(20),
  4. rating nvarchar2(1)
  5. )
  6. partition by range(rating)
  7. (
  8. partition book_low values less than ('3'),
  9. partition book_normal values less than ('5'),
  10. partition book_great values less than (maxvalue)
  11. );

    此例,我们创建了一张book表,我们根据他的评分分别存入不同分区中。

    下面我们插入一下数据,并看看分区表中的数据记录:

  1. insert into book values('c++','test','5');
  2. insert into book values('java','test','5');
  3. insert into book values('c#','test','3');
  4. insert into book values('c','test','4');
  5. insert into book values('数学','test','1');
  6. insert into book values('语文','test','2');
  7. insert into book values('化学','test','2');
  8. analyze table BOOK compute statistics;
  9. select t.PARTITION_NAME,t.num_rows from all_tab_partitions t where t.table_name = 'BOOK';

    上面的analyze语句用于分析这张表,这样才能得到准确的分区表信息。

    结果:

  1. BOOK_NORMAL 2
  2. BOOK_LOW 3
  3. BOOK_GREAT 2

    确实是按我们希望的那样分区保存的。

    下面的其他分区方式,查看各个分区的数据记录条数都是用同样的方法。

    2.2、散列分区

    除了范围分区,Oracle也支持散列分区,这个概念大家应该比较清楚。

    此时的分区语句为:PARTITION BY HASH。

    同时我们要设置要分区的个数,考虑到Oracle中的分区映射实现方式(本人暂时也不清楚具体的实现方式),建议分区的个数设置为2的乘方,以便使数据均匀分布。

    示例:

  1. create table book(
  2. title nvarchar2(50) primary key,
  3. publisher nvarchar2(20),
  4. rating nvarchar2(1)
  5. )
  6. partition by hash(title)
  7. partitions 16;

    可以指定分区的表空间:

  1. create table book(
  2. title nvarchar2(50) primary key,
  3. publisher nvarchar2(20),
  4. rating nvarchar2(1)
  5. )
  6. partition by hash(title)
  7. partitions 2
  8. store in (book_ts1,book_ts2);

    我们也可以指定每个分区的名字:

  1. create table book(
  2. title nvarchar2(50) primary key,
  3. publisher nvarchar2(20),
  4. rating nvarchar2(1)
  5. )
  6. partition by hash(title)
  7. (
  8. partition book_part1,
  9. partition book_part2
  10. );

    或者同时指定分区名和表空间:

  1. create table book(
  2. title nvarchar2(50) primary key,
  3. publisher nvarchar2(20),
  4. rating nvarchar2(1)
  5. )
  6. partition by hash(title)
  7. (
  8. partition book_part1 tablespace part1_ts,
  9. partition book_part2 tablespace part2_ts
  10. );

     这样可以为分区和指定更有意义的名字。

    2.3、列表分区

    列表分区也是一种可行的分区方法,可代替范围分区和散列分区。

    在列表分区中,我们需要告诉Oracle所有可能的值,并指定应该插入相应行的分区。

    示例:

  1. --列表分区
  2. drop table book;
  3. create table book(
  4. title nvarchar2(50) primary key,
  5. publisher nvarchar2(20),
  6. rating nvarchar2(1)
  7. )
  8. partition by list(rating)
  9. (
  10. partition book_part1 values('5'),
  11. partition book_part2 values('4','3'),
  12. partition book_part3 values(default)
  13. );

    其中,default关键字用于处理没有在列表中列出的值,他们讲保存到这个分区。

    2.4、间隔分区

    间隔分区是从Oracle 11g开始新增的一种新的分区类型,但实际上它是范围分区的一种,不过是添加了特性。

    间隔分区中不需要指定间隔持续时间,而是指定每个分区的时长。

    当插入新行时,将根据间隔定义确定将此行插入到那个分区,如果没有对应分区,将自动创建(所以插入数据前最好检查数据值约束,以免创建不必要的分区,如年份错误输入为2076)。

    示例:

  1. --间隔分区
  2. drop table book;
  3. create table book(
  4. title nvarchar2(50) primary key,
  5. publisher nvarchar2(20),
  6. rating nvarchar2(1),
  7. publishAt date
  8. )
  9. partition by range(publishAt)
  10. interval (numtoyminterval(1,'year'))
  11. (
  12. partition p2010 values less than (to_date('2011-01-01','yyyy-mm-dd'))
  13. );
  14. insert into book values('c++','test','5',to_date('2015-03-15','yyyy-mm-dd'));
  15. insert into book values('java','test','5',to_date('2011-03-15','yyyy-mm-dd'));
  16. insert into book values('c#','test','3',to_date('2014-03-15','yyyy-mm-dd'));
  17. insert into book values('c','test','4',to_date('2016-03-15','yyyy-mm-dd'));
  18. insert into book values('数学','test','1',to_date('2012-03-15','yyyy-mm-dd'));
  19. insert into book values('语文','test','2',to_date('2015-03-15','yyyy-mm-dd'));
  20. insert into book values('化学','test','2',to_date('2014-03-15','yyyy-mm-dd'));
  21. insert into book values('政治','test','2',to_date('2002-03-15','yyyy-mm-dd'));
  22. insert into book values('自然','test','2',to_date('2003-03-15','yyyy-mm-dd'));
  23. insert into book values('生物','test','2',to_date('2006-03-15','yyyy-mm-dd'));

    这里会按年份将不同时间出版的放入不同的分区表,其中所有2011年前的统一方法p2010分区中。

    查看状态:

  1. SYS_P681 1
  2. SYS_P680 1
  3. SYS_P679 2
  4. SYS_P678 1
  5. SYS_P677 2
  6. P2010 3

    可以看到,很多分区都是自动创建的。

    2.5、引用分区

    引用分区也是从Oracle 11g开始支持的。

    如果一个分区表依赖与另一个分区表,就可以使用引用分区,这时两张表将同步分区。

    使用的语句是:partition by reference

    示例:

  1. create table orders(
  2. order_id number primary key,
  3. order_date TIMESTAMP
  4. )
  5. PARTITION BY RANGE(order_date)
  6. interval (numtoyminterval(1,'month'))
  7. (
  8. partition p2010 values less than (to_date('2011-01-01','yyyy-mm-dd'))
  9. );
  10. CREATE TABLE order_items
  11. (
  12. order_id NUMBER NOT NULL,
  13. item_id NUMBER(3) primary key,
  14. CONSTRAINT order_items_fk
  15. FOREIGN KEY(order_id) REFERENCES orders(order_id)
  16. )
  17. PARTITION BY REFERENCE(order_items_fk);

    orders表按月分区,order_items将同步orders表的分区。

    2.6、索引分区

    索引也可以按照与对表进行分区时所用的相同范围的值来分区。

    使用方式为:

  1. create index index_name
  2. on table_name(column_name)
  3. local
  4. (
  5. --分区...
  6. );

    使用local关键字时,如上不需要指定范围,范围将由表的分区策略决定,所以索引分区与表分区是一一对应的,对于分区来说是本地的。

    如果使用global关键字,就可以创建于表分区范围不同的所以分区。

    局部(local)索引易于管理,但是全局所以进行唯一性检查的速度可能会更快。

    2.7、创建子分区

    子分区即分区的分区。

    可以使用子分区将各种方式的分区结合起来使用。对于非常非常大的表来说,这种组合分区是一种吧数据分成可管理和可调整的组成部分的有效方法。

    示例:

  1. drop table book;
  2. create table book(
  3. title nvarchar2(50) primary key,
  4. publisher nvarchar2(20),
  5. rating nvarchar2(1),
  6. publishAt date
  7. )
  8. partition by range(rating)
  9. subpartition by hash(title)
  10. subpartitions 8
  11. (
  12. partition book_low values less than ('3'),
  13. partition book_normal values less than ('5'),
  14. partition book_great values less than (maxvalue)
  15. );

    此处先根据评分分区,再通过title进行hash分区。


3、管理分区表

    对于分区表的管理,依然是使用ALTER TABLE命令。

    相关的操作有:ADD、DROP、EXCHANGE、MOVE、MODIFY、RENAME、SPLIT、TRUNCATE等。

    分区表中的数据是由Oracle内部实现分离和管理的,对于分区表,我们的操作就像是普通表一样的。

    分区表的一种常见用法是可以使应用程序的停机时间最小化。

    另一个优点是可以对不同的分区采用不同的压缩算法,对于访问较少的数据,可以使用较高级别的压缩方式以节约空间。

    更多内容建议参考官方文档:http://docs.oracle.com/database/121/CNCPT/schemaob.htm#CNCPT88859

1
0
查看评论
发表评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场

oracle分区表详解

一. 分区表理论知识 Oracle提供了分区技术以支持VLDB(Very Large DataBase)。分区表通过对分区列的判断,把分区列不同的记录,放到不同的分区中。分区完全对应用透明。 Or...
  • hijiankang
  • hijiankang
  • 2013-06-25 21:57
  • 100624

Oracle 分区表的新增、修改、删除、合并。普通表转分区表方法

一. 分区表理论知识 Oracle提供了分区技术以支持VLDB(Very Large DataBase)。分区表通过对分区列的判断,把分区列不同的记录,放到不同的分区中。分区完全对应用透明。 ...
  • a380346189
  • a380346189
  • 2017-02-10 14:22
  • 1441

oracle11g分区表按时间自动创建

oracle11g分区表按时间自动创建
  • rznice
  • rznice
  • 2017-02-13 14:46
  • 3145

Oracle 分区表和普通表查询效率分析对比

Oracle 分区表和普通表查询效率分析对比目录Oracle 分区表和普通表查询效率分析对比 目录 测试环境 创建普通的表使用toad的generate data生成一千万条记录 创建表并以哈希方式分...
  • wangdonghao137
  • wangdonghao137
  • 2015-12-02 16:11
  • 3283

什么是Oracle的分区表?

假设我们现在正在酝酿经营一家图书馆,最初,我们只有十本书提供给大家来阅读和购买。对于十本书而言,我们可能只需要一个书架格子将其作为保存这十本书的容器就足够了,因为任何一个人都可以很轻松的扫一眼就可以将...
  • ziwen00
  • ziwen00
  • 2013-06-24 09:17
  • 7506

Oracle之分区表和普通表的查询执行计划对比

总结:通过执行计划查看,分区表查询效率明显优于普通表。 --范围分区示例 drop table range_part_tab purge; --注意,此分区为范围分区 create table...
  • qq_33879355
  • qq_33879355
  • 2017-11-05 22:55
  • 95

ORACLE普通表转换分区表

1:查看表空间情况 select tablespace_name,        file_id,        file_name,        round(bytes...
  • u011278012
  • u011278012
  • 2016-11-21 23:15
  • 457

oracle分区表的使用和查询

一  创建和使用分区表 范围分区、散列分区、列表分区、复合分区 二 有关分区表的维护操作 添加、删除、截断、合并、拆分、接合、重命名、交换、移动、索引 三 相关查询
  • mzglzzc
  • mzglzzc
  • 2015-06-01 17:22
  • 20990

oracle分区表常用统计信息

---显示数据库所有分区表的信息:DBA_PART_TABLES ---显示表分区信息 显示数据库所有分区表的详细分区信息:DBA_TAB_PARTITIONS -...
  • suyishuai
  • suyishuai
  • 2013-12-27 08:41
  • 2070

oracle按时间创建分区表

首先明确分区表和表分区的区别:表分区是一种思想,分区表示一种技术实现。当表的大小过G的时候可以考虑进行表分区,提高查询效率,均衡IO。oracle分区表是oracle数据库提供的一种表分区的实现形式。...
  • shanhuhau
  • shanhuhau
  • 2014-02-24 09:57
  • 14857
    个人资料
    • 访问:752049次
    • 积分:5480
    • 等级:
    • 排名:第5701名
    • 原创:96篇
    • 转载:1篇
    • 译文:0篇
    • 评论:294条
    博客专栏
    其他信息