It so life

love as life

Oracle数据库中分区表的操作方法1

Posted on

Oracle数据库中分区表的操作方法1

通行证：用户名 密码 首页 - 新闻 - 免费邮箱 - 短信 - POPO - 相册 - 搜索 - 交友 - 拍卖 NetEase 新闻 | 体育 | 财经 | 娱乐 | 商业| 科技 | 汽车 | 数码 | 女人 | 旅游 | 教育 | 文化 | 广州 | 军事 | 部落 评论 | NBA | 明星 | 证券 | 基金 | 探索 | 房产 | 手机 | 两性 | 健康 | 培训 | 电影 | 游戏| 拍卖 | 论坛 网易学院·教程 ·工具软件·办公软件·操作系统·图形图像·网站建设·编程开发·多媒体·网络·病毒 ·学院首页·精选专题·艺术设计·电子杂志·教授视点·教授名录·软件新闻·论坛

你现在的位置是： 网易 > 网易科技 > 网易学院·教程

Oracle数据库中分区表的操作方法

http://tech.163.com/school · 2005-06-16 11:44:19 · 来源: blog

第1页：Oracle数据库中分区表的操作方法 第2页：Oracle数据库中分区表的操作方法 摘要：在大量业务数据处理的项目中，可以考虑使用分区表来提高应用系统的性能并方便数据管理，本文详细介绍了分区表的使用。 在大型的企业应用或企业级的数据库应用中，要处理的数据量通常可以达到几十到几百GB，有的甚至可以到TB级。虽然存储介质和数据处理技术的发展也很快，但是仍然不能满足用户的需求，为了使用户的大量的数据在读写操作和查询中速度更快，Oracle提供了对表和索引进行分区的技术，以改善大型应用系统的性能。 使用分区的优点： ·增强可用性：如果表的某个分区出现故障，表在其他分区的数据仍然可用； ·维护方便：如果表的某个分区出现故障，需要修复数据，只修复该分区即可； ·均衡I/O：可以把不同的分区映射到磁盘以平衡I/O，改善整个系统性能； ·改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索速度。 Oracle数据库提供对表或索引的分区方法有三种： ·范围分区 ·Hash分区（散列分区） ·复合分区 下面将以实例的方式分别对这三种分区方法来说明分区表的使用。为了测试方便，我们先建三个表空间。

create tablespace dinya_space01 datafile ’/test/demo/oracle/demodata/dinya01.dnf’ size 50M create tablespace dinya_space01 datafile ’/test/demo/oracle/demodata/dinya02.dnf’ size 50M create tablespace dinya_space01 datafile ’/test/demo/oracle/demodata/dinya03.dnf’ size 50M 1.1. 分区表的创建 1.1.1. 范围分区 范围分区就是对数据表中的某个值的范围进行分区，根据某个值的范围，决定将该数据存储在哪个分区上。如根据序号分区，根据业务记录的创建日期进行分区等。 需求描述：有一个物料交易表，表名：material_transactions。该表将来可能有千万级的数据记录数。要求在建该表的时候使用分区表。这时候我们可以使用序号分区三个区，每个区中预计存储三千万的数据，也可以使用日期分区，如每五年的数据存储在一个分区上。 根据交易记录的序号分区建表：

SQL> create table dinya_test 2 ( 3 transaction_id number primary key, 4 item_id number(8) not null, 5 item_description varchar2(300), 6 transaction_date date not null 7 ) 8 partition by range (transaction_id) 9 ( 10 partition part_01 values less than(30000000) tablespace dinya_space01, 11 partition part_02 values less than(60000000) tablespace dinya_space02, 12 partition part_03 values less than(maxvalue) tablespace dinya_space03 13 ); Table created. 建表成功，根据交易的序号，交易ID在三千万以下的记录将存储在第一个表空间dinya_space01中，分区名为:par_01，在三千万到六千万之间的记录存储在第二个表空间： dinya_space02中，分区名为：par_02，而交易ID在六千万以上的记录存储在第三个表空间dinya_space03中，分区名为par_03. 根据交易日期分区建表：

SQL> create table dinya_test 2 ( 3 transaction_id number primary key, 4 item_id number(8) not null, 5 item_description varchar2(300), 6 transaction_date date not null 7 ) 8 partition by range (transaction_date) 9 ( 10 partition part_01 values less than(to_date(’2006-01-01’,’yyyy-mm-dd’)) tablespace dinya_space01, 11 partition part_02 values less than(to_date(’2010-01-01’,’yyyy-mm-dd’)) tablespace dinya_space02, 12 partition part_03 values less than(maxvalue) tablespace dinya_space03 13 ); Table created. 这样我们就分别建了以交易序号和交易日期来分区的分区表。每次插入数据的时候，系统将根据指定的字段的值来自动将记录存储到制定的分区（表空间）中。 当然，我们还可以根据需求，使用两个字段的范围分布来分区，如partition by range ( transaction_id ,transaction_date), 分区条件中的值也做相应的改变，请读者自行测试。 1.1.2. Hash分区（散列分区） 散列分区为通过指定分区编号来均匀分布数据的一种分区类型，因为通过在I/O设备上进行散列分区，使得这些分区大小一致。如将物料交易表的数据根据交易ID散列地存放在指定的三个表空间中：

SQL> create table dinya_test 2 ( 3 transaction_id number primary key, 4 item_id number(8) not null, 5 item_description varchar2(300), 6 transaction_date date 7 ) 8 partition by hash(transaction_id) 9 ( 10 partition part_01 tablespace dinya_space01, 11 partition part_02 tablespace dinya_space02, 12 partition part_03 tablespace dinya_space03 13 ); Table created. 建表成功，此时插入数据，系统将按transaction_id将记录散列地插入三个分区中，这里也就是三个不同的表空间中。 1.1.3. 复合分区 有时候我们需要根据范围分区后，每个分区内的数据再散列地分布在几个表空间中，这样我们就要使用复合分区。复合分区是先使用范围分区，然后在每个分区内再使用散列分区的一种分区方法，如将物料交易的记录按时间分区，然后每个分区中的数据分三个子分区，将数据散列地存储在三个指定的表空间中：

