Chinaunix首页 | 论坛 | 博客
  • 博客访问: 832722
  • 博文数量: 199
  • 博客积分: 6363
  • 博客等级: 准将
  • 技术积分: 2225
  • 用 户 组: 普通用户
  • 注册时间: 2007-04-28 10:01
个人简介

来自农村的老实娃

文章分类

全部博文(199)

文章存档

2017年(1)

2014年(2)

2013年(3)

2012年(6)

2011年(26)

2010年(34)

2009年(50)

2008年(44)

2007年(33)

我的朋友

分类: Oracle

2009-04-21 11:04:07

 

#索引组织表可以按范围、列表或散列进行分区。

Oracle 数据库10g还提供了三种类型的分区索引:

 

#本地索引

本地索引是其分区方式与其所在基础表的分区方式一模一样的索引。本地索引的每个分区仅对应于其所在基础表的一个分区。

 

示例:

SQL> CREATE INDEX idx_dyx_part_test1 ON dyx_part_test1 (xiaos_date) LOCAL

  2  (PARTITION idx_dyx_part_test1_01 TABLESPACE ts_xiaos2009q1,

  3  PARTITION idx_dyx_part_test1_02 TABLESPACE ts_xiaos2009q2,

  4  PARTITION idx_dyx_part_test1_03 TABLESPACE ts_xiaos2009q3,

  5  PARTITION idx_dyx_part_test1_04 TABLESPACE ts_xiaos2009q4

  6  ) TABLESPACE users;

 

Index created.

 

#全局分区索引

分区索引是使用不同于其所在表的分区键进行分区的索引,其所在表可以是分区表或非分区表。全局分区的索引可以使用

范围或散列分区进行分区。例如,某个表可以按月份进行范围分区,因此具有十二个分区,而该表上的索引则可以使用不同的分区键

进行范围分区,从而具有不同的分区数量。

 

分区索引就是在所有每个区上单独创建索引,它能自动维护,在droptruncate某个分区时不影响该索引的其他分区索引的使用,也就是索

引不会失效,维护起来比较方便,但是在查询性能稍微有点影响

 

#全局非分区索引

全局非分区索引基本上和非分区表的索引一样。索引结构是不分区的。

Oracle 提供了一系列丰富的技术,可用于对表、索引和索引组织表进行分区,因此可以针对任何业务环境中的任何应用程序进行最佳的分区。

Oracle 还提供一套完整的SQL命令,用于管理分区表。其中包括添加新分区、删除分区、分拆分区以及合并分区的命令。

 

用分区功能提高可管理性

    通过 Oracle 分区功能,可将表和索引分成更多、更小的可管理单元,从而使数据库管理员能以“化整为零,个个击破”的方式管理数据。

使用分区功能,维护操作可集中于表的特定部分。

例如,数据库管理员可以只对表的一部分做备份,而不必对整个表做备份。对整个数据库对象的维护操作,可以在每个分区的基础上进行,

从而将维护工作分解成更容易管理的小块。

 

    利用分区功能提高可管理性的一个典型用法是支持数据仓库中的‘滚动视窗’加载进程。假设数据库管理员每周向表中加载新数据。可以对

该表进行范围分区,使每个分区包含一周的数据。这样加载进程只是简单地添加新的分区。添加一个分区的操作比修改整个表效率高很多,因

DBA 不需要修改任何其他分区。

 

用分区功能提高性能

由于限制了所检查或操作的数据数量,同时支持并行任务执行,Oracle 分区功能实现了性能上增益。这些特性包括:

 

#分区修整

分区修整是用分区功能提高性能的最简单最有价值的手段。分区修整常常能够将查询性能提高几个数量级。例如,假设某个应用程序包含一个存

储订单历史记录的 Orders 表,并且此表已按周分区。查询一周的订单只需访问该订单表的一个分区。如果该订单表包含两年的历史记录,这个

查询只需要访问一个分区而不是一百零四个。该查询的执行速度因为分区修整而有可能快一百倍。分区修整能与所有其他 Oracle 性能特性协作。

