9.1.4 使用分区表，索引组织表，簇表，临时表
9.1.4.1 分区表(partition table)
分区表是一种虚表，没有对应的segment, 分区表的segment由该表的分区对应，
也就是说分区表具有多个不同的segment, 但是逻辑上讲，分区表是一个完成的表，
具有表的所有特性，而所有的分区具有相同的逻辑特性。
引入分区表后，可以将不同分区放在不同的磁盘上，如果某个磁盘损坏，则只有该
损坏磁盘上的数据无法访问，其他正常的都可以访问。 可以以分区为单位进行备份。
对于性能来说，Oracle能够进行分区排除(partition pruning), 如果在c1字段列上
做了分区，那么对于where c1='ABC' 这样的条件来说，oracle可以知道c1='ABC'分
布在哪个分区，从而直接访问该分区，不需要对整个表进行扫描。
分区种类：
A. 范围分区(Range Partition)
B. 哈希分区(Hash Partition)
C. 列表分区(List Partition)
D. 范围哈希组合分区(Range-Hash Partition)
E. 范围列表组合分区Range-List Partition)
对于普通表(也就是堆组织表)，上述分区形式都可以使用(甚至可以对某个分区指定
compress属性)，只是分区依赖列不能是LOB及Long之类的数据类型，每个表的分区
或子分区总数不能超过 1024K-1个。
对于索引组织表(IOT),只能够支持普通分区方式，不支持组合分区，常规表的限制
对于索引组织表同样有效，此外还有一些其他的限制，比如要求索引组织表的分区
依赖列必须是主键才可以 等等。 这里只讨论普通表。
Oracle一般建议如果单个表超过2G最好对其进行分区。如果想对某个表分区，必须
在创建的时候就指定分区，当然我们可以将一个未分区的表修改为分区表，但是不
能直接修改，需要临时表间接实现。
如何创建分区表 --
创建分区表的时候需要注意的有，row movement属性，它用来控制是否允许修改
列值 (指分区列) 所造成的记录移动至其他分区存储，默认是disable的，Oracle允
许修改分区列，如果设置为enable row movement, 那么Oracle允许分区列发生改变
后如果值属于另外一个分区，记录自动转移到其他分区，且Oracle会自动维护索引。
但是相应的rowid也会不可避免的发生变化，影响比较大的是Logminer中的SQL_UNDO.
当row movement设置为disable时，如果记录被更新到其他分区，则更新语句会报错。
因为比较难控制是否分区列是否会有更新，所以一般我们在建立分区表的时候会设置
ENABLE ROW MOVEMENT .
A. 范围分区(Range Partition)
根据某个值的范围进行分区，典型的就是时间字段。比如每半年的数据放在一个分区中。
范围分区支持指定多个列作为依赖列，可以创建一个maxvalue分区，不在指定范围的记录
都会记录到maxvalue所在分区。不过加了maxvalue之后，如果需要新加入分区，需要进行
split , 如果没有使用maxvalue，可以直接add partition .
eg: create table t2 (id number, createdate date)
partition by range (createdate)
(
partiton p1 values less than (to_date('2008-01-01','yyyy-mm-dd')) tablespace tbs01,
partiton p2 values less than (to_date('2009-01-01','yyyy-mm-dd')) tablespace tbs02,
partiton p3 values less than (to_date('2010-01-01','yyyy-mm-dd')) tablespace tbs03,
partiton pmax values less than (maxvalue) tablespace tbs04
) ;
查询各分区记录：select * from test01 partition (p1);
设计的视图有： user_part_tables 记录分区表的信息， user_tab_partitions 记录表的分区信息
B. 哈希分区(Hash Partition)
对列的值运用hash函数，将得到的结果作为分区的区号。通常在表的字段值不容易
划分范围的时候使用hash分区，比如对于主键列，就可以采用hash分区。hash分区
最大的特点在于在不同的分区里平均分布数据，在创建hash分区时，分区的个数应该
是2的幂，否则分区表的数据分布会不平衡。hash分区可以支持多个依赖列。
具体为什么需要是2的幂数据分布才会平衡，可以参考简单测试：
http://www.itpub.net/thread-1390083-1-1.html
Hash分区指定分区有两种方式：
1), 直接指定分区名，分区所在表空间等信息
2), 只指定分区数量，和可供使用的表空间
eg: create table t3 (id number, name varchar2(20))
partition by hash(id)
(
partiton p1 tablespace tbs01,
partiton p2 tablespace tbs02,
partiton p3 tablespace tbs03,
partiton p4 tablespace tbs04
) ;
eg: create table t3 (id number, name varchar2(20))
partition by hash(id)
partitions 4
store in (ts01,ts02,ts03,ts04) ;
备注: 这里分区数量和可供使用的表空间数量之间没有直接对应关系。
分区数并不一定要等于表空间数。
可以测试一下，详细参考：
http://space.itpub.net/35489/viewspace-684470
1)， HASH分区不存在split partition，只能add partition。数据在各个分区的分布
不能人为控制，不能显示的指定某一个分区进行分裂，但是加入分区后，从上面的例
子可以看出数据拆分分布是有规律的。
例如： alter table test01 add partition p9 ;
2)， 当我们确定合理的分区数量的之后，数据的分布完全由分区表中的数据本身决定。
对于某些特定的数据来说，HASH分区后的效果可能并不好。数据的随机性越大，数据的
样本量越大，HASH分区后的效果越好，因为数据有可能更加平均的分散到每个bucket中。
3)， 对于分区个数为m的HASH分区表来说，无论期间经历了怎么样的过程（比如说先建
立n个分区的HASH分区表(n区数为m的分区表；）最后的数据分布都是相近的(不完全一样) 。
4)， 对于HASH分区表，drop partition操作是不可以的。
alter table test01 drop partition p1;
ORA-14255: 未按范围, 组合范围或列表方法对表进行分区
C. 列表分区(List Partition)
当分区的值为一些离散值的时候，该数据行就进入某个值指定的分区。它的
分区列只能有一个，而不像range或hash可以同时指定多个列作为分区依赖列，
不过它的单个分区对应值可以是多个。 一旦插入的值不在分区范围，则会报
错，所以一般建议建立一个default分区存储那些不在指定范围的记录，类
似range分区中的maxvalue分区。
eg :
create table t4 (id number, createdate date, category varchar2(10))
partition by list (category)
(
partiton p1 values ('01','02') tablespace tbs01,
partiton p2 values ('03','04') tablespace tbs02,
partiton p3 values ('05','06') tablespace tbs03,
partiton p4 values ('default) tablespace tbs04
) ;
注意： 10g中组合分区有两种：Range-hash, Range-list . 注意顺序，根
分区只能是Range分区，子分区可以是hash分区或list分区。 不过在11g中
分区组合方式还有 range-range, list-range, list-list, list-hash,
这就相当于除了hash外三种分区方式的笛卡尔形式都有了。
D. 范围哈希组合分区(Range-Hash Partition)
先按照某个字段进行范围分区，然后再对其他的列进行hash分区，得出的hash
分区叫做子分区，语法详见oracle 文档.
需要指定的有：
column_list:分区依赖列(支持多个，中间以逗号分隔);
subpartition:子分区方式，有两处：
Subpartition by list:语法与list 分区完全相同，只不过把关键字partition 换成subpartition
Subpartition by hash:语法与hash 分区完全相同，只不过把关键字partition 换成subpartition
partition: 分区名称;
range_partition_values_clause:与range 分区范围值的语法;
tablespace_clause:分区的存储属性，例如所在表空间等属性(可为空)，默认继承基表所在表空间的
例子:
create table t5 (id number, createdate date, category varchar2(10))
partition by range(id) subpartition by hash(category)
subpartition 4 store in (tbs01,tbs02,tbs03,tbs04)
(
partiton p1 values less than(10) tablespace tbs01,
partiton p2 values less than(50) tablespace tbs02,
partiton p3 values less than(100) tablespace tbs03,
partiton p4 values less than(maxvalue) tablespace tbs04
) ;
备注: 可以通过以下视图查询相关分区及子分区信息.
user_part_tables
user_tab_partitions
user_tab_subpartitions
对于某个分区创建hash子分区:
create table t6 (id number, createdate date, category varchar2(10))
partition by range(id) subpartition by hash(category)
(
partiton p1 values less than(10) tablespace tbs01,
partiton p2 values less than(50) tablespace tbs02
(subpartition p2_h1 tablespace tbs01,
subpartition p2_h2 tablespace tbs02,
subpartition p2_h3 tablespace tbs03 ),
partiton p3 values less than(100) tablespace tbs03,
partiton p4 values less than(maxvalue) tablespace tbs04
) ;
可以给各个分区指定不同的子分区：
create table t7 (id number, createdate date, category varchar2(10))
partition by range(id) subpartition by hash(category)
(
partiton p1 values less than(10) tablespace tbs01
(subpartition p1_h1 tablespace tbs01,
subpartition p1_h2 tablespace tbs02 ),
partiton p2 values less than(50) tablespace tbs02
subpartition 3 store in (tbs01,tbs02,tbs03),
partiton p3 values less than(100) tablespace tbs03,
partiton p4 values less than(maxvalue) tablespace tbs04
) ;
没有显式指定子分区的分区(比如上例的p3,p4)，系统会自动创建一个子分区,
子分区名称为 SYS_SUBP+数字 组成 。
分区模板的应用 ---
在指定子分区信赖列之后，制定子分区的存储模板，各个分区即会按照子分区
模板创建子分区， 例如：
create table t_partition_rh (id number,name varchar2(50))
partition by range(id) subpartition by hash(name)
subpartition template (
subpartition h1 tablespace tbspart01,
subpartition h2 tablespace tbspart02,
subpartition h3 tablespace tbspart03,
subpartition h4 tablespace tbspart04)(
partition t_r_p1 values less than (10) tablespace tbspart01,
partition t_r_p2 values less than (20) tablespace tbspart02,
partition t_r_p3 values less than (30) tablespace tbspart03,
partition t_r_pd values less than (maxvalue) tablespace tbspart04);
查询以下语句可以看到每个分区下都有4个子分区。
select partition_name,subpartition_name,tablespace_name
from user_tab_subpartitions where table_name='T_PARTITION_RH';
E. 范围列表组合分区(Range-List Partition)
先按照某个字段进行范围分区，然后再对其他的列进行List分区，得出的List
分区叫做子分区，语法详见oracle 文档, 创建方式和Range-Hash分区相似. 同样
范围列表分区也可以使用分区模板。
分区原则 ---
1)、如果选择的分区不能确保各分区内记录量的基本平均，则这种分区方式
有可能是不恰当的。比如对于range 分区，假设分了10 个分区，而其中一个分区
中的记录数占总记录数的90%，其它9 个分区只占总记录数的10%，则这个分区方式
就起不到数据平衡的作用。当然，如果你的目的并不是为了平衡，只是为了区分数
据，ok，对于这种情况，我想说的是，你务必要意识到存在这个问题。
2、对于分区的表或索引，其所涉及的所有分区，其块大小必须一致。
分区索引 ---
对于索引，可以区分创建的是全局索引或本地索引：
A. 全局索引(global index): 全局索引既可以分区(和分区表的分区不一样)，也可以
不分区，可以创建Range分区，Hash分区，既可以建于分区表上，也可以建于非分区表
上，也就是说，全局索引是完全独立的，因此它也需要我们更多的维护操作。
例子：
create table tab(c1 int,c2 varchar2(16),c3 varchar2(64),
c4 int constraint pk_ta primary key (c1))
partition by range(c1)
(partition p1 values less than (10000000),
partition p2 values less than (20000000),
partition p3 values less than (30000000),
partition p4 values less than (maxvalue));
create index idx_tab_c4 on tab(c4) global partition by range(c4)
( partition ip1 values less than(10000),
partition ip2 values less than(20000),
partition ip3 values less than(maxvalue));
B. 本地索引(local index): 分区形式与表的分区完全相同，依赖列独立，存储属性
也相同。对于本地索引，索引分区的维护自动进行，add/drop/split/truncate表的分
区的时候，本地索引会自动维护其索引分区 (注意自动维护不代表索引不失效)。
例子：
create index idx_tab_c2 on tab(c2) local (partition p1,partition p2,partition p3,partition p4);
或者 create index idx_tab_c2 on tab(c2) local ;
本地(局部)索引 local index --
1. 局部索引一定是分区索引，分区键等同于表的分区键，分区数等同于表的分区
说，一句话，局部索引的分区机制和表的分区机制一样。
2. 如果local index的索引列以分区键开头，则称为前缀局部索引。
3. 如果local index的列不是以分区键开头(或不包含分区键列),则称为非前缀索引。
4. 前缀和非前缀索引都可以支持分区剪除，前提是查询的条件中包含分区键 。
5. Local index支持分区内的唯一性，无法支持表上的唯一性，因此如果要用局部索引去
给表做唯一性约束，则约束中必须要包括分区键列。即Local partition index 不能建
立在PK或unique上，除非PK或unique 列上包含有分区列才可以。
6. 局部分区索引是对单个分区的，每个分区索引只指向一个表分区，全局索引则不然，
一个分区索引能指向n个表分区，同时，一个表分区，也可能指向n个索引分区，对分区
表中的某个分区做truncate或者move，shrink等，可能会影响到n个全局索引分区，正
因为这点，局部分区索引具有更高的可用性。
7. 位图索引只能为局部分区索引。
8. 局部索引(Local Index)多应用于数据仓库环境中。
9. 因为local partition index 是完全和分区表的分区属性一样， 索引分区的
局部性(在各自分区内进行索引重组，而不是整个表)导致了必须扫描所有分区才能
check到要查找的值所在的索引区域，所以local index不适合OLTP系统 。
相关资料：
http://space.itpub.net/35489/viewspace-616711
分区剪除测试：
http://space.itpub.net/35489/viewspace-684640
测试发现：只有包含有分区键作为where条件才能使用到分区剪除
全局索引global index --
1.全局索引的分区键和分区数和表的分区键和分区数可能都不相同，表和全局
索引的分区机制不一样。
2.全局索引可以分区，也可以是不分区索引，全局索引必须是前缀索引，即全局
索引的索引列必须是以索引分区键作为其前几列。
3. 全局分区索引的索引条目可能指向若干个分区，因此，对于全局分区索引，
即使只动，截断一个分区中的数据，都需要rebulid若干个分区甚至是整个索引。
4. 全局分区索引只按范围或者散列hash分区，hash分区是10g以后才支持。
5.oracle9i以后对分区表做move或者truncate时可以用update global indexes语
句来同步更新全局分区索引，用消耗一定资源来换取高度的可用性。
6.表用a列作分区，索引用b做局部分区索引，若where条件中用b来查询，那么oracle
会扫描所有的表和索引的分区，成本会比分区更高，此时可以考虑用b做全局分区索引。
7.全局索引多应用于OLTP系统中。
分区索引字典 --
DBA_PART_INDEXES 分区索引的概要统计信息，可以得知每个表上有哪些分区索引，
分区索引的類型(local/global,)
DBA_ind_partitions每个分区索引的分区级统计信息
DBA_indexes minus dba_part_indexes，可以得到每个表上有哪些非分区索引
索引重建 --
Alter index idx_name rebuild partition index_partition_name [online nologging]
需要对每个分区索引做rebuild，重建的时候可以选择online(不会锁定表),或者
nologging建立索引的时候不生成日志，加快速度。
Alter index rebuild idx_name [online nologging]
对非分区索引，只能整个index重建
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/35489/viewspace-684276/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/35489/viewspace-684276/
扫一扫
1501
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
