Oracle 12C新特性-分区表局部分区索引

May112018

作者：dbdream 发布：2018-05-11 17:39 分类：Oracle, Oracle 12c, 未分类抢沙发

Oracle 12C开始支持在分区表的指定分区上创建索引，全局索引和本地索引都可以创建指定分区的索引，但是主键和唯一索引除外，因为主键和唯一索引要确认数据的唯一性，如果只创建部分分区的索引就无法校验数据的一致性，所以主键和唯一索引不支持局部索引。

这是一个很有用的功能，就拿我们现有的一套核心的数据库来说，订单表的数据包含2013年至今(2018-04-28)的数据，这其中绝大部分都是历史数据，这些历史数据被查询的概率极低，但是创建索引时，这部分数据还无法规避，而订单表的索引还很多，大部分还是很多列的复合索引，这就导致了，索引的总大小是表大小的二倍还多，和领导提了很多次归档一下历史订单数据，而开发人员不配合，领导也害怕系统出问题而一直没有执行。

这种情况，Oracle 12C提出的这个局部索引就很有用，当然Oracle 12C还推出了数据生命周期这个新特性也很适合这种场景，本文主要测试局部索引特性，数据生命周期特性请听下回分解。

局部索引是在分区的属性中指定的，通过INDEXING为OFF或ON来控制，默认是ON。可以在建表时指定，也可以通过ALTER TABLE命令进行设置，下面先测试下建表的时候指定局部索引。测试表创建SQL如下：

