Oracle分区知识介绍-下集

六、分区裁剪（Partition pruning）
概念：
1、分区裁剪（Partition pruning），又叫分区修剪、分区消除，是指在对Oracle分区表进行查询时，优化器会检查谓词条件中是否存在对分区段的过滤，如果存在，则可以仅访问符合条件的分区，即裁剪掉无用的分区。
2、分区裁剪（Partitionpruning）是根据查询语句中的谓词条件来进行分区消除的，分区裁剪既可以在分区级别消除， 也可以在分区上的索引级别消除。
3、范围和列表分区上可以在谓词上使用等于，范围操作（><等）Like语句和in子句进行分区消除。
4、哈希分区表上只能使用等于和In 操作进行分区消除。

示例1：

Select * from time_range_slaes WHERE time_id<to_date(‘1999-01-01’,’YYYY-MM-DD);
以上这句SQL是对分区表进行查询，通过使用谓词条件进行分区裁剪，只查看19990101之前的数据，
可以通过查询计划查看
Explain plan for 
Select * from time_range_slaes WHERE time_id<to_date(‘1999-01-01’,’YYYY-MM-DD);
再查询数据字典输出执行计划
Select * from table(dbms_xplan.display);
需要注意执行计划里面的两个字段，（pstart,pstop）都表示只对一个分区进行了扫描和查询（pstart表示从1开始，pstop表示从1结束），
虽然是table access full，但只是针对谓词条件所筛选的分区，可以创建4个不同时区的范围分区进行测试。

示例２：

Select * from time_range_slaes WHERE time_id<to_date(‘1999-01-01’,’YYYY-MM-DD) and channel_id=’2’;
以上这句SQL是对分区表存在子分区的情况下，通过使用谓词条件进行分区裁剪，同样我们使用执行计划来观察分区裁剪的意义。
Explain plan for 
Select * from time_range_slaes WHERE time_id<to_date(‘1999-01-01’,’YYYY-MM-DD) and channel_id=’2’;
再查询数据字典输出执行计划
Select * from table(dbms_xplan.display);

从上述执行计划看到这个查询时满足了第一个分区的同时也满足了第一个子分区（‘2’，‘4’），那接下来看执行计划：
首先我们范围分区已经满足了条件，是找到了第一个分区（PARTITION RANGE SINGLE，pstart=1，pstop=1）,接着继续往下看， 我们的子分区是通过列表分区（partition list single）来划分的，所以直接看id =2和3两行记录，就能知道虽然是全表扫描 但通过谓词条件裁剪后，优化器找到了满足第一个分区后又满足第二个子分区的条件（pstart=1,pstop=1）

七、本地分区索引（Local Partitioned Index）
概念：

1. 同分区表类似，索引也可以分区，称为分区索引。分区索引又分为本地分区索引和全局分区索引。
2. 本地分区索引就是按照分区表的分区，在每个分区上创建相应的索引。每个表分区都有一个相应 的索引分区，并且每个索引只负责该表分区数据的检索。
3. 全局分区索引不依赖于分区表的分区，在全表上创建索引。全局分区索引也可以对索引进行分区， 但可以和分区表的分区不一致。
4. 本地分区索引通常用在DSS系统、数据仓库，可维护性高。
5. 全局分区索引通常用于OLTP系统，有更好的随机选择性能。
6. 本地分区索引优势（高可用、方便维护、易于恢复）
7. 本地分区索引又分为两类，本地前缀索引（prefixed）和本地非前缀索引（no prefixed）。

示例1：

--判断本地分区索引是前缀还是非前缀的依据是否作为索引的引导列。
--创建本地前缀分区索引：
Create index hash_sales_idx1 on Time_Range_Sales(time_id)LOCAL;
Create index hash_sales_idx2 on Time_Range_Sales(time_id,Channel_id)LOCAL; 
--查看分区索引信息：
Select * from User_Part_Indexes;
Select * from User_Ind_Partitions; 
--创建本地非前缀分区索引：
Create index hash_sales_idx3 on Time_Range_Sales(Channel_id)LOCAL;
Create index hash_sales_idx4 on Time_Range_Sales(Channel_id,time_id)LOCAL; 
--查看分区索引信息：
Select * from User_Part_Indexes;
Select * from User_Ind_Partitions; --注意algnment字段的值会标记该索引为前缀索引还是非前缀索引
--不管是创建的非前缀索引还是前缀索引，通过User_Ind_Partitions会发现只要创建一个本地索引，
--就会按照分区表所拥有的分区数量进行分区索引创建，假如有四个分区，那就会有四个分区索引。

示例2：

