天达云 科技型企业 | 亚太互联网络信息中心(APNIC)成员 | 注册免费体验
天达云首页 > 互联网学习教程 > 关系型数据库 > 怎么理解oracle 12c分区表不完全索引

怎么理解oracle 12c分区表不完全索引
更新：HHH   时间：2023-1-7

本篇内容主要讲解“怎么理解oracle 12c分区表不完全索引”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“怎么理解oracle 12c分区表不完全索引”吧!

有的分区表根据业务特点，热点数据只是最近几个月，甚至是当月数据，这时候在其他不活跃分区上建索引就显得浪费存储空间，对有的全局索引来说还会影响性能。oracle 12c中提供了只在部分分区上建索引的功能，不完全索引的出现，完美的解决了这个问题。

建一个分区表

create table part1

(id int, code int,name varchar2(100))

indexing off

partition by range (id)

(partition p1 values less than (1000),

partition p2 values less than (2000),

partition p3 values less than (3000)   indexing on

);

oracle通过关键字indexing作为不完全索引的开关。在这个建表语句中，表级别的indexing默认是on，分区的indexing属性如果没有指定，那么会继承表级别参数。这里的这个建表语句，把表级别indexing关掉，显示指示分区P3 indexing 为on，那么其他三个分区p1,p2的indexing就是off了。这样就可以实现P3分区上建不完全索引了。

该关键字属性可以通过查看dba_tab_partitions的indexing得到

SYS@cdbtest1(MING)> COL PARTITION_NAME   FOR A30

SYS@cdbtest1(MING)> select   PARTITION_NAME,indexing from dba_tab_partitions where table_owner='MING' AND   TABLE_NAME='PART1';

 

PARTITION_NAME                 INDE

------------------------------ ----

P1                             OFF

P2                             OFF

P3                             ON

可以看到只有P3是显示指定的ON，其他分区集成了表级别的indexing属性。

插入数据

begin

for i in 1 .. 2999 loop

insert into part1 values(i,i,'mingshuo');

end loop ;

commit;

end;

/

 

创建全局索引

create index id_part1_global on part1(id)   global indexing full;

create index code_part1_global on   part1(code) global indexing partial;

SYS@cdbtest1(MING)> col INDEX_NAME for   a30

SYS@cdbtest1(MING)> select   index_name,partitioned,indexing from dba_indexes where index_name in   ('ID_PART1_GLOBAL','CODE_PART1_GLOBAL');

 

INDEX_NAME                     PAR INDEXIN

------------------------------ ---   -------

CODE_PART1_GLOBAL              NO  PARTIAL

ID_PART1_GLOBAL                NO  FULL

indexing full是默认的，虽然P1，P2分区indexing是OFF，但是如果在创建索引时指定了indexing full，那么还是会基于所有分区创建索引；

如果指定了indexing partial，那么就会按照分区的indexing属性，选择性的基于indexing为ON的分区上创建索引。

也就是说创建索引时指定的indexing参数优先级更高，会覆盖表定义中的indexing属性。

如果创建索引时不指定那么就按照默认的indexing full来生效，创建索引时指定indexing partial会按照表定义时的indexing属性。

这里，id_part1_global是完全索引，code_part1_global是不完全索引。

对此，利用执行计划印证一下。

MING@ming(MING)> explain plan for   select count(id) from part1;

 

Explained.

 

MING@ming(MING)> set line 200

MING@ming(MING)> select * from   table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Plan hash value: 2604063405

 

-----------------------------------------------------------------------------------------

| Id    | Operation             |   Name            | Rows  | Bytes | Cost (%CPU)| Time     |

-----------------------------------------------------------------------------------------

|     0 | SELECT STATEMENT      |                 |     1 |      13 |     5   (0)| 00:00:01 |

|     1 |  SORT AGGREGATE       |                 |     1 |      13 |            |          |

