Oracle数据库分区技术经验分享

27
分区裁剪功能
99-Jan
99-Feb 99-Mar 99-Apr 99-May 99-Jun
Partition pruning: Only the relevant partitions are accessed.
SQL> 2 3 4 5 6 7 SELECT SUM(sales_amount) FROM sales WHERE sales_date BETWEEN TO_DATE(‘01-MAR-1999’, ‘DD-MON-YYYY’) AND TO_DATE(‘31-MAY-1999’, ‘DD-MON-YYYY’);
local 分区
• 本地索引的分区和其对应的表分区数量相等，因此每个表分区都对应着相应的索 引分区。 • 为表新添加表分区时，会自动添加相应的索引分区。 • 本地索引可以创建成本地非前缀型（分区的键值跟表一致，而索引的键值与之不 同）和前缀型。 • 例子： create index inx_range on range_love2008(datadate) local (partition p0 tablespace users, partition p1 tablespace users, partition p2 tablespace users, partition p3 tablespace users)
21
分区索引选择策略
22
<Insert Picture Here>
3、分区操作
23
分区表的管理功能
• 修改分区的Default Attribute • 分区的增加（ADD） • 分区的清空（TRUNCATE） • 分区的交换（EXCHANGE） • 分区的移动（MOVE） • 分区的更名（RENAME） • 分区的删除（DROP）（no hash） • 分区的合并（MERGE）（no hash） • 分区的分离（SPLIT）（no hash） • 分区的接合（COALESE）（only hash）
17
Types of Index Partitioning
•Local (Partitioned)