SQL> create table dinya_test 2 ( 3 transaction_id number primary key, 4 item_id number(8) not null, 5 item_description varchar2(300), 6 transaction_date date 7 ) 8 partition by range(transaction_date)subpartition by hash(transaction_id) 9 subpartitions 3 store in (dinya_space01,dinya_space02,dinya_space03) 10 ( 11 partition part_01 values less than(to_date(’2006-01-01’,’yyyy-mm-dd’)), 12 partition part_02 values less than(to_date(’2010-01-01’,’yyyy-mm-dd’)), 13 partition part_03 values less than(maxvalue) 14 ); Table created. 该例中，先是根据交易日期进行范围分区，然后根据交易的ID将记录散列地存储在三个表空间中。 1.2. 分区表操作 以上了解了三种分区表的建表方法，下面将使用实际的数据并针对按日期的范围分区来测试分区表的数据记录的操作。 1.2.1. 插入记录：

SQL> insert into dinya_test values(1,12,’BOOKS’,sysdate); 1 row created. SQL> insert into dinya_test values(2,12, ’BOOKS’,sysdate+30); 1 row created. SQL> insert into dinya_test values(3,12, ’BOOKS’,to_date(’2006-05-30’,’yyyy-mm-dd’)); 1 row created. SQL> insert into dinya_test values(4,12, ’BOOKS’,to_date(’2007-06-23’,’yyyy-mm-dd’)); 1 row created. SQL> insert into dinya_test values(5,12, ’BOOKS’,to_date(’2011-02-26’,’yyyy-mm-dd’)); 1 row created. SQL> insert into dinya_test values(6,12, ’BOOKS’,to_date(’2011-04-30’,’yyyy-mm-dd’)); 1 row created. SQL> commit; Commit complete. SQL> 按上面的建表结果，2006年前的数据将存储在第一个分区part_01上，而2006年到2010年的交易数据将存储在第二个分区part_02上，2010年以后的记录存储在第三个分区part_03上。

落叶秋客 [返回首页]

[1] [2] 相关文章：

推荐文章：

·赢在2007赛迪网年度企业人物榜单揭晓 ·你最需要哪种类型的视频教程 ·不用网银也网上购物 三步自助开通 ·2007年UPA用户体验大会成功闭幕 ·2007中国青年设计节暨用户体验年会在京开幕 ·User Friendly 2007现场图片 ·手机畅游互联网 空中Opera 2.0实测 ·动易软件即将全面开源和免费 ·搜图有“道” 图片达人的搜索秘籍 ·数字水墙 08年世博会亮相 ·RabbitAir MinusA2空气净化器设计 ·Oscar Bjarnason 平面设计 ·国产杀毒软件半个月内勇夺两项国际权威认证 ·美化你的博客-推荐多个博客图标下载网站 ·微软回应Vista用户遇到的网络问题“设计如此” ·eBay改进用户体验 借鉴亚马逊页面设计搜索功能 ·极点五笔6.0正式版发布 ·WPS Office 2007抢先试用 ·报告称Google Apps功能远不及微软Office ·什么是SOCK5代理，什么是HTTP代理 最新评论 更多评论 共 0条评论 网易通行证： 　 密码： 　 　 参与评论 您的发表的评论将在5分钟内被审核，请耐心等待

网易学院联系电话：020-61210163-560 合作/投稿邮箱：NeteaseSchool(at)126.com

合作媒体：结绳记事 艺网 中国电脑救援中心 ad110.com 中国教程网 FIF多媒体制作组 金鹰电脑教程网 时代光魔 好好学习 天天向上 主编信箱　热线:020-　给网易提意见　About NetEase - 公司简介 - 联系方法 - 招聘信息 - 客户服务 - 相关法律 - 网络营销 - 帮助中心 网易公司版权所有 ©1997-2009

Oracle数据库中分区表的操作方法2

Posted on

Oracle数据库中分区表的操作方法2

通行证：用户名 密码 首页 - 新闻 - 免费邮箱 - 短信 - POPO - 相册 - 搜索 - 交友 - 拍卖 NetEase 新闻 | 体育 | 财经 | 娱乐 | 商业| 科技 | 汽车 | 数码 | 女人 | 旅游 | 教育 | 文化 | 广州 | 军事 | 部落 评论 | NBA | 明星 | 证券 | 基金 | 探索 | 房产 | 手机 | 两性 | 健康 | 培训 | 电影 | 游戏| 拍卖 | 论坛 网易学院·教程 ·工具软件·办公软件·操作系统·图形图像·网站建设·编程开发·多媒体·网络·病毒 ·学院首页·精选专题·艺术设计·电子杂志·教授视点·教授名录·软件新闻·论坛

你现在的位置是： 网易 > 网易科技 > 网易学院·教程

Oracle数据库中分区表的操作方法

http://tech.163.com/school · 2005-06-16 11:44:19 · 来源: blog

第1页：Oracle数据库中分区表的操作方法 第2页：Oracle数据库中分区表的操作方法 1.2.2. 查询分区表记录：