Oracle 能将分区修整功能与任何索引技术、联接技术或并行访问方法结合使用。

 

 

分区表的维护:

增加分区:

 

 

SQL> ALTER TABLE dyx_part_test1 ADD PARTITION xiaoss2009_q5

  2  VALUES LESS THAN (TO_DATE('2010-06-01','YYYY-MM-DD'))

  3  TABLESPACE users;

 

Table altered.

 

如果已有maxvalue分区,不能增加分区,可以采取分裂分区的办法增加分区!

 

删除分区:

 

 

SQL> ALTER TABLE dyx_part_test1 DROP partition xiaoss2009_q5;

 

Table altered.

 

截短分区:

 

SQL> alter table dyx_part_test1 truncate partition xiaoss2009_q4;

 

Table truncated.

 

合并分区:

 

SQL> alter table dyx_part_test1 merge partitions xiaoss2009_q3,xiaoss2009_q4 into partition xiaoss2009_q4;

 

Table altered.

 

SQL> alter index idx_dyx_part_test1 rebuild partition idx_dyx_part_test1_01 parallel 4;

 

Index altered.

 

分裂分区:

 

范围示例:

SQL> ALTER TABLE dyx_part_test1

  2  SPLIT PARTITION xiaoss2009_q4

  3  AT (TO_DATE('2009-10-01','YYYY-MM-DD'))

  4  INTO (partition xiaoss2009_q3,partition xiaoss2009_q4);

 

Table altered.

 

列表示例:

 

SQL> select * from dyx_part_test2 partition(p2);

 

        ID NAME                           LOCA

---------- ------------------------------ ------------------------------

         3 广成子                         天津

         5 太已真人                       上海

         7 元始天尊                       重庆

 

SQL> alter table dyx_part_test2 split partition p2 values ('天津','重庆') into (partition p5,partition p6);

 

Table altered.

 

SQL> select * from dyx_part_test2 partition(p2);

select * from dyx_part_test2 partition(p2)

                                       *

ERROR at line 1:

ORA-02149: Specified partition does not exist

 

SQL> select * from dyx_part_test2 partition(p5);

 

        ID NAME                           LOCA

---------- ------------------------------ ------------------------------

         3 广成子                         天津

         7 元始天尊                       重庆

 

SQL> select * from dyx_part_test2 partition(p6);

 

        ID NAME                           LOCA

---------- ------------------------------ ------------------------------

         5 太已真人                       上海

 

注:分开的分区(上例中的p5p6)不能是原分区表中已经有的不同于被分区的分区

 

交换分区:

    交换分区(Exchange partition)提供了一种方式,让你在表与表或分区与分区之间迁移数据,注意不是将表转换成分区或非分区的形式,而仅只是迁移表中数据

(互相迁移),由于其号称是采用了更改数据字典的方式,因此效率最高(几乎不涉及io操作)Exchange partition适用于所有分区格式,你可以将数据从分区表迁移

到非分区表,也可以从非分区表迁移至分区表,或者从hash partitionrange partition诸如此类吧

 

SQL> create table dyx_part_test7 (id number,name varchar2(50))

  2  partition by range(id)

  3  (partition t_range_p1 values less than (10) tablespace ts_xiaos2009q1,

  4  partition t_range_p2 values less than (20) tablespace ts_xiaos2009q2,

  5  partition t_range_p3 values less than (30) tablespace ts_xiaos2009q3,

  6  partition t_range_pmax values less than (maxvalue) tablespace ts_xiaos2009q4

  7  );

 

Table created.

 

SQL> insert into dyx_part_test7 values (11,'a');

 

1 row created.

 

SQL> insert into dyx_part_test7 values (12,'b');

 

1 row created.

 

SQL> insert into dyx_part_test7 values (13,'c');

 

1 row created.

 

SQL> commit;

 

Commit complete.

 

SQL> create table dyx_part_test7_tmp (id number,name varchar2(50));

 

Table created.

 

SQL> alter table dyx_part_test7 exchange partition t_range_p2

  2  with table dyx_part_test7_tmp;

 