|     2 |   INDEX FAST FULL SCAN|   ID_PART1_GLOBAL |     1 |    13 |       5   (0)| 00:00:01 |

-----------------------------------------------------------------------------------------

 

9 rows selected.

 

id列上是正常的全局索引，所以走了索引快速扫描。

code列上是不完全索引，count(code)看一下执行计划：

先删除之前id列上的索引避免干扰

drop index ID_PART1_GLOBAL;

MING@ming(MING)> explain plan for   select count(code) from part1;

 

Explained.

 

MING@ming(MING)> select * from   table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Plan hash value: 1564279961

 

-----------------------------------------------------------------------------------------------------------------------------------

| Id    | Operation                                     |   Name              | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |

-----------------------------------------------------------------------------------------------------------------------------------

|     0 | SELECT STATEMENT                              |                   |     1 |      13 |   549   (1)| 00:00:01 |  |          |

|     1 |  SORT AGGREGATE                               |                   |     1 |      13 |            |          |    |        |

|     2 |   VIEW                                        |   VW_TE_2           |     2 |      26 |   549   (1)| 00:00:01 |  |          |

|     3 |    UNION-ALL                                  |                   |       |         |            |          |    |        |

|*    4 |     TABLE ACCESS BY GLOBAL   INDEX ROWID BATCHED| PART1               |     1 |    26 |       1   (0)| 00:00:01 |3 |      3 |

|*    5 |      INDEX RANGE SCAN                         | CODE_PART1_GLOBAL   |     1 |       |       1   (0)| 00:00:01 |  |          |

|     6 |     PARTITION RANGE   ITERATOR                  |                   |     1 |      26 |   548   (1)| 00:00:01 |1 |      2 |

|*    7 |      TABLE ACCESS FULL                        | PART1             |     1 |      26 |   548   (1)| 00:00:01 |1 |      2 |

-----------------------------------------------------------------------------------------------------------------------------------

 

Predicate Information (identified by   operation id):

---------------------------------------------------

 

     4 - filter("PART1"."ID">=2000 AND   "PART1"."ID"<3000)

     5 - access("CODE">2600)

     7 - filter("CODE">2600)

 

10 rows selected.

 

看执行计划就发现，P3分区确实采用了CODE_PART1_GLOBAL索引，其他分区采用全表扫描，两个结果集union all。

再来看分区索引

drop index CODE_PART1_GLOBAL;

create index id_part1_partial on   part1(id) local indexing partial; 

create index code_part1_partial on   part1(code) local indexing full; 

 

SYS@cdbtest1(MING)> select   index_name,partitioned,indexing from dba_indexes where index_name in   ('ID_PART1_PARTIAL','CODE_PART1_PARTIAL');

 

INDEX_NAME                     PAR INDEXIN

------------------------------ ---   -------

ID_PART1_PARTIAL               YES PARTIAL

CODE_PART1_PARTIAL             YES FULL

 

SYS@cdbtest1(MING)> col HIGH_VALUE for   a15

SYS@cdbtest1(MING)> col PARTITION_NAME   for a20

SYS@cdbtest1(MING)> col index_name for   a25

SYS@cdbtest1(MING)> set line 150

SYS@cdbtest1(MING)> select   INDEX_NAME,HIGH_VALUE,PARTITION_NAME,STATUS,LEAF_BLOCKS,NUM_ROWS from   dba_ind_partitions where  INDEX_NAME in   ('ID_PART1_PARTIAL','CODE_PART1_PARTIAL');

 

INDEX_NAME                HIGH_VALUE      PARTITION_NAME       STATUS   LEAF_BLOCKS   NUM_ROWS

------------------------- ---------------   -------------------- -------- ----------- ----------

CODE_PART1_PARTIAL        1000            P1                   USABLE             3        999

CODE_PART1_PARTIAL        2000            P2                   USABLE             3       1000

CODE_PART1_PARTIAL        3000            P3                   USABLE             3       1000