SQL> create table t_test(ORD_ID  VARCHAR2(40),CST_ID VARCHAR2(40),INST_DTM DATE,BIZ_UNT_CD VARCHAR2(10))
  2  indexing on
  3  partition by range(INST_DTM)
  4  (partition p1 values less than(to_date('2014-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) INDEXING OFF,
  5  partition p2 values less than(to_date('2015-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) INDEXING OFF,
  6  partition p3 values less than(to_date('2016-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) INDEXING ON,
  7  partition p4 values less than(maxvalue));

Table created.

默认情况下，INDEXING为ON，可以通过user_tab_partitions视图来查看这些信息。

SQL> select TABLE_NAME,PARTITION_NAME,NUM_ROWS,INDEXING from user_tab_partitions where TABLE_NAME='T_TEST';

TABLE_NAME PARTITION_NAME    NUM_ROWS INDE
---------- --------------- ---------- ----
T_TEST     P1                 2894575 OFF
T_TEST     P2                 7545716 OFF
T_TEST     P3                 9883824 ON
T_TEST     P4                 3242359 ON

因为在建表时明确指定了INDEXING为ON(上文建表语句的第二行)，所以P4分区即使没有指定INDEXING，也使用了ON，因为建表时INDEXING默认值是ON，上文也可以不写INDEXING ON，也就是如果INDEXING为ON的时候写不写第二行都是一样的，如下：

SQL> drop table T_TEST purge;

Table dropped.
SQL> create table t_test(ORD_ID  VARCHAR2(40),CST_ID VARCHAR2(40),INST_DTM DATE,BIZ_UNT_CD VARCHAR2(10))
  2  partition by range(INST_DTM)
  3  (partition p1 values less than(to_date('2014-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) INDEXING OFF,
  4  partition p2 values less than(to_date('2015-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) INDEXING OFF,
  5  partition p3 values less than(to_date('2016-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) INDEXING ON,
  6  partition p4 values less than(maxvalue));

Table created.
SQL> select TABLE_NAME,PARTITION_NAME,INDEXING from user_tab_partitions where TABLE_NAME='T_TEST';

TABLE_NAME PARTITION_NAME  INDE
---------- --------------- ----
T_TEST     P1              OFF
T_TEST     P2              OFF
T_TEST     P3              ON
T_TEST     P4              ON

同样，如果建表时，指定了INDEXING为OFF，那么不指定INDEXING的分区将会是使用OFF。

向测试表中插入数据并收集下统计信息，不收集统计信息，可能会导致接下来的测试SQL的执行计划不准确。

SQL> insert into t_test select ORD_ID,CST_ID,INST_DTM,BIZ_UNT_CD from t_test01;

23566474 rows created.
SQL> commit;

Commit complete.
SQL> exec dbms_stats.gather_table_stats('DBDREAM','T_TEST');

PL/SQL procedure successfully completed.

这并不是说这样创建的索引就是局部索引了，这仅仅是设置了局部索引的开关而已，如果按照以前的SQL创建索引，还是所有分区都会创建索引的。

在12C版本开始，创建索引的语法同样也多了INDEXING选项，FULL和PATTIAL，默认值是FULL，也就是所有分区都创建索引。

当创建索引时，指定INDEXING的值为PARTIAL时，只有INDEXING为ON的分区才会创建索引，INDEXING为OFF的分区不会创建索引。也就是说，只有指定INDEXING的值为PARTIAL时，分区的INDEXING为ON/OFF这个开关才起作用。

下面测试一下不知道INDEXING时，创建一个全局索引。

SQL> CREATE INDEX IX_CST_ID ON T_TEST(CST_ID) GLOBAL;

Index created.

通过USER_INDEXES视图可以查看出索引的INDEXING信息。

SQL> SELECT INDEX_NAME,BLEVEL,LEAF_BLOCKS,INDEXING FROM USER_INDEXES WHERE INDEX_NAME='IX_CST_ID';

INDEX_NAME     BLEVEL LEAF_BLOCKS INDEXIN
---------- ---------- ----------- -------
IX_CST_ID           2       77430 FULL

可以看到，在不指定INDEXING时创建索引，默认使用的是FULL，下面在创建一个INDEXING为FULL的全局索引。

SQL> drop index IX_CST_ID;

Index dropped.
SQL> CREATE INDEX IX_CST_ID ON T_TEST(CST_ID) GLOBAL INDEXING FULL;

Index created.
SQL> SELECT INDEX_NAME,BLEVEL,LEAF_BLOCKS,INDEXING FROM USER_INDEXES WHERE INDEX_NAME='IX_CST_ID';

INDEX_NAME     BLEVEL LEAF_BLOCKS INDEXIN
---------- ---------- ----------- -------
IX_CST_ID           2       77430 FULL

在创建一个INDEXING为PARTIAL的全局索引。

SQL> drop index IX_CST_ID;

Index dropped.
SQL> CREATE INDEX IX_CST_ID ON T_TEST(CST_ID) GLOBAL INDEXING PARTIAL;

Index created.
SQL> SELECT INDEX_NAME,BLEVEL,LEAF_BLOCKS,INDEXING FROM USER_INDEXES WHERE INDEX_NAME='IX_CST_ID';

INDEX_NAME     BLEVEL LEAF_BLOCKS INDEXIN
---------- ---------- ----------- -------
IX_CST_ID           2       43344 PARTIAL

可以看到LEAF_BLOCKS明显减少，下面在看一下LOCAL索引，先创建一个INDEXING为FULL的本地索引。

SQL> drop index IX_CST_ID;

Index dropped.
SQL> CREATE INDEX IX_CST_ID ON T_TEST(CST_ID) GLOBAL INDEXING FULL;

Index created.

通过user_ind_partitions视图查看索引信息，会发现4个分区都创建的了索引。

SQL> select INDEX_NAME,PARTITION_NAME,SEGMENT_CREATED from user_ind_partitions where INDEX_NAME='IX_CST_ID';

INDEX_NAME PARTITION_NAME  SEG
---------- --------------- ---
IX_CST_ID  P1              YES
IX_CST_ID  P2              YES
IX_CST_ID  P3              YES
IX_CST_ID  P4              YES

在创建一个INDEXING为PARTIAL的局部分区索引。

SQL> drop index IX_CST_ID;

Index dropped.
SQL> CREATE INDEX IX_CST_ID ON T_TEST(CST_ID) LOCAL INDEXING PARTIAL;

Index created.

通过user_ind_partitions视图查看索引信息，会发现只有INDEXING为ON的分区上被创建了索引。

SQL> select INDEX_NAME,PARTITION_NAME,SEGMENT_CREATED from user_ind_partitions where INDEX_NAME='IX_CST_ID';

INDEX_NAME PARTITION_NAME  SEG
---------- --------------- ---
IX_CST_ID  P1              NO
IX_CST_ID  P2              NO
IX_CST_ID  P3              YES
IX_CST_ID  P4              YES

下面看一下执行计划是什么样的，先看一下INDEXING为FULL的索引的执行计划。

SQL> drop index IX_CST_ID;

Index dropped.
SQL> CREATE INDEX IX_CST_ID ON T_TEST(CST_ID) LOCAL INDEXING FULL;

Index created.
SQL> select count(*) from T_TEST where INST_DTM between to_date('2015-01','yyyy-mm') and to_date('2015-12','yyyy-mm') and CST_ID='10';

  COUNT(*)
----------
         2

Execution Plan
----------------------------------------------------------
Plan hash value: 3617685272

-----------------------------------------------------------------------------------------------------------------
| Id | Operation                                   |Name     |Rows |Byte |Cost (%CPU)|Time    | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------
|   0| SELECT STATEMENT                            |         |    1|   17|     7  (0)|00:00:01|       |       |
|   1|  SORT AGGREGATE                             |         |    1|   17|           |        |       |       |
|   2|   PARTITION RANGE SINGLE                    |         |    4|   68|    7   (0)|00:00:01|     3 |     3 |
|*  3|    TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| T_TEST  |    4|   68|    7   (0)|00:00:01|     3 |     3 |
|*  4|     INDEX RANGE SCAN                        |IX_CST_ID|    4|     |    3   (0)|00:00:01|     3 |     3 |
-----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("INST_DTM"<=TO_DATE(' 2015-12-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
   4 - access("CST_ID"='10')

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          5  consistent gets
          2  physical reads
          0  redo size
        542  bytes sent via SQL*Net to client
        608  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

可以看到，INDEXING为FULL的索引，执行计划没有什么变化，在看一下INDEXING为PARTIAL时的索引的执行计划。

SQL> drop index IX_CST_ID;

Index dropped.
SQL> CREATE INDEX IX_CST_ID ON T_TEST(CST_ID) LOCAL INDEXING PARTIAL;

Index created.
SQL> select count(*) from T_TEST where INST_DTM between to_date('2015-01','yyyy-mm') and to_date('2015-12','yyyy-mm') and CST_ID='10';

  COUNT(*)
----------
         2

Execution Plan
----------------------------------------------------------
Plan hash value: 2238460597

-----------------------------------------------------------------------------------------------------------------
| Id | Operation                                     |Name     |Rows |Bytes|Cost (%CPU)|Time    | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------
|   0| SELECT STATEMENT                              |         |    1|   17|    7   (0)|00:00:01|       |       |
|   1|  SORT AGGREGATE                               |         |    1|   17|           |        |       |       |
|   2|   VIEW                                        |VW_TE_2  |    5|     |    7   (0)|00:00:01|       |       |
|   3|    UNION-ALL                                  |         |     |     |           |        |       |       |
|   4|     PARTITION RANGE SINGLE                    |         |    4|   68|    7   (0)|00:00:01|     3 |     3 |
|*  5|      TABLE ACCESS BY LOCAL INDEX ROWID BATCHED|T_TEST   |    4|   68|    7   (0)|00:00:01|     3 |     3 |
|*  6|       INDEX RANGE SCAN                        |IX_CST_ID|    4|     |    3   (0)|00:00:01|     3 |     3 |
|*  7|     FILTER                                    |         |     |     |           |        |       |       |
|   8|      PARTITION RANGE EMPTY                    |         |    1|   17|    2   (0)|00:00:01|INVALID|INVALID|
|*  9|       TABLE ACCESS FULL                       |T_TEST   |    1|   17|    2   (0)|00:00:01|INVALID|INVALID|
-----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - filter("INST_DTM"<=TO_DATE(' 2015-12-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
   6 - access("CST_ID"='10')
   7 - filter(NULL IS NOT NULL)
   9 - filter("CST_ID"='10' AND "INST_DTM">=TO_DATE(' 2015-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "T_TEST"."INST_DTM"<TO_DATE(' 2015-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          5  consistent gets
          2  physical reads
          0  redo size
        542  bytes sent via SQL*Net to client
        608  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

可以看到，INDEXING为PARTIAL的索引，执行计划完全改变，多了UNION ALL操作，并且除了索引扫描之外还有扫描局部索引之外的分区的操作，并且结果做了UNION ALL操作，但是这里的执行计划并没有真正扫描索引之外的分区，因为我的查询SQL的查询条件有分区键，直接可以定位到具体的分区，通过执行计划也可以看到，TABLE ACCESS FULL操作被FILTER过滤掉了，可能这样看不太直观，下面看一下不使用分区键，不能直接定位到具体分区的情况，也就是真正需要全表扫描的情况。

SQL> select count(*) from T_TEST where CST_ID='10';

  COUNT(*)
----------
         2

Execution Plan
----------------------------------------------------------
Plan hash value: 569975690

-----------------------------------------------------------------------------------------------------------------
| Id | Operation                                     |Name     |Rows |Bytes|Cost (%CPU)|Time    | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------
|   0| SELECT STATEMENT                              |         |    1|    9|15508   (1)|00:00:01|       |       |
|   1|  SORT AGGREGATE                               |         |    1|    9|           |        |       |       |
|   2|   VIEW                                        |VW_TE_2  |    4|     |15508   (1)|00:00:01|       |       |
|   3|    UNION-ALL                                  |         |     |     |           |        |       |       |
|   4|     PARTITION RANGE OR                        |         |    2|   34|   14   (0)|00:00:01|KEY(OR)|KEY(OR)|
|*  5|      TABLE ACCESS BY LOCAL INDEX ROWID BATCHED|T_TEST   |    2|   34|   14   (0)|00:00:01|KEY(OR)|KEY(OR)|
|*  6|       INDEX RANGE SCAN                        |IX_CST_ID|    4|     |    9   (0)|00:00:01|KEY(OR)|KEY(OR)|
|   7|     PARTITION RANGE ITERATOR                  |         |    2|   34|15494   (1)|00:00:01|     1 |     2 |
|*  8|      TABLE ACCESS FULL                        |T_TEST   |    2|   34|15494   (1)|00:00:01|     1 |     2 |
-----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - filter("T_TEST"."INST_DTM">=TO_DATE(' 2015-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') OR
              "T_TEST"."INST_DTM" IS NULL)
   6 - access("CST_ID"='10')
   8 - filter("CST_ID"='10')

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
     110313  consistent gets
      55248  physical reads
          0  redo size
        542  bytes sent via SQL*Net to client
        608  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

因为没有使用分区键查询，Oracle并不能直接定位到具体的分区，所以通过索引扫描了3和4分区，又通过全分区扫描了1和2分区，然后结果做了UNION ALL，和上面的那个直走局部索引的执行计划完全不同。

在跨分区查询时，Oracle就是通过这种有索引的分区走索引，没索引的分区全扫，然后结果集UNION ALL的操作实现的。

这个新功能对于冷数据和热数据明显的分区表来讲，非常有用，当然也要看业务的具体场景是否合适使用。

本文固定链接: https://www.dbdream.com.cn/2018/05/oracle-12c%e6%96%b0%e7%89%b9%e6%80%a7-%e5%88%86%e5%8c%ba%e8%a1%a8%e5%b1%80%e9%83%a8%e5%88%86%e5%8c%ba%e7%b4%a2%e5%bc%95/ | 信春哥，系统稳，闭眼上线不回滚！

该日志由 dbdream 于2018年05月11日发表在 Oracle, Oracle 12c, 未分类分类下，你可以发表评论，并在保留原文地址及作者的情况下引用到你的网站或博客。
原创文章转载请注明: Oracle 12C新特性-分区表局部分区索引 | 信春哥，系统稳，闭眼上线不回滚！

【上一篇】Oracle 12C新特性-插入数据时指定NULL值时使用默认值(DEFAULT ON NULL)
【下一篇】Oracle 12C新特性-分区表全局索引异步维护

Oracle 12C新特性-分区表局部分区索引

您可能还会对这些文章感兴趣！

Oracle 12C新特性-分区表局部分区索引：等您坐沙发呢！

发表评论

最新日志热评日志随机日志

最活跃的读者

标签云集

分类目录

文章归档

博客统计

用户登录