来自农村的老实娃
分类: 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