来自农村的老实娃
分类： Oracle
2009-04-21 11:04:07
#索引组织表可以按范围、列表或散列进行分区。
Oracle 数据库
#本地索引
本地索引是其分区方式与其所在基础表的分区方式一模一样的索引。本地索引的每个分区仅对应于其所在基础表的一个分区。
示例：
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.
#全局分区索引
分区索引是使用不同于其所在表的分区键进行分区的索引，其所在表可以是分区表或非分区表。全局分区的索引可以使用
范围或散列分区进行分区。例如，某个表可以按月份进行范围分区，因此具有十二个分区，而该表上的索引则可以使用不同的分区键
进行范围分区，从而具有不同的分区数量。
分区索引就是在所有每个区上单独创建索引，它能自动维护，在drop或truncate某个分区时不影响该索引的其他分区索引的使用，也就是索
引不会失效，维护起来比较方便，但是在查询性能稍微有点影响
#全局非分区索引
全局非分区索引基本上和非分区表的索引一样。索引结构是不分区的。
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('
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('
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 太已真人 上海
注：分开的分区（上例中的p5和p6）不能是原分区表中已经有的不同于被分区的分区
交换分区:
交换分区(Exchange partition)提供了一种方式，让你在表与表或分区与分区之间迁移数据，注意不是将表转换成分区或非分区的形式，而仅只是迁移表中数据
(互相迁移)，由于其号称是采用了更改数据字典的方式，因此效率最高(几乎不涉及io操作)。Exchange partition适用于所有分区格式，你可以将数据从分区表迁移
到非分区表，也可以从非分区表迁移至分区表，或者从hash partition到range 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
---------- --------------------------------------------------
12 b
记录成功交换到未分区的表中
接着再执行一次交换
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
---------- --------------------------------------------------
12 b
从上面可以看出又交换回去了
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
---------- --------------------------------------------------
12 b
从上面的测试可以看出，两个表或分区表是在交换数据！
注意：
参于交换的两表之间表结构必须一致，除非附加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
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database
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
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database
With the Partitioning, OLAP and Data Mining options
Export file created by EXPORT:V
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('
3 PARTITION P2 VALUES LESS THAN (TO_DATE('
4 PARTITION P3 VALUES LESS THAN (TO_DATE('
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