SQL> select / from dinya_test partition(part_01); TRANSACTION_ID ITEM_ID ITEM_DESCRIPTION TRANSACTION_DATE -------------------------------------------------------------------------------- 1 12 BOOKS 2005-1-14 14:19: 2 12 BOOKS 2005-2-13 14:19: SQL> SQL> select / from dinya_test partition(part_02); TRANSACTION_ID ITEM_ID ITEM_DESCRIPTION TRANSACTION_DATE -------------------------------------------------------------------------------- 3 12 BOOKS 2006-5-30 4 12 BOOKS 2007-6-23 SQL> SQL> select / from dinya_test partition(part_03); TRANSACTION_ID ITEM_ID ITEM_DESCRIPTION TRANSACTION_DATE -------------------------------------------------------------------------------- 5 12 BOOKS 2011-2-26 6 12 BOOKS 2011-4-30 SQL> 从查询的结果可以看出，插入的数据已经根据交易时间范围存储在不同的分区中。这里是指定了分区的查询，当然也可以不指定分区，直接执行select / from dinya_test查询全部记录。 在也检索的数据量很大的时候，指定分区会大大提高检索速度。 1.2.3. 更新分区表的记录：

SQL> update dinya_test partition(part_01) t set t.item_description=’DESK’ where t.transaction_id=1; 1 row updated. SQL> commit; Commit complete. SQL> 这里将第一个分区中的交易ID=1的记录中的item_description字段更新为“DESK”，可以看到已经成功更新了一条记录。但是当更新的时候指定了分区，而根据查询的记录不在该分区中时，将不会更新数据，请看下面的例子：

SQL> update dinya_test partition(part_01) t set t.item_description=’DESK’ where t.transaction_id=6; 0 rows updated. SQL> commit; Commit complete. SQL> 指定了在第一个分区中更新记录，但是条件中限制交易ID为6，而查询全表，交易ID为6的记录在第三个分区中，这样该条语句将不会更新记录。 1.2.4. 删除分区表记录：

SQL> delete from dinya_test partition(part_02) t where t.transaction_id=4; 1 row deleted. SQL> commit; Commit complete. SQL> 上面例子删除了第二个分区part_02中的交易记录ID为4的一条记录，和更新数据相同，如果指定了分区，而条件中的数据又不在该分区中时，将不会删除任何数据。 1.3. 分区表索引的使用： 分区表和一般表一样可以建立索引，分区表可以创建局部索引和全局索引。当分区中出现许多事务并且要保证所有分区中的数据记录的唯一性时采用全局索引。 1.3.1. 局部索引分区的建立：

SQL> create index dinya_idx_t on dinya_test(item_id) 2 local 3 ( 4 partition idx_1 tablespace dinya_space01, 5 partition idx_2 tablespace dinya_space02, 6 partition idx_3 tablespace dinya_space03 7 ); Index created. SQL> 看查询的执行计划，从下面的执行计划可以看出，系统已经使用了索引：

SQL> select / from dinya_test partition(part_01) t where t.item_id=12; Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=187) 1 0 TABLE ACCESS (BY LOCAL INDEX ROWID) OF ’DINYA_TEST’ (Cost= 2 Card=1 Bytes=187) 2 1 INDEX (RANGE SCAN) OF ’DINYA_IDX_T’ (NON-UNIQUE) (Cost=1 Card=1) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 4 consistent gets 0 physical reads 0 redo size 334 bytes sent via SQL/Net to client 309 bytes received via SQL/Net from client 2 SQL/Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 2 rows processed SQL> 1.3.2. 全局索引分区的建立 全局索引建立时global 子句允许指定索引的范围值，这个范围值为索引字段的范围值：

SQL> create index dinya_idx_t on dinya_test(item_id) 2 global partition by range(item_id) 3 ( 4 partition idx_1 values less than (1000) tablespace dinya_space01, 5 partition idx_2 values less than (10000) tablespace dinya_space02, 6 partition idx_3 values less than (maxvalue) tablespace dinya_space03 7 ); Index created. SQL> 本例中对表的item_id字段建立索引分区，当然也可以不指定索引分区名直接对整个表建立索引，如：

SQL> create index dinya_idx_t on dinya_test(item_id); Index created. SQL> 同样的，对全局索引根据执行计划可以看出索引已经可以使用：

SQL> select / from dinya_test t where t.item_id=12; Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=3 Bytes=561) 1 0 TABLE ACCESS (BY GLOBAL INDEX ROWID) OF ’DINYA_TEST’ (Cost =2 Card=3 Bytes=561) 2 1 INDEX (RANGE SCAN) OF ’DINYA_IDX_T’ (NON-UNIQUE) (Cost=1 Card=3) Statistics ---------------------------------------------------------- 5 recursive calls 0 db block gets 10 consistent gets 0 physical reads 0 redo size 420 bytes sent via SQL/Net to client 309 bytes received via SQL/Net from client 2 SQL/Net roundtrips to/from client 3 sorts (memory) 0 sorts (disk) 5 rows processed SQL> 1.4. 分区表的维护： 了解了分区表的建立、索引的建立、表和索引的使用后，在应用的还要经常对分区进行维护和管理。日常维护和管理的内容包括：增加一个分区，合并一个分区及删除分区等等。下面以范围分区为例说明增加、合并、删除分区的一般操作： 1.4.1. 增加一个分区:

SQL> alter table dinya_test 2 add partition part_04 values less than(to_date(’2012-01-01’,’yyyy-mm-dd’)) tablespace dinya_spa ce03; Table altered. SQL> 增加一个分区的时候，增加的分区的条件必须大于现有分区的最大值，否则系统将提示ORA-14074 partition bound must collate higher than that of the last partition 错误。 1.4.2. 合并一个分区：

SQL> alter table dinya_test merge partitions part_01,part_02 into partition part_02; Table altered. SQL> 在本例中将原有的表的part_01分区和part_02分区进行了合并，合并后的分区为part_02,如果在合并的时候把合并后的分区定为part_01的时候，系统将提示ORA-14275 cannot reuse lower-bound partition as resulting partition 错误。 1.4.3. 删除分区：