ID_PART1_PARTIAL          1000            P1                   UNUSABLE           0          0

ID_PART1_PARTIAL          2000            P2                   UNUSABLE           0          0

ID_PART1_PARTIAL          3000            P3                   USABLE             3       1000

 

6 rows selected.

 

通过status和num_rows两列可以看到，indexing full确实优先级更高，没有指定indexing的，按照表定义，id列上索引只在P3分区存在，code列索引每个分区都有。

下面也看一下不完全索引在执行计划中的表现：

MING@ming(MING)> select count(code)   from part1 where code<200;

 

 

Execution Plan

----------------------------------------------------------

Plan hash value: 186457639

 

-----------------------------------------------------------------------------------------------------------

| Id    | Operation            |   Name               | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |

-----------------------------------------------------------------------------------------------------------

|     0 | SELECT STATEMENT     |                    |     1 |      13 |     3   (0)| 00:00:01 |       |         |

|     1 |  SORT AGGREGATE      |                    |     1 |      13 |            |          |       |         |

|     2 |   PARTITION RANGE ALL|                    |     1 |      13 |     3   (0)| 00:00:01 |     1 |       3 |

|*    3 |    INDEX RANGE SCAN  | CODE_PART1_PARTIAL |     1 |      13 |     3   (0)| 00:00:01 |     1 |       3 |

-----------------------------------------------------------------------------------------------------------

 

Predicate Information (identified by   operation id):

---------------------------------------------------

 

     3 - access("CODE"<200)

 

Note

-----

     - dynamic statistics used: dynamic sampling (level=2)

 

 

MING@ming(MING)> select count(id) from   part1 where id<200;

 

 

Execution Plan

----------------------------------------------------------

Plan hash value: 1660407118

 

-------------------------------------------------------------------------------------------------

| Id    | Operation               |   Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |

-------------------------------------------------------------------------------------------------

|     0 | SELECT STATEMENT          |       |     1 |      13 |   275   (1)| 00:00:01 |       |         |

|     1 |  SORT AGGREGATE         |       |       1 |    13 |            |          |       |         |

|     2 |   PARTITION RANGE   SINGLE|       |     1 |      13 |   275   (1)| 00:00:01 |     1 |       1 |

|*    3 |    TABLE ACCESS FULL    | PART1 |     1 |      13 |   275   (1)| 00:00:01 |     1 |       1 |

-------------------------------------------------------------------------------------------------

 

Predicate Information (identified by   operation id):

---------------------------------------------------

 

     3 - filter("ID"<200)

 

Note

-----

     - dynamic statistics used: dynamic sampling (level=2)

 

 

Statistics

----------------------------------------------------------

            6  recursive calls

            4  db block gets

           92  consistent gets

            0  physical reads

            0  redo size

          544  bytes sent via SQL*Net to   client

          607  bytes received via SQL*Net   from client

            2  SQL*Net roundtrips to/from   client

            0  sorts (memory)

            0  sorts (disk)

            1  rows processed

 

MING@ming(MING)> select count(id) from   part1 where id>2900;

 

 

Execution Plan

----------------------------------------------------------

Plan hash value: 3675562320

 

------------------------------------------------------------------------------------------------------------

| Id    | Operation               |   Name             | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |

------------------------------------------------------------------------------------------------------------

|     0 | SELECT STATEMENT          |                  |       1 |    13 |     1     (0)| 00:00:01 |       |       |

|     1 |  SORT AGGREGATE         |                  |     1 |      13 |            |          |       |         |

|     2 |   PARTITION RANGE   SINGLE|                  |     1 |      13 |     1   (0)| 00:00:01 |     3 |       3 |

|*    3 |    INDEX RANGE SCAN     | ID_PART1_PARTIAL |     1 |      13 |     1   (0)| 00:00:01 |     3 |       3 |

------------------------------------------------------------------------------------------------------------

 

Predicate Information (identified by   operation id):

