oracle数据库range分区以及子分区

野性酷女 2022-02-16 21:08 476阅读 1赞

###  ###

*  前言
 *   *  1、 创建包含主分区表和子分区表
     *  2、创建分区局部索引
     *  3、插入数据
     *  4、查看执行计划 （这里是解释执行计划）
     *  5、创建一个上面分区表字段数据相同的非分区表 并创建索引
     *  6、 再来查看非分区表的执行计划
     *  7、当分区表和非分区表都走索引扫描
     *  9、在分区中创建 位图索引
     *  10、 再次来同样语句的执行计划
     *  12、当位图索引对于谓语中索引列包含'<','<=','>','>=' 的执行计划
     *  13、当需要获取位图索引列时的执行计划
 *  总结

# 前言 #

Oracle建议如果单个表超过2G就最好对其进行分区，一个分区数据行不要超过30w对于大表创建分区的好处是显而易见的本例是在oracle 11g下自带样例，sh 用户下 基于sales进行分区演示。该表数据 有91w条数据

select count(1) from  sales t;

![在这里插入图片描述][20190418141302858.png]  
我们用sales 表数据来重新建表并进行分区，比较分区与不分区的效率，以及分区后分区局部索引与位图索引的效率比较。

## 1、 创建包含主分区表和子分区表 ##

sales\_part\_test  
都按照time\_id字段进行分区