SQL> alter table dinya_test drop partition part_01; Table altered. SQL> 删除分区表的一个分区后，查询该表的数据时显示，该分区中的数据已全部丢失，所以执行删除分区动作时要慎重，确保先备份数据后再执行，或将分区合并。 1.5. 总结： 需要说明的是，本文在举例说名分区表事务操作的时候，都指定了分区，因为指定了分区，系统在执行的时候则只操作该分区的记录，提高了数据处理的速度。不要指定分区直接操作数据也是可以的。在分区表上建索引及多索引的使用和非分区表一样。此外，因为在维护分区的时候可能对分区的索引会产生一定的影响，可能需要在维护之后重建索引，相关内容请参考分区表索引部分的文档

落叶秋客 [返回首页]

[1] [2] 相关文章：

推荐文章：

·赢在2007赛迪网年度企业人物榜单揭晓 ·你最需要哪种类型的视频教程 ·不用网银也网上购物 三步自助开通 ·2007年UPA用户体验大会成功闭幕 ·2007中国青年设计节暨用户体验年会在京开幕 ·User Friendly 2007现场图片 ·手机畅游互联网 空中Opera 2.0实测 ·动易软件即将全面开源和免费 ·搜图有“道” 图片达人的搜索秘籍 ·数字水墙 08年世博会亮相 ·RabbitAir MinusA2空气净化器设计 ·Oscar Bjarnason 平面设计 ·国产杀毒软件半个月内勇夺两项国际权威认证 ·美化你的博客-推荐多个博客图标下载网站 ·微软回应Vista用户遇到的网络问题“设计如此” ·eBay改进用户体验 借鉴亚马逊页面设计搜索功能 ·极点五笔6.0正式版发布 ·WPS Office 2007抢先试用 ·报告称Google Apps功能远不及微软Office ·什么是SOCK5代理，什么是HTTP代理 最新评论 更多评论 共 0条评论 网易通行证： 　 密码： 　 　 参与评论 您的发表的评论将在5分钟内被审核，请耐心等待

网易学院联系电话：020-61210163-560 合作/投稿邮箱：NeteaseSchool(at)126.com

合作媒体：结绳记事 艺网 中国电脑救援中心 ad110.com 中国教程网 FIF多媒体制作组 金鹰电脑教程网 时代光魔 好好学习 天天向上 主编信箱　热线:020-　给网易提意见　About NetEase - 公司简介 - 联系方法 - 招聘信息 - 客户服务 - 相关法律 - 网络营销 - 帮助中心 网易公司版权所有 ©1997-2009

oracle 分区表的建立方法 (2)

Posted on

oracle 分区表的建立方法 (2)

登陆论坛 | 论坛注册| 加入收藏 | 设为首页| RSS * 首页

Oracle的分区表可以包括多个分区，每个分区都是一个独立的段（SEGMENT），可以存放到不同的表空间中。查询时可以通过查询表来访问各个分区中的数据，也可以通过在查询时直接指定分区的方法来进行查询。

分区提供以下优点：

由于将数据分散到各个分区中，减少了数据损坏的可能性；

可以对单独的分区进行备份和恢复；

可以将分区映射到不同的物理磁盘上，来分散IO；

提高可管理性、可用性和性能。

Oracle提供了以下几种分区类型：

范围分区（range）；

哈希分区（hash）；

列表分区（list）；

范围－哈希复合分区（range-hash）；

范围－列表复合分区（range-list）。

Oracle的普通表没有办法通过修改属性的方式直接转化为分区表，必须通过重建的方式进行转变，下面介绍三种效率比较高的方法，并说明它们各自的特点。

方法一：利用原表重建分区表。

步骤：

SQL> CREATE TABLE T (ID NUMBER PRIMARY KEY, TIME DATE);

表已创建。

SQL> INSERT INTO T SELECT ROWNUM, CREATED FROM DBA_OBJECTS;

已创建6264行。

SQL> COMMIT;

提交完成。

SQL> CREATE TABLE T_NEW (ID, TIME) PARTITION BY RANGE (TIME) 2 (PARTITION P1 VALUES LESS THAN (TO_DATE('2004-7-1', 'YYYY-MM-DD')), 3 PARTITION P2 VALUES LESS THAN (TO_DATE('2005-1-1', 'YYYY-MM-DD')), 4 PARTITION P3 VALUES LESS THAN (TO_DATE('2005-7-1', 'YYYY-MM-DD')), 5 PARTITION P4 VALUES LESS THAN (MAXVALUE)) 6 AS SELECT ID, TIME FROM T;

表已创建。

SQL> RENAME T TO T_OLD;

表已重命名。

SQL> RENAME T_NEW TO T;

表已重命名。