Table altered.

 

SQL> select * from dyx_part_test7 partition(t_range_p2);

 

no rows selected

 

SQL> select * from dyx_part_test7_tmp;

 

        ID NAME

---------- --------------------------------------------------

        11 a

        12 b

        13 c

记录成功交换到未分区的表中

 

接着再执行一次交换

 

SQL> alter table dyx_part_test7 exchange partition t_range_p2

  2  with table dyx_part_test7_tmp;

 

Table altered.

 

SQL> select * from dyx_part_test7_tmp;

 

no rows selected

 

SQL> select * from dyx_part_test7 partition(t_range_p2);

 

        ID NAME

---------- --------------------------------------------------

        11 a

        12 b

        13 c

从上面可以看出又交换回去了

 

SQL> insert into dyx_part_test7_tmp values (15,'d');

 

1 row created.

 

SQL> insert into dyx_part_test7_tmp values (16,'e');

 

1 row created.

 

SQL> insert into dyx_part_test7_tmp values (17,'d');

 

1 row created.

 

SQL>

SQL> commit;

 

Commit complete.

 

SQL> alter table dyx_part_test7 exchange partition t_range_p2

  2  with table dyx_part_test7_tmp;

 

Table altered.

 

SQL>

SQL> select *from dyx_part_test7 partition(t_range_p2);

 

        ID NAME

---------- --------------------------------------------------

        15 d

        16 e

        17 d

 

SQL> select *from dyx_part_test7_tmp;

 

        ID NAME

---------- --------------------------------------------------

        11 a

        12 b

        13 c

从上面的测试可以看出,两个表或分区表是在交换数据!

 

注意:

  参于交换的两表之间表结构必须一致,除非附加with validation子句;

  如果是从非分区表向分区表做交换,非分区表中的数据必须符合分区表中指定分区的规则,除非附加without validation子句;

  如果从分区表向分区表做交换,被交换的分区的数据必须符合分区规则,除非附加without validation子句;

  Global索引或涉及到数据改动了的global索引分区会被置为unusable,除非附加update indexes子句。

  提示:

  一旦附加了without validation子句,则表示不再验证数据有效性,因此指定该子句时务必慎重。

 

访问指定分区:

 

SQL> select * from dyx_part_test2 partition(p1);

 

        ID NAME                           LOCA

---------- ------------------------------ ------------------------------

         2 云中子                         北京

         8 南级战神                       北京

 

导出导入:

 

EXPORT指定分区:

 

[oracle@ora ~]$ exp dyx/dyx tables=dyx_part_test2:p1 file=dyx_part_test2_p1.dmp

 

Export: Release 10.2.0.1.0 - Production on Sat Apr 25 09:42:47 2009

 

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

 

 

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options

Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

 

About to export specified tables via Conventional Path ...

. . exporting table                 DYX_PART_TEST2

. . exporting partition                             P1          2 rows exported

EXP-00091: Exporting questionable statistics.

Export terminated successfully with warnings.

 

IMPORT指定分区:

 

[oracle@ora ~]$ imp dyx/dyx file=dyx_part_test2_p1.dmp TABLES=(dyx_part_test2:p1) IGNORE=y

 

Import: Release 10.2.0.1.0 - Production on Sat Apr 25 09:44:15 2009

 

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

 

 

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options

 

Export file created by EXPORT:V10.02.01 via conventional path

import done in ZHS16GBK character set and AL16UTF16 NCHAR character set

. importing DYX's objects into DYX

. importing DYX's objects into DYX

. . importing partition          "DYX_PART_TEST2":"P1"          2 rows imported

Import terminated successfully without warnings.

 

 

普通表变为分区表

 

将已存在数据的普通表转变为分区表,没有办法通过修改属性的方式直接转化为分区表,必须通过重建的方式进行转变,一般可以有三种方法:

 

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

 

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

Table created.

 

SQL> INSERT INTO T

  2  SELECT ROWNUM, SYSDATE - ROWNUM FROM user_OBJECTS WHERE ROWNUM <= 5000;