Pre-Fixed(前缀型）
o
分区键值是索引的键值或索引键值的第一个字段 分区键值不是索引键值或索引键值的第一个字段
Non Pre-Fixed（非前缀型）
o
•Global (Partitioned)
Oracle 数据库分区技术经验分享
大唐软件技术股份有限公司 刘兵兵
议题：
1. 分区技术的现状分析 2. 分区技术简介和最新的发展 3. 分区设计经验分享
2
<Insert Picture Here>
议题一：分区技术的运而生
分区技术是为大表和大索引而设的。

Index is Partitioned using Range or Hash 索引分区键值必须与索引键值相同或左前缀（前缀型）
•Global (Non-Partitioned)

Normal B-Tree index
18
global index
• • 全局索引以整个表的数据为对象建立索引 全局索引和表之间没有直接的联系，这一点和本地索引不同。 • 例1:CREATE INDEX month_ix ON sales(sales_month) GLOBAL PARTITION BY RANGE(sales_month) (PARTITION pm1_ix VALUES LESS THAN (2) PARTITION pm2_ix VALUES LESS THAN (3) PARTITION pm3_ix VALUES LESS THAN (4) PARTITION pm4_ix VALUES LESS THAN (5) PARTITION pm5_ix VALUES LESS THAN (6) PARTITION pm6_ix VALUES LESS THAN (7) PARTITION pm7_ix VALUES LESS THAN (8) PARTITION pm8_ix VALUES LESS THAN (9) PARTITION pm9_ix VALUES LESS THAN (10) PARTITION pm10_ix VALUES LESS THAN (11) PARTITION pm11_ix VALUES LESS THAN (12) PARTITION pm12_ix VALUES LESS THAN (MAXVALUE)); • 例2：CREATE INDEX hgidx ON tab (c1,c2,c3) GLOBAL PARTITION BY HASH (c1,c2) (PARTITION p1 TABLESPACE tbs_1, PARTITION p2 TABLESPACE tbs_2); 19
• • • •
<Insert Picture Here>
2、分区索引
14
分区索引
索引 表 不分区 分区
不分区
√ √
15
√ √
分区
不同的分区索引
Global Nonpartitioned index Global Partitioned Index
杭州 绍兴 温州
03年
04年
…
08年
Local partitioned index
8
使用的现状分析
• 设计上思考不够，为分区而分区。
– 比如，哪些表需要做分区？分区字段该选哪个？是不是进行压 缩存储？
• 分区类型简单
– 分区类型没有经过深思熟虑
• 索引类型键值的选择不合适，索引分区的使用存在误区。
– 往往是表做了分区就完成了，索引的设计很单一和随意。
9
<Insert Picture Here>
为提高性能和可管理性而设的。
分区要根据实际情况精心设计。
4
什么是分区？ – Simple Yet Powerful
Large Table
管理困难
Partition
分割存放 易于管理 提高性能
复合 Partition
比较好的性能 针对业务需求有更好的灵 活性
•应用透明 •支持多个存储位置，支持不同的物理存储特性
议题二：分区技术简介和最新的发展
10
分区表与非分区表的区别
– 分区表及非分区表既可以使用分区索引，也可以使用非分区索引。 – 分区技术是针对表，索引组织表和索引的技术。
11
<Insert Picture Here>
1、分区类型
12
分区类型
• • • • 范围分区（Range Partitioning） 哈希分区（Hash Partitioning） 列表分区（List Partitioning） 间隔分区（Interval Extension to Range Partitioning (11g) ） – （PARTITION BY RANGE(XXX) – INTERVAL (NUMTOMYINTERVAL(1,’MONTH’) 参考分区（Referential Partitioning (11g)） – (PARTITION BY REFERENCE(外键约束）） 虚拟字段分区（Virtual Column Partitioning (11g)） – （先定义一个虚拟字段YY AS ..，然后 PARTITION BY YY） 系统分区（system partition（11g） – （ PARTITION BY SYSTEM ，由insert指定插入哪个分区） 复合分区 • Range-Hash, Range-List • Extensions in Oracle Database 11g – List-Range – Range-Range – List-Hash 13 – List-List
建立分区索引的一般规则
• 1）OLTP
– 全局分区索引和本地前缀索引比本地非前缀索引 提供更好的性 能。因为他们可以探查更少的索引分区。 – 当有表的分区或子分区操作维护的时候，local索引提供更好的 可用性；对于有历史归档数据的表，local的非前缀索引有用处。
• 2）DSS 本地非前缀索引可以提高性能，因为数据大，很多分 区必然会被并行的查询。 • 3）对于历史表 索引必须是local的，这样在有规律的drop 分区的时候， 比较方便。 • 4）在非分区字段上建立unique索引只能建全局的索引。 因为不包含分区键的唯一本地非前缀索引是不支持的。
5
分区的一些限制
• 表或索引的所有分区必须具备相同的逻辑结构
例如列名（column name），数据类型（datatype），及数 据约束（constraint）等
• 但每个分区的物理属性可以不同
例如 pctfree，pctused，及表空间等。
• 表空间的块大小
– 一个分区对象的所有分区必须存储在数据块容量（block size）相同的表空间中。 – 但一个表的不同的分区索引可以放在不同块大小的表空间中。
压缩： CREATE INDEX i_cost1 ON costs_demo (prod_id) COMPRESS LOCAL (PARTITION costs_old, PARTITION costs_q1_2003, PARTITION costs_q2_2003, PARTITION costs_recent NOCOMPRESS); • 如果建立分区索引时没有指定默认的表空间，那么新增的索引分区所在表空间和 新增表分区所在表空间保持一致。 20
4、 利用分区技术 提高性能
26
分区裁剪
• 分区剪除（partition pruning）就是在查询中依据from和 where条件跳过不必要的分区及子分区 。 • 分区裁剪分为静态和动态两种。 – 静态裁剪在编译时进行-where条件有分区键值 – 动态裁剪在运行时进行-where条件中有操作或函数， 绑定变量等 • 分区裁剪技术同样可以优化分区索引。 – 如果表及其上的索引依据不同列进行分区，在查询器 优化查询时即使不能对表分区进行剪除，也可以对索 引分区进行剪除。