本地前缀索引分区裁剪
分区裁剪除了可以在表分区级别发生，也可以在索引分区级别发生。---与表分区上的分区消除类似，当
Oracle优化器通过分区索引访问数据，并且通过谓词条件确定可以消除不需要的索引分区时，便会进行索引上的分区裁剪。
本地前缀索引分区裁剪：
Explain plan for 
Select * from time_range_slaes WHERE time_id<to_date(‘1999-01-01’,’YYYY-MM-DD);
再查询数据字典输出执行计划
Select * from table(dbms_xplan.display);

从上一章可以看到我们走了表分区后，这里我们多了个分区索引，同时满足了谓词条件（Hash_SALES_IDX1）,所以就查询到了第一个分区（pstart=1，pstop=1），而不会显示出其他分区，只用扫描索引，而不用扫描表，更进一步提高了优化器的检索速度， 如果日期改成了2000年，则会走表分区扫描，而不会走索引扫描。

示例3：

本地非前缀索引分区裁剪
Create index hash_sales_idx2 on Time_Range_Sales(Channel_id, time_id)LOCAL;
Explain plan for 
Select * from time_range_slaes WHERE time_id<to_date(‘1999-01-01’,’YYYY-MM-DD) and channel_id=’2’;
再查询数据字典输出执行计划
Select * from table(dbms_xplan.display);

可以看到谓词条件加入了channel_id的条件后，一样会满足索引扫描条件。这里尝试更换日期2000也会执行索引扫描，因为满足了channel_id的条件。

七、全局分区索引（Global Partitioned Index）
概念：
1、 全局分区索引和分区表的分区信息独立不相干，是在全表级别创建的索引类型。
2、 全局分区索引非常适合OLTP系统，能够极大地提高查询性能。
3、 全局分区索引可以以表中任意的列为索引列，索引列可以不是分区表的分区键。

示例1、

--在分区表上创建全局分区索引：
Create index time_channel_sales_idx on time_range_sales(channel_id) global
Partition by range(channel_id)
(partition p1 values less than (3),
partition p1 values less than (4),
partition p1 values less than (maxvalue)
);
--查询索引信息：
Select * from user_part_indexes;
Select * from user_ind_paertitions;

示例2、

--在普通表上创建全局分区索引；
Create table sales_tb (
prod_id  number(6),
cust_id  number,
time_id  date,
channel_id char(1),
promo_id number(6),
quantity_sold number(3),
amount_sold number(10,2)
)
Create index sales_tb_idx1 on sales_tb(channel_id) global
Partition by range(channel_id)
(partition p1 values less than (3),
partition p1 values less than (4),
partition p1 values less than (maxvalue)
);
表和索引的分区都是独立的，表没有分区，但是一样可以建立分区索引，简称为全局分区索引，并且全局分区索引列的前缀列和表分区的列必须相同。
示例3、
全局分区索引上的分区裁剪：
Explain plan for select * from time_range_sales where channel_id = ‘2’;
Select * from table(dbms.xplan.display);
 
可以看到同样是满足了谓词条件后，这里的选择项也一样会把全局索引当成分区索引使用。

九、分区表常用维护操作

概念： 在上面的八大例子中，已经基本将Oracle分区表详细介绍了一遍，本节会对上述的八大点所涵盖的知识点进行一个总结巩固，如下：
创建分区表
添加分区
删除分区
子分区
合并分区
拆分分区
截断分区
重命名分区
移动分区
分区信息查询

以下我们会用列表分区表对我们所掌握的知识点进行演示：

1. 创建列表分区表：

Create table list_sales(
prod_id  number(6),
cust_id  number,
time_id  date,
channel_id char(1),
promo_id number(6),
quantity_sold number(3),
amount_sold number(10,2)
)
partition by list(channel_id)
(
partition channel_01 values (1) tablespace users, --指定分区数据所存放的表空间，可以将不同的分区存放在不同的表空间当中。
partition channel_02 values (2) tablespace users,
partition channel_03 values (3) tablespace users,
partition channel_04 values (4) tablespace example
);
select * from dba_part_tables where table_name='LIST_SALES';
select * from dba_tab_partitions where table_name='LIST_SALES';

2. 添加分区：

接着上面创建的分区，再手动添加一个新的分区，后面的表空间可以指定也可以不指定。
Alter table list_sales add partition channel_05 values (5) tablespace example; 
select * from dba_tab_partitions where table_name='LIST_SALES';