SQL> SELECT COUNT(/*) FROM T;

COUNT(/*)

  6264

SQL> SELECT COUNT(/*) FROM T PARTITION (P1);

COUNT(/*)

     0

SQL> SELECT COUNT(/*) FROM T PARTITION (P2);

COUNT(/*)

  6246

SQL> SELECT COUNT(/*) FROM T PARTITION (P3);

COUNT(/*)

    18

优点：方法简单易用，由于采用DDL语句，不会产生UNDO，且只产生少量REDO，效率相对较高，而且建表完成后数据已经在分布到各个分区中了。

不足：对于数据的一致性方面还需要额外的考虑。由于几乎没有办法通过手工锁定T表的方式保证一致性，在执行CREATE TABLE语句和RENAME T_NEW TO T语句直接的修改可能会丢失，如果要保证一致性，需要在执行完语句后对数据进行检查，而这个代价是比较大的。另外在执行两个RENAME语句之间执行的对T的访问会失败。

适用于修改不频繁的表，在闲时进行操作，表的数据量不宜太大。

方法二：使用交换分区的方法。

步骤：

SQL> CREATE TABLE T (ID NUMBER PRIMARY KEY, TIME DATE);

表已创建。

SQL> INSERT INTO T SELECT ROWNUM, CREATED FROM DBA_OBJECTS;

已创建6264行。

SQL> COMMIT;

提交完成。

SQL> CREATE TABLE T_NEW (ID NUMBER PRIMARY KEY, TIME DATE) PARTITION BY RANGE (TIME) 2 (PARTITION P1 VALUES LESS THAN (TO_DATE('2005-7-1', 'YYYY-MM-DD')), 3 PARTITION P2 VALUES LESS THAN (MAXVALUE));

表已创建。

SQL> ALTER TABLE T_NEW EXCHANGE PARTITION P1 WITH TABLE T;

表已更改。

SQL> RENAME T TO T_OLD;

表已重命名。

SQL> RENAME T_NEW TO T;

表已重命名。

SQL> SELECT COUNT(/*) FROM T;

COUNT(/*)

  6264

优点：只是对数据字典中分区和表的定义进行了修改，没有数据的修改或复制，效率最高。如果对数据在分区中的分布没有进一步要求的话，实现比较简单。在执行完RENAME操作后，可以检查T_OLD中是否存在数据，如果存在的话，直接将这些数据插入到T中，可以保证对T插入的操作不会丢失。

不足：仍然存在一致性问题，交换分区之后RENAME T_NEW TO T之前，查询、更新和删除会出现错误或访问不到数据。如果要求数据分布到多个分区中，则需要进行分区的SPLIT操作，会增加操作的复杂度，效率也会降低。

适用于包含大数据量的表转到分区表中的一个分区的操作。应尽量在闲时进行操作。

方法三：Oracle9i以上版本，利用在线重定义功能

步骤：

SQL> CREATE TABLE T (ID NUMBER PRIMARY KEY, TIME DATE);

表已创建。

SQL> INSERT INTO T SELECT ROWNUM, CREATED FROM DBA_OBJECTS;

已创建6264行。

SQL> COMMIT;

提交完成。

SQL> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE(USER, 'T', DBMS_REDEFINITION.CONS_USE_PK);

PL/SQL 过程已成功完成。

SQL> CREATE TABLE T_NEW (ID NUMBER PRIMARY KEY, TIME DATE) PARTITION BY RANGE (TIME) 2 (PARTITION P1 VALUES LESS THAN (TO_DATE('2004-7-1', 'YYYY-MM-DD')), 3 PARTITION P2 VALUES LESS THAN (TO_DATE('2005-1-1', 'YYYY-MM-DD')), 4 PARTITION P3 VALUES LESS THAN (TO_DATE('2005-7-1', 'YYYY-MM-DD')), 5 PARTITION P4 VALUES LESS THAN (MAXVALUE));

表已创建。

SQL> EXEC DBMS_REDEFINITION.START_REDEF_TABLE(USER, 'T', 'T_NEW', -

'ID ID, TIME TIME', DBMS_REDEFINITION.CONS_USE_PK);

PL/SQL 过程已成功完成。

SQL> EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('YANGTK', 'T', 'T_NEW');

PL/SQL 过程已成功完成。

SQL> SELECT COUNT(/*) FROM T;

COUNT(/*)

  6264

SQL> SELECT COUNT(/*) FROM T PARTITION (P2);

COUNT(/*)

  6246

SQL> SELECT COUNT(/*) FROM T PARTITION (P3);

COUNT(/*)

    18

优点：保证数据的一致性，在大部分时间内，表T都可以正常进行DML操作。只在切换的瞬间锁表，具有很高的可用性。这种方法具有很强的灵活性，对各种不同的需要都能满足。而且，可以在切换前进行相应的授权并建立各种约束，可以做到切换完成后不再需要任何额外的管理操作。

不足：实现上比上面两种略显复杂。

适用于各种情况。

这里只给出了在线重定义表的一个最简单的例子，详细的描述和例子可以参考下面两篇文章。

Oracle的在线重定义表功能：http://blog.itpub.net/post/468/12855

Oracle的在线重定义表功能（二）：http://blog.itpub.net/post/468/12962

索引也可以进行分区，分区索引有两种类型：global和local。对于local索引，每一个表分区对应一个索引分区，当表的分区发生变化时，索引的维护由Oracle自动进行。对于global索引，可以选择是否分区，而且索引的分区可以不与表分区相对应。当对分区进行维护操作时，通常会导致全局索引的INVALDED，必须在执行完操作后REBUILD。Oracle9i提供了UPDATE GLOBAL INDEXES语句，可以使在进行分区维护的同时重建全局索引。

全局索引可以包含多个分区的值 局部索引比全局索引容易管理,而全局索引比较快 注意:不能为散列分区 或者 子分区创建全局索引

Oracle的分区功能十分强大。不过用起来发现有两点不大方便：

第一是已经存在的表没有方法可以直接转化为分区表。不过Oracle提供了在线重定义表的功能，可以通过这种方式来完成普通表到分区表的转化。可以参考这个例子：http://blog.itpub.net/post/468/13091

第二点是如果采用了local分区索引，那么在增加表分区的时候，索引分区的表空间是不可控制的。如果希望将表和索引的分区分开到不同的表空间且不同索引分区也分散到不同的表空间中，那么只能在增加分区后，对新增的分区索引单独rebuild。

Oracle最大允许存在多少个分区呢?

我们可以从Oracle的Concepts手册上找到这个信息，对于Oracle9iR2: Tables can be partitioned into up to 64,000 separate partitions.

对于Oracle10gR2，Oracle增强了分区特性：

Tables can be partitioned into up to 1024K-1 separate partitions.

关于何时应该进行分区,Oracle有如下建议:

■ Tables greater than 2GB should always be considered for partitioning. ■ Tables containing historical data, in which new data is added into the newest partition. A typical example is a historical table where only the current month's data is updatable and the other 11 months are read only. ** 来顶一下 近回首页 返回首页 发表评论 共有 条评论 用户名: 密码: 验证码: 匿名发表 相关文章* Oracle数据库应用程序性能优化探

oracle 分区表的建立方法

Posted on

oracle 分区表的建立方法

登陆论坛 | 论坛注册| 加入收藏 | 设为首页| RSS * 首页

Oracle的分区表可以包括多个分区，每个分区都是一个独立的段（SEGMENT），可以存放到不同的表空间中。查询时可以通过查询表来访问各个分区中的数据，也可以通过在查询时直接指定分区的方法来进行查询。

分区提供以下优点：

由于将数据分散到各个分区中，减少了数据损坏的可能性；

可以对单独的分区进行备份和恢复；

可以将分区映射到不同的物理磁盘上，来分散IO；

提高可管理性、可用性和性能。

Oracle提供了以下几种分区类型：

范围分区（range）；

哈希分区（hash）；

列表分区（list）；

范围－哈希复合分区（range-hash）；

范围－列表复合分区（range-list）。

Oracle的普通表没有办法通过修改属性的方式直接转化为分区表，必须通过重建的方式进行转变，下面介绍三种效率比较高的方法，并说明它们各自的特点。

方法一：利用原表重建分区表。

步骤：

SQL> CREATE TABLE T (ID NUMBER PRIMARY KEY, TIME DATE);

表已创建。

SQL> INSERT INTO T SELECT ROWNUM, CREATED FROM DBA_OBJECTS;

已创建6264行。

SQL> COMMIT;

提交完成。

SQL> CREATE TABLE T_NEW (ID, TIME) PARTITION BY RANGE (TIME) 2 (PARTITION P1 VALUES LESS THAN (TO_DATE('2004-7-1', 'YYYY-MM-DD')), 3 PARTITION P2 VALUES LESS THAN (TO_DATE('2005-1-1', 'YYYY-MM-DD')), 4 PARTITION P3 VALUES LESS THAN (TO_DATE('2005-7-1', 'YYYY-MM-DD')), 5 PARTITION P4 VALUES LESS THAN (MAXVALUE)) 6 AS SELECT ID, TIME FROM T;

表已创建。

SQL> RENAME T TO T_OLD;

表已重命名。

SQL> RENAME T_NEW TO T;

表已重命名。

SQL> SELECT COUNT(/*) FROM T;

COUNT(/*)

  6264

SQL> SELECT COUNT(/*) FROM T PARTITION (P1);

COUNT(/*)

     0

SQL> SELECT COUNT(/*) FROM T PARTITION (P2);

COUNT(/*)

  6246

SQL> SELECT COUNT(/*) FROM T PARTITION (P3);

COUNT(/*)

    18

优点：方法简单易用，由于采用DDL语句，不会产生UNDO，且只产生少量REDO，效率相对较高，而且建表完成后数据已经在分布到各个分区中了。

不足：对于数据的一致性方面还需要额外的考虑。由于几乎没有办法通过手工锁定T表的方式保证一致性，在执行CREATE TABLE语句和RENAME T_NEW TO T语句直接的修改可能会丢失，如果要保证一致性，需要在执行完语句后对数据进行检查，而这个代价是比较大的。另外在执行两个RENAME语句之间执行的对T的访问会失败。

适用于修改不频繁的表，在闲时进行操作，表的数据量不宜太大。

方法二：使用交换分区的方法。

步骤：

SQL> CREATE TABLE T (ID NUMBER PRIMARY KEY, TIME DATE);

表已创建。

SQL> INSERT INTO T SELECT ROWNUM, CREATED FROM DBA_OBJECTS;

已创建6264行。

SQL> COMMIT;

提交完成。

SQL> CREATE TABLE T_NEW (ID NUMBER PRIMARY KEY, TIME DATE) PARTITION BY RANGE (TIME) 2 (PARTITION P1 VALUES LESS THAN (TO_DATE('2005-7-1', 'YYYY-MM-DD')), 3 PARTITION P2 VALUES LESS THAN (MAXVALUE));

表已创建。

SQL> ALTER TABLE T_NEW EXCHANGE PARTITION P1 WITH TABLE T;

表已更改。

SQL> RENAME T TO T_OLD;

表已重命名。

SQL> RENAME T_NEW TO T;

表已重命名。

SQL> SELECT COUNT(/*) FROM T;

COUNT(/*)

  6264

优点：只是对数据字典中分区和表的定义进行了修改，没有数据的修改或复制，效率最高。如果对数据在分区中的分布没有进一步要求的话，实现比较简单。在执行完RENAME操作后，可以检查T_OLD中是否存在数据，如果存在的话，直接将这些数据插入到T中，可以保证对T插入的操作不会丢失。

不足：仍然存在一致性问题，交换分区之后RENAME T_NEW TO T之前，查询、更新和删除会出现错误或访问不到数据。如果要求数据分布到多个分区中，则需要进行分区的SPLIT操作，会增加操作的复杂度，效率也会降低。

适用于包含大数据量的表转到分区表中的一个分区的操作。应尽量在闲时进行操作。

方法三：Oracle9i以上版本，利用在线重定义功能

步骤：

SQL> CREATE TABLE T (ID NUMBER PRIMARY KEY, TIME DATE);

表已创建。

SQL> INSERT INTO T SELECT ROWNUM, CREATED FROM DBA_OBJECTS;

已创建6264行。

SQL> COMMIT;

提交完成。

SQL> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE(USER, 'T', DBMS_REDEFINITION.CONS_USE_PK);

PL/SQL 过程已成功完成。

SQL> CREATE TABLE T_NEW (ID NUMBER PRIMARY KEY, TIME DATE) PARTITION BY RANGE (TIME) 2 (PARTITION P1 VALUES LESS THAN (TO_DATE('2004-7-1', 'YYYY-MM-DD')), 3 PARTITION P2 VALUES LESS THAN (TO_DATE('2005-1-1', 'YYYY-MM-DD')), 4 PARTITION P3 VALUES LESS THAN (TO_DATE('2005-7-1', 'YYYY-MM-DD')), 5 PARTITION P4 VALUES LESS THAN (MAXVALUE));

表已创建。

SQL> EXEC DBMS_REDEFINITION.START_REDEF_TABLE(USER, 'T', 'T_NEW', -

'ID ID, TIME TIME', DBMS_REDEFINITION.CONS_USE_PK);

PL/SQL 过程已成功完成。

SQL> EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('YANGTK', 'T', 'T_NEW');

PL/SQL 过程已成功完成。

SQL> SELECT COUNT(/*) FROM T;

COUNT(/*)

  6264

SQL> SELECT COUNT(/*) FROM T PARTITION (P2);

COUNT(/*)

  6246

SQL> SELECT COUNT(/*) FROM T PARTITION (P3);

COUNT(/*)

    18

优点：保证数据的一致性，在大部分时间内，表T都可以正常进行DML操作。只在切换的瞬间锁表，具有很高的可用性。这种方法具有很强的灵活性，对各种不同的需要都能满足。而且，可以在切换前进行相应的授权并建立各种约束，可以做到切换完成后不再需要任何额外的管理操作。

不足：实现上比上面两种略显复杂。

适用于各种情况。

这里只给出了在线重定义表的一个最简单的例子，详细的描述和例子可以参考下面两篇文章。

Oracle的在线重定义表功能：http://blog.itpub.net/post/468/12855

Oracle的在线重定义表功能（二）：http://blog.itpub.net/post/468/12962

索引也可以进行分区，分区索引有两种类型：global和local。对于local索引，每一个表分区对应一个索引分区，当表的分区发生变化时，索引的维护由Oracle自动进行。对于global索引，可以选择是否分区，而且索引的分区可以不与表分区相对应。当对分区进行维护操作时，通常会导致全局索引的INVALDED，必须在执行完操作后REBUILD。Oracle9i提供了UPDATE GLOBAL INDEXES语句，可以使在进行分区维护的同时重建全局索引。

全局索引可以包含多个分区的值 局部索引比全局索引容易管理,而全局索引比较快 注意:不能为散列分区 或者 子分区创建全局索引

Oracle的分区功能十分强大。不过用起来发现有两点不大方便：

第一是已经存在的表没有方法可以直接转化为分区表。不过Oracle提供了在线重定义表的功能，可以通过这种方式来完成普通表到分区表的转化。可以参考这个例子：http://blog.itpub.net/post/468/13091

第二点是如果采用了local分区索引，那么在增加表分区的时候，索引分区的表空间是不可控制的。如果希望将表和索引的分区分开到不同的表空间且不同索引分区也分散到不同的表空间中，那么只能在增加分区后，对新增的分区索引单独rebuild。

Oracle最大允许存在多少个分区呢?

我们可以从Oracle的Concepts手册上找到这个信息，对于Oracle9iR2: Tables can be partitioned into up to 64,000 separate partitions.

对于Oracle10gR2，Oracle增强了分区特性：

Tables can be partitioned into up to 1024K-1 separate partitions.

关于何时应该进行分区,Oracle有如下建议:

■ Tables greater than 2GB should always be considered for partitioning. ■ Tables containing historical data, in which new data is added into the newest partition. A typical example is a historical table where only the current month's data is updatable and the other 11 months are read only. ** 来顶一下 近回首页 返回首页 发表评论 共有 条评论 用户名: 密码: 验证码: 匿名发表 相关文章* Oracle数据库应用程序性能优化探

大数据量表分表后如何实现关系查询

Posted on

大数据量表分表后如何实现关系查询

CSDN-CSDN社区-其他数据库开发-MySQL/Postgresql

收藏 不显示删除回复显示所有回复显示星级回复显示得分回复 大数据量表分表后如何实现关系查询？[问题点数:20分，结帖人:berg369]

  • berg369
  • 等　级：
  • 结帖率：99.12%
  • 楼主发表于：2009-01-17 17:16:12 大数据量的表似乎只能水平分表吧，但分表后如何实现关系查询呢？ 例如淘宝的商品查询，能够以各种条件查到任何商品，它的商品库非常庞大，肯定不是一个表，但可通过各种条件查询，通常这些条件会是商品表的外键，如果分表，怎么进行关系查询呢？ * 对我有用[0]
  • 丢个板砖[0]
  • 引用
  • 举报
  • 管理
  • TOP 回复次数：15 * ACMAIN_CHM用户头像
  • ACMAIN_CHM
  • (acmain) *
  • 等　级：
  • 5

3 更多勋章 /#1楼 得分：0回复于：2009-01-17 18:38:06 如果a,b表结构相同可以通过union来联接 select / from a union all select / from b 但对大表很少这样处理。 一般是采取分区表.而不是分为多个表。 * 对我有用[0]

3 更多勋章 /#3楼 得分：0回复于：2009-01-18 09:22:05 MySQL中的分区概述 SQL标准在数据存储的物理方面没有提供太多的指南。SQL语言的使用独立于它所使用的任何数据结构或图表、表、行或列下的介质。但是，大部分高级数据库管理系统已经开发了一些根据文件系统、硬件或者这两者来确定将要用于存储特定数据块物理位置的方法。在MySQL中，InnoDB存储引擎长期支持表空间的概念，并且MySQL服务器甚至在分区引入之前，就能配置为存储不同的数据库使用不同的物理路径. 分区又把这个概念推进了一步，它允许根据可以设置为任意大小的规则，跨文件系统分配单个表的多个部分。实际上，表的不同部分在不同的位置被存储为单独的表。用户所选择的、实现数据分割的规则被称为分区函数，这在MySQL中它可以是模数，或者是简单的匹配一个连续的数值区间或数值列表，或者是一个内部HASH函数，或一个线性HASH函数。函数根据用户指定的分区类型来选择，把用户提供的表达式的值作为参数。该表达式可以是一个整数列值，或一个作用在一个或多个列值上并返回一个整数的函数。这个表达式的值传递给分区函数，分区函数返回一个表示那个特定记录应该保存在哪个分区的序号。这个函数不能是常数，也不能是任意数。它不能包含任何查询，但是实际上可以使用MySQL 中任何可用的SQL表达式，只要该表达式返回一个小于MAXVALUE（最大可能的正整数）的正数值。 可以通过使用用来创建分区表的CREATE TABLE语句的PARTITION子句的DATA DIRECTORY（数据路径）和INDEX DIRECTORY（索引路径）选项，为每个分区的数据和索引指定特定的路径。此外，MAX_ROWS和MIN_ROWS选项可以用来设定最大和最小的行数，它们可以各自保存在每个分区里。 分区的一些优点包括： · 与单个磁盘或文件系统分区相比，可以存储更多的数据。 · 对于那些已经失去保存意义的数据，通常可以通过删除与那些数据有关的分区，很容易地删除那些数据。相反地，在某些情况下，添加新数据的过程又可以通过为那些新数据专门增加一个新的分区，来很方便地实现。 通常和分区有关的其他优点包括下面列出的这些。MySQL 分区中的这些功能目前还没有实现，但是在我们的优先级列表中，具有高的优先级；我们希望在5.1的生产版本中，能包括这些功能。 · 一些查询可以得到极大的优化，这主要是借助于满足一个给定WHERE 语句的数据可以只保存在一个或多个分区内，这样在查找时就不用查找其他剩余的分区。因为分区可以在创建了分区表后进行修改，所以在第一次配置分区方案时还不曾这么做时，可以重新组织数据，来提高那些常用查询的效率。 · 涉及到例如SUM() 和 COUNT()这样聚合函数的查询，可以很容易地进行并行处理。这种查询的一个简单例子如 “SELECT salesperson_id, COUNT(orders) as order_total FROM sales GROUP BY salesperson_id；”。通过“并行”， 这意味着该查询可以在每个分区上同时进行，最终结果只需通过总计所有分区得到的结果。 · 通过跨多个磁盘来分散数据查询，来获得更大的查询吞吐量。 * 对我有用[0]

3 更多勋章 /#4楼 得分：5回复于：2009-01-18 09:22:57 分区类型 · RANGE 分区：基于属于一个给定连续区间的列值，把多行分配给分区。 · LIST 分区：类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。 · HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。 · KEY 分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL 服务器提供其自身的哈希函数。必须有一列或多列包含整数值。 无论使用何种类型的分区，分区总是在创建时就自动的顺序编号，且从0开始记录，记住这一点非常重要。当有一新行插入到一个分区表中时，就是使用这些分区编号来识别正确的分区。例如，如果你的表使用4个分区，那么这些分区就编号为0, 1, 2, 和3。对于RANGE和LIST分区类型，确认每个分区编号都定义了一个分区，很有必要。对HASH分区，使用的用户函数必须返回一个大于0的整数值。对于KEY分区，这个问题通过MySQL服务器内部使用的 哈希函数自动进行处理。 分区的名字基本上遵循其他MySQL 标识符应当遵循的原则，例如用于表和数据库名字的标识符。但是应当注意，分区的名字是不区分大小写的。 * 对我有用[0]

  • 丢个板砖[0]
  • 引用
  • 举报
  • 管理
  • TOP * berg369用户头像
  • berg369
  • 等　级：
  • /#5楼 得分：0回复于：2009-01-18 11:24:39 是否可以这样理解： 1、表分区后，sql查询将在多个分区并行，并把结果返回，sum或count操作，则在多个分区分别处理后汇总？是以这种原理提高性能吗？ 2、表分区后，不需要修改原单表中的任何sql语句？ 3、表分区后，各种关系操作不受影响，如某个表引用该表主键作为外键，即使分区，也不受影响 * 对我有用[0]
  • 丢个板砖[0]
  • 引用
  • 举报
  • 管理
  • TOP * ljf_ljf用户头像
  • ljf_ljf
  • (Mark Liang) *
  • 等　级：
  • /#6楼 得分：5回复于：2009-01-18 11:43:23 数据量增多始终都是一个无地洞来的，而数据库资源总是有限的，现在一般处理方法都是减少每次查询消耗资源。 无论是分区或者分表都是这样做法。 所以，大数据量表一般都是按时间来进行分区，而在关系其他表查询时候就需要进行减少大表数据记录。说真的方法都是那些，没有什么新技术只是要看你产品具体使用量才能提出一些更加有效的方法。 * 对我有用[0]
  • 丢个板砖[0]
  • 引用
  • 举报
  • 管理
  • TOP * berg369用户头像
  • berg369
  • 等　级：
  • /#7楼 得分：0回复于：2009-01-18 11:54:49 不太明白6楼的意思啊，举例说我就想做个淘宝吧，通常会设计商家表、商品表、分类表等，然后通过外键关联起来，未来的数据量可能接近于淘宝，能够做到像淘宝一样快速的多条件查询，应该注意哪些方法呢？ * 对我有用[0]
  • 丢个板砖[0]
  • 引用
  • 举报
  • 管理
  • TOP * ACMAIN_CHM用户头像
  • ACMAIN_CHM
  • (acmain) *
  • 等　级：
  • 5

3 更多勋章 /#8楼 得分：5回复于：2009-01-18 11:54:54 是否可以这样理解： 1、表分区后，sql查询将在多个分区并行，并把结果返回，sum或count操作，则在多个分区分别处理后汇总？是以这种原理提高性能吗？ 应该不是这样，全表扫描不会有什么显著的效率提高。否则就不是有什么数据仓库之说了。 建议你GOOGLE中找一下数据仓库。这个OLTP不是一个概念。 分区能够得到提高的是当你仅查2004看的时候，它只要访问2004这个分区就行了。 如6楼所说。 2、表分区后，不需要修改原单表中的任何sql语句？ 对用户来说是透明的。不需要修改原单表中的任何sql语句. 3、表分区后，各种关系操作不受影响，如某个表引用该表主键作为外键，即使分区，也不受影响 不受影响 * 对我有用[0]