57 rows created.

 

SQL> commit;

Commit complete.

 

SQL> CREATE TABLE T_NEW (ID, TIME) PARTITION BY RANGE (TIME)

  2  (PARTITION P1 VALUES LESS THAN (TO_DATE('2009-01-01', 'YYYY-MM-DD')),

  3  PARTITION P2 VALUES LESS THAN (TO_DATE('2009-04-01', 'YYYY-MM-DD')),

  4  PARTITION P3 VALUES LESS THAN (TO_DATE('2009-10-01', 'YYYY-MM-DD')),

  5  PARTITION P4 VALUES LESS THAN (MAXVALUE))

  6  AS SELECT ID,TIME FROM T;

 

Table created.

 

更换表名

SQL> RENAME T TO T_OLD;

Table renamed.

 

SQL> SQL> RENAME T_NEW TO T;

Table renamed.

 

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

 

  COUNT(*)

----------

        33

 

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

 

  COUNT(*)

----------

        24

 

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

 

不足:对于数据的一致性方面还需要额外的考虑。由于几乎没有办法通过手工锁定T表的方式保证一致性,在执行CREATE TABLE语句和RENAME T_NEW TO T语句直接的

修改可能会丢失,如果要保证一致性,需要在执行完语句后对数据进行检查,而这个代价是比较大的。另外在执行两个RENAME语句之间执行的对T的访问会失败。

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

 

 

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

因前面有闪换分区的示例,这里就不用重新示例了

 

优点:只是对数据字典中分区和表的定义进行了修改,没有数据的修改或复制,效率最高。如果对数据在分区中的分布没有进一步要求的话,实现比较简单。在执行

RENAME操作后,可以检查T_OLD中是否存在数据,如果存在的话,直接将这些数据插入到T中,可以保证对T插入的操作不会丢失。

 

不足:仍然存在一致性问题,交换分区之后RENAME T_NEW TO T之前,查询、更新和删除会出现错误或访问不到数据。如果要求数据分布到多个分区中,则需要进行

分区的SPLIT操作,会增加操作的复杂度,效率也会降低。

 

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

 

 

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

 

不作示例

 

优点:保证数据的一致性,在大部分时间内,表T都可以正常进行DML操作。只在切换的瞬间锁表,具有很高的可用性。这种方法具有很强的灵活性,对各种不同的需

要都能满足。而且,可以在切换前进行相应的授权并建立各种约束,可以做到切换完成后不再需要任何额外的管理操作。

 

相关视图:

 

显示当前用户可访问的所有分区表信息﹕ALL_PART_TABLES

 

显示当前用户所有分区表的信息﹕USER_PART_TABLES

 

显示表分区信息 显示数据库所有分区表的详细分区信息﹕DBA_TAB_PARTITIONS

 

显示当前用户可访问的所有分区表的详细分区信息﹕ALL_TAB_PARTITIONS

 

 

显示当前用户所有分区表的详细分区信息﹕USER_TAB_PARTITIONS

 

显示子分区信息 显示数据库所有组合分区表的子分区信息﹕DBA_TAB_SUBPARTITIONS

 

显示当前用户可访问的所有组合分区表的子分区信息﹕ALL_TAB_SUBPARTITIONS

 

 

显示当前用户所有组合分区表的子分区信息﹕USER_TAB_SUBPARTITIONS

 

 

显示分区列 显示数据库所有分区表的分区列信息﹕DBA_PART_KEY_COLUMNS

 

显示当前用户可访问的所有分区表的分区列信息﹕ALL_PART_KEY_COLUMNS

 

 

显示当前用户所有分区表的分区列信息﹕USER_PART_KEY_COLUMNS

 

显示子分区列 显示数据库所有分区表的子分区列信息﹕DBA_SUBPART_KEY_COLUMNS

 

显示当前用户可访问的所有分区表的子分区列信息﹕ALL_SUBPART_KEY_COLUMNS

 

显示当前用户所有分区表的子分区列信息﹕USER_SUBPART_KEY_COLUMNS

 

阅读(2762) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~