这里有一点注意，当我们列表分区已经常见了default分区时，就不能再手动增加分区了，如果添加了会报ora-14323的错误，从上述的分区来看我们对应的1-5的values值会存放在对应的分区当中，但是当出现这6这些不存在分区键值的值出现时，这时 我们假如指定了default分区（partition channel_other values (default) tablespace other），这些数据就会存放在这个分区里， 而如果我们手动增加了个分区（Alter table list_sales add partition channel_06 values (6) tablespace example; ）时，就会出现ora-14323的错误了，因为6的分区键值已经包含在default里面了，所以存在default分区时，不能再新增新的分区。

3. 删除分区/删除子分区：

Alter table list_sales drop partition channel_05; --删除我们刚刚新增的分区
Alter table sales drop subpartition p2sub3; --假设我们有张sales表里有子分区（p2sub3），可以通过该语句删除
Select * from dba_tab_partitions where table_name = ‘LIST_SALES’;

4. 合并分区：

--将channel_01和channel_02分区和数据通过merge关键字合并成一个名为channel_01的新分区，而新的数据会跟进旧的分区键值合并插入到新的分区当中，channel_01和02的键值为2和3，
那新的channel_01分区的键值就是values（2,3）
Alter table list_sales merge partitions channel_01,channel_02 into partition channel_01;

5. 拆分分区：

--将channel_02拆分为04和新02两个分区，满足1和9的值会存放在04分区当中，除此之外其他值存放在新02分区中。
Alter table list_sales split partition channels_02 values(1,9) into (partition channels_04,partition channels_02);

6. 截断分区/子分区：

--假如现在我们有个分区或者子分区的数据不想要了，可以通过该命令清除，数据没了但是分区还是存在，需要用drop才能删除分区。
Alter table list_sales truncate partition channel_02;
Alter table list_sales truncate subpartition p2sub2;

7. 重命名分区/子分区：

Alter table list_sales rename partition channel_02 to channel_10;
Alter table list_sales rename subpartition p2sub2 to p10sub10;
Select * from dba_tab_partitions where table_name = ‘LIST_SALES’;

8. 移动分区/子分区：

--因为分区也是表，而表是依赖于表空间的基础上存在的，当我们分区的所在表空间满了或者需要将该表分区迁移到别的表空间时，可以使用以下命令：
Alter table list_sales move partition channel_02 tablespace users2; --将channel_02的所在表空间进行逻辑迁移到tablespace users02上。
Select * from dba_segments where segment name = ‘LIST_STALES’; --可以通过段表查看，一个分区占用一个段。

9. 分区信息查询：

--分区表/分区索引相关数据字典：
Select * from dba_tables where partitioned=’YES’; --查看数据库的分区表。
Select * from dba_segments where segment_name=’LIST_SALES’;--查看数据库的段信息，一个分区会占用1个段，一个段默认是8k大小，存储在硬盘上。
Select * from dba_part_tables; --查看数据库分区表的信息。
Select * from dba_tab_partitions where table_name=’LIST_SALES’; --查看分区表的分区信息。
Select * from dba_tab_subpartitions; --查看数据库分区表的子分区信息。
Select * from dba_part_key_columns; --查看分区表的分区列信息。
Select * from dba_subpart_key_columns; --查看分区表的子分区列信息。
Select * from dba_part_indexes; --查看分区索引信息。
Select * from dba_ind_partitions; --查看分区索引的分区信息。

总结：
**范围分区：**会根据值与值之间的范围，进行分区写入。
**列表分区：**会根据散列的固定值，进行分区写入。
**哈希分区：**会根据Oracle内部的哈希算法规则由其自行控制分区写入。
**复合分区：**当表的分区策略不满足承载现有数据压力的情况下，可以对现有分区再进行分区。
**间隔分区：**其实间隔分区就类似范围分区的一个插件，本质上还是范围分区，提供自动创建分区的新特性，只有Oracle11g以上才有。
**分区裁剪：**分区裁剪目的就是为了突出分区的意义，让优化器会通过谓词条件选择合适的分区进行结果返回，而不用进行全表扫描。
**本地分区索引：**本地分区索引会为表分区每个分区建立独立的索引，假如某个表分区损坏了，也不会对另外几个分区和分区索引产生影响，本地分区索引创建会根据表分区数量同样创建对应的segment段，而不是只生成一个，所以达到高可用效果并且易于维护。
**全局分区索引：**全局分区索引不依赖于表上的分区，表索引的数量也可以和分区数量不用匹配，当查询的条件是需要跨分区查询内容的时候，LOCAL INDEX的效率比GLOBAL INDEX的效率要低，通过consistent gets和db block gets的对比可以看出来；如果查询的条件是在单个分区里面查询的时候，那么LOCAL INDEX的效率比GLOBAL INDEX的效率要高。