create table sales_part_test
    (
      prod_id       NUMBER not null,
      cust_id       NUMBER not null,
      time_id       DATE not null,
      channel_id    NUMBER not null,
      promo_id      NUMBER not null,
      quantity_sold NUMBER(10,2) not null,
      amount_sold   NUMBER(10,2) not null
    )
    partition by range(time_id) subpartition  by range  (time_id)  --指定主表分区和子分区分区方式都是:范围分区，并按照列time_id 进行范围划分
    ( 
     partition sales_part_1998 values less than (TO_DATE('1999-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))
     tablespace   EXAMPLE
     (
       subpartition sales_part_1998_01 values less than ( TO_DATE('1998-02-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))  tablespace   EXAMPLE,--指定主分区sales_part_1998子分区 sales_part_1998_01 注意每个主分区的子分区名字不能一样
       subpartition sales_part_1998_02 values less than ( TO_DATE('1998-03-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))  tablespace   EXAMPLE,
       subpartition sales_part_1998_03 values less than ( TO_DATE('1998-04-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))  tablespace   EXAMPLE,
       subpartition sales_part_1998_04 values less than ( TO_DATE('1998-05-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))  tablespace   EXAMPLE,
       subpartition sales_part_1998_05 values less than ( TO_DATE('1998-06-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))  tablespace   EXAMPLE, 
       subpartition sales_part_1998_06 values less than ( TO_DATE('1998-07-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))  tablespace   EXAMPLE, 
       subpartition sales_part_1998_07 values less than ( TO_DATE('1998-08-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))  tablespace   EXAMPLE, 
       subpartition sales_part_1998_08 values less than ( TO_DATE('1998-09-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))  tablespace   EXAMPLE,
       subpartition sales_part_1998_09 values less than ( TO_DATE('1998-10-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))  tablespace   EXAMPLE, 
       subpartition sales_part_1998_10 values less than ( TO_DATE('1998-11-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))  tablespace   EXAMPLE, 
       subpartition sales_part_1998_11 values less than ( TO_DATE('1998-12-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))  tablespace   EXAMPLE, 
       subpartition sales_part_1998_12 values less than ( TO_DATE('1999-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))  tablespace   EXAMPLE 
    
     )
     ,                 
     partition sales_part_1999 values less than (TO_DATE('2000-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))
     tablespace EXAMPLE
     (
       subpartition sales_part_1999_01 values less than ( TO_DATE('1999-02-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))  tablespace   EXAMPLE,
       subpartition sales_part_1999_02 values less than ( TO_DATE('1999-03-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))  tablespace   EXAMPLE,
       subpartition sales_part_1999_03 values less than ( TO_DATE('1999-04-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))  tablespace   EXAMPLE,
       subpartition sales_part_1999_04 values less than ( TO_DATE('1999-05-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))  tablespace   EXAMPLE,
       subpartition sales_part_1999_05 values less than ( TO_DATE('1999-06-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))  tablespace   EXAMPLE, 
       subpartition sales_part_1999_06 values less than ( TO_DATE('1999-07-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))  tablespace   EXAMPLE, 
       subpartition sales_part_1999_07 values less than ( TO_DATE('1999-08-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))  tablespace   EXAMPLE, 
       subpartition sales_part_1999_08 values less than ( TO_DATE('1999-09-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))  tablespace   EXAMPLE,
       subpartition sales_part_1999_09 values less than ( TO_DATE('1999-10-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))  tablespace   EXAMPLE, 
       subpartition sales_part_1999_10 values less than ( TO_DATE('1999-11-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))  tablespace   EXAMPLE, 
       subpartition sales_part_1999_11 values less than ( TO_DATE('1999-12-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))  tablespace   EXAMPLE, 
       subpartition sales_part_1999_12 values less than ( TO_DATE('2000-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))  tablespace   EXAMPLE 
     ),
     partition sales_part_2000 values less than (TO_DATE('2001-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))
     tablespace EXAMPLE
     (
       subpartition sales_part_2000_01 values less than ( TO_DATE('2000-02-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))  tablespace   EXAMPLE,
       subpartition sales_part_2000_02 values less than ( TO_DATE('2000-03-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))  tablespace   EXAMPLE,
       subpartition sales_part_2000_03 values less than ( TO_DATE('2000-04-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))  tablespace   EXAMPLE,
       subpartition sales_part_2000_04 values less than ( TO_DATE('2000-05-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))  tablespace   EXAMPLE,
       subpartition sales_part_2000_05 values less than ( TO_DATE('2000-06-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))  tablespace   EXAMPLE, 
       subpartition sales_part_2000_06 values less than ( TO_DATE('2000-07-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))  tablespace   EXAMPLE, 
       subpartition sales_part_2000_07 values less than ( TO_DATE('2000-08-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))  tablespace   EXAMPLE, 
       subpartition sales_part_2000_08 values less than ( TO_DATE('2000-09-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))  tablespace   EXAMPLE,
       subpartition sales_part_2000_09 values less than ( TO_DATE('2000-10-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))  tablespace   EXAMPLE, 
       subpartition sales_part_2000_10 values less than ( TO_DATE('2000-11-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))  tablespace   EXAMPLE, 
       subpartition sales_part_2000_11 values less than ( TO_DATE('2000-12-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))  tablespace   EXAMPLE, 
       subpartition sales_part_2000_12 values less than ( TO_DATE('2001-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))  tablespace   EXAMPLE 
      ),
     partition sales_part_2001 values less than (TO_DATE('2002-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))
     tablespace EXAMPLE (
       subpartition sales_part_2001_01 values less than ( TO_DATE('2001-02-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))  tablespace   EXAMPLE,
       subpartition sales_part_2001_02 values less than ( TO_DATE('2001-03-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))  tablespace   EXAMPLE,
       subpartition sales_part_2001_03 values less than ( TO_DATE('2001-04-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))  tablespace   EXAMPLE,
       subpartition sales_part_2001_04 values less than ( TO_DATE('2001-05-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))  tablespace   EXAMPLE,
       subpartition sales_part_2001_05 values less than ( TO_DATE('2001-06-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))  tablespace   EXAMPLE, 
       subpartition sales_part_2001_06 values less than ( TO_DATE('2001-07-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))  tablespace   EXAMPLE, 
       subpartition sales_part_2001_07 values less than ( TO_DATE('2001-08-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))  tablespace   EXAMPLE, 
       subpartition sales_part_2001_08 values less than ( TO_DATE('2001-09-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))  tablespace   EXAMPLE,
       subpartition sales_part_2001_09 values less than ( TO_DATE('2001-10-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))  tablespace   EXAMPLE, 
       subpartition sales_part_2001_10 values less than ( TO_DATE('2001-11-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))  tablespace   EXAMPLE, 
       subpartition sales_part_2001_11 values less than ( TO_DATE('2001-12-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))  tablespace   EXAMPLE, 
       subpartition sales_part_2001_12 values less than ( TO_DATE('2002-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))  tablespace   EXAMPLE 
     )
     )

## 2、创建分区局部索引 ##

create index idx_sales_sales_part_test  on sales_part_test (time_id)local;

## 3、插入数据 ##

insert into   sales_part_test select  t.* from  sales  t;

## 4、查看执行计划 （这里是解释执行计划） ##

select  t.* from  sales_part_test t where t.time_id<=date'1998-05-01'

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3doYW5kZ2Ro_size_16_color_FFFFFF_t_70]  
看到最先是通过我们创建的局部索引idx\_sales\_sales\_part\_test 采用了索引范围扫描方式访问了第一个主分区  
sales\_part\_1998 的所有子分区，  
获取到数据后再通过 PARTITION RANGE ITERATOR（分区范围迭代） 方式或获取主分区 sales\_part\_1998的1-5子分区。最后 看到 只访问了一个主分区 就是 sales\_part\_1998 同时看到分区表的逻辑读是38个

## 5、创建一个上面分区表字段数据相同的非分区表 并创建索引 ##

create table  sales_nopart as select  t.* from  sales t;
    create index idx_sales_nopart   on sales_nopart (time_id) ;

## 6、 再来查看非分区表的执行计划 ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3doYW5kZ2Ro_size_16_color_FFFFFF_t_70 1]  
可以看到 执行计划并没有选择索引扫描，而是选择了全部扫描，而且成本是分区表的1243倍，耗时是15倍左右  
分区表的一致性读只有38个，物理读没有，而不是分区表的逻辑读即一致性读是8227个，物理读是4166个。性能高低立见高低。

## 7、当分区表和非分区表都走索引扫描 ##

把两个表都进行分析

exec dbms_stats.gather_table_stats('sh','SALES_PART_TEST');
    exec dbms_stats.gather_table_stats('sh','SALES_NOPART');

来看如下语句的执行计划

select  t.* from  sales_part_test t where t.time_id=date'1998-05-01'

分区表执行计划如下:  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3doYW5kZ2Ro_size_16_color_FFFFFF_t_70 2]

非分区表执行计划

select  t.* from  sales_nopart t where t.time_id=date'1998-05-01'

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3doYW5kZ2Ro_size_16_color_FFFFFF_t_70 3]  
通过上面的结果，可以看到分区表和非分区表都走了索引范围扫描，但是非分区表逻辑读是58个 成本是42 依然比  
分区表的 54个逻辑读和33的成本要多。

## 9、在分区中创建 位图索引 ##

看到这里我们还不能满足，我们发现其实分区中time\_id重复值太多了 ，我们可以尝试把b树索引改为创建位图索引

drop  index idx_sales_sales_part_test;
    create bitmap index idx_sales_sales_part_test  on sales_part_test (time_id)local;
    --注意位图索引必须创建为局部索引

## 10、 再次来同样语句的执行计划 ##

--先分析表
         exec dbms_stats.gather_table_stats('sh','SALES_PART_TEST');
         select  t.* from  SALES_PART_TEST t where t.time_id=date'1998-05-01'

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3doYW5kZ2Ro_size_16_color_FFFFFF_t_70 4]  
从结果中看到 创建位图索引后，oracle选择了位图索引进行唯一值扫描，并只扫描了一个第五个子分区，成本只有4，而且逻辑读只用了36个，这个才是最优选择？  
但是………… 当我们执行以下语句时，执行计划还会比b树索引的成本低吗？

## 12、当位图索引对于谓语中索引列包含’<’,’<=’,’>’,’>=’ 的执行计划 ##

select  t.* from  SALES_PART_TEST t where t.time_id<=date'1998-05-01'

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3doYW5kZ2Ro_size_16_color_FFFFFF_t_70 5]

结果让我们大跌眼镜，此时，数据库最新并没有选择走索引，而是对主分区sales\_part\_1998 选择了全表扫描  
，其他分区是扫描都是一样的，但是总的逻辑读是走B树索引38 个的10倍多。所以对于数据库并没有绝对的最优，都是相对的。到这里我们会有个疑问：为啥没走位图索引？  
对于走位图索引 需要 谓语中 有位图索引列 且谓语是 “=”  
需要区别的是对于B树索引， 对非唯一索引列上进行的任何查询 就会走 索引范围扫描 见 step 4 和step 7 结果都证实了这一点。

## 13、当需要获取位图索引列时的执行计划 ##

要获取列的列表可以通过其中一列的位图索引来获得 这里走位图索引范围扫描  
我们通过把上面的sql 中\* 改为 t.cust\_id,t.time\_id,t.prod\_id 再来看执行计划

select t.cust_id,t.time_id,t.prod_id from  SALES_PART_TEST t where t.time_id <=date'1998-05-01'

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3doYW5kZ2Ro_size_16_color_FFFFFF_t_70 6]  
可以看到执行计划果然选择了位图索引范围扫描，尽管依然扫描了主分区sales\_part\_1998 的所有子分区  
但是看到逻辑读只有122个 ，仅仅是B树索引范围扫描的4倍不到，跟全表扫描相比实在好了很多。  
需要注意的位图索引需要慎重使用，位图索引适合数据仓库，位图索引特别不适经常DML操作的线上系统。

# 总结 #

遇到问题就要刨根到底，一层一层的解开自己的疑惑，本文开始仅仅是想验证对于数据量大的表，分区比不分区查询  
效率更高，但是到后面引申了分区表中建B树索引和位图索引的效率比较，同时也回顾了索引扫描的  
条件，同时也学到了凡是都没有绝对的最好，需要辩证来看待问题。

[20190418141302858.png]: /images/20220217/0a7e92e7f6e648368b350ef2e413c163.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3doYW5kZ2Ro_size_16_color_FFFFFF_t_70]: /images/20220217/8b68d495b80647698f148103159bbbbd.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3doYW5kZ2Ro_size_16_color_FFFFFF_t_70 1]: /images/20220217/c284bd073bad482e89d152747ddd13e2.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3doYW5kZ2Ro_size_16_color_FFFFFF_t_70 2]: /images/20220217/a274867a2acd4ef7bd4a6b4d80ee0f38.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3doYW5kZ2Ro_size_16_color_FFFFFF_t_70 3]: /images/20220217/7aed0995384843d4940da9a97e8663c3.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3doYW5kZ2Ro_size_16_color_FFFFFF_t_70 4]: /images/20220217/88c4208b1b7a4dc58de1466ce5735824.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3doYW5kZ2Ro_size_16_color_FFFFFF_t_70 5]: /images/20220217/a4d67f8f984243798d58537ac272419d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3doYW5kZ2Ro_size_16_color_FFFFFF_t_70 6]: /images/20220217/013065e10c424ac1b8cd5a936e9710a5.png