---------------------------------------------------

 

     3 - access("ID">2900)

 

Note

-----

     - dynamic statistics used: dynamic sampling (level=2)

 

 

Statistics

----------------------------------------------------------

           11  recursive calls

            4  db block gets

           44  consistent gets

            4  physical reads

            0  redo size

          543  bytes sent via SQL*Net to   client

          607  bytes received via SQL*Net   from client

            2  SQL*Net roundtrips to/from   client

            0  sorts (memory)

            0  sorts (disk)

            1  rows processed

 

 

MING@ming(MING)> select count(id) from   part1 where id>1900;

 

 

Execution Plan

----------------------------------------------------------

Plan hash value: 712638347

 

--------------------------------------------------------------------------------------------------------------

| Id    | Operation                 |   Name             | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |

--------------------------------------------------------------------------------------------------------------

|     0 | SELECT STATEMENT            |                  |     1 |      13 |   277   (1)| 00:00:01 |       |         |

|     1 |  SORT AGGREGATE           |                  |     1 |      13 |            |          |       |         |

|     2 |   VIEW                    | VW_TE_2          |    1422 | 18486 |   277   (1)| 00:00:01 |       |         |

|     3 |    UNION-ALL              |                  |       |         |            |          |       |         |

|     4 |     PARTITION RANGE   SINGLE|                  |     1 |      25 |     1   (0)| 00:00:01 |     3 |       3 |

|*    5 |      INDEX RANGE SCAN     | ID_PART1_PARTIAL |     1 |      25 |     1   (0)| 00:00:01 |     3 |       3 |

|     6 |     PARTITION RANGE   SINGLE|                  |  1421 | 35525 |   276     (1)| 00:00:01 |     2 |     2 |

|*    7 |      TABLE ACCESS FULL    | PART1            |    1421 | 35525 |   276   (1)| 00:00:01 |     2 |       2 |

--------------------------------------------------------------------------------------------------------------

 

Predicate Information (identified by   operation id):

---------------------------------------------------

 

     5 - access("PART1"."ID">=2000 AND   "PART1"."ID"<3000)

     7 - filter("PART1"."ID">1900)

 

id>1900的时候，会跨越两个分区，P2走分区扫描，P3走不完全索引，两个结果集union all。

改变表的indexing属性

比如这里我需要P2，P3的indexing属性都是ON，P2定义的时候是OFF，可以通过下面的语句修改：

alter table part1 modify partition P2 indexing on;

实际生产环境下，如果因为之前不了解这种不完全索引，那么建表的时候不会按照我上面实验的建表语句中，表级别indexing设置为OFF，后续如果要用这个新特性的话，就需要去修改分区的indexing，这时候就需要上面的语句，把分区级别修改为OFF。

MING@ming(MING)> COL PARTITION_NAME   FOR A30

MING@ming(MING)> select   PARTITION_NAME,indexing from dba_tab_partitions where table_owner='MING' AND   TABLE_NAME='PART1';

 

PARTITION_NAME                 INDE

------------------------------ ----

P1                             OFF

P2                             ON

P3                             ON

 

这里要注意一下索引的有效性

如果之前将分区indexing从ON修改为OFF，那么之前存在的分区上的不完全索引会失效，换句话说，已经存在的不完全索引的usable或者unusable状态是跟indexing的ON或者OFF对应的。indexing由OFF变为ON，索引从unusable变为usable，indexing由ON变为OFF，索引从usable变为unusable。

到此，相信大家对“怎么理解oracle 12c分区表不完全索引”有了更深的了解，不妨来实际操作一番吧！这里是天达云网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！
返回关系型数据库教程...
400-837-6568
7 * 24小时全天全国服务热线400电话
天达云·专业的云计算服务平台 全国400服务电话：400-837-6568
Copyright © 2011-2025 Tdyun.com. All Rights Reserved. 天达云 版权所有 粤ICP备18083267号-7