Oracle 12C 新特性之表分区部分索引(Partial Indexes)

简介:

12c之前没办法在部分或指定的分区上创建索引,12c 版本中引入了Partial Indexes(部分索引), 无论是global还是local都可以有选择性的对部分分区创建索引。分区上有索引用索引,无索引全表扫,跨多分区中indexing有开有关时有用索引和全表的子集union all。

partial index有两个限制条件:
1、 表是分区表
2、 不能是唯一索引
3、 建表的时候分区指定 INDEXING OFF or ON 或者 alter table 修改

一:indexing 探讨。

-- 创建实验表
CREATE TABLE p_andy
(ID number(10), NAME varchar2(30))
INDEXING OFF
PARTITION BY RANGE (id)
(PARTITION p1 VALUES LESS THAN (200) INDEXING OFF,
PARTITION p2 VALUES LESS THAN (400),
PARTITION p3 VALUES LESS THAN (600),
PARTITION p4 VALUES LESS THAN (800) INDEXING ON,
partition p5 values less than (maxvalue)
);
Table created.
-- 插入数据
begin
for i in 1 .. 1000 loop
insert into p_andy values(i,'andyi');
end loop ;
commit;
end;
/
PL/SQL procedure successfully completed.
-- 查看现在表的分区:
SQL> 
col table_name for a25
col partition_name for a25
col HIGH_VALUE for a10
select table_name,partition_name,PARTITION_POSITION,tablespace_name,HIGH_VALUE,indexing from user_tab_partitions where table_name='P_ANDY';
TABLE_NAME                PARTITION_NAME            PARTITION_POSITION TABLESPACE_NAME                HIGH_VALUE INDE
------------------------- ------------------------- ------------------ ------------------------------ ---------- ----
P_ANDY                    P1                                         1 USERS                          200        OFF
P_ANDY                    P2                                         2 USERS                          400        OFF
P_ANDY                    P3                                         3 USERS                          600        OFF
P_ANDY                    P4                                         4 USERS                          800        ON
P_ANDY                    P5                                         5 USERS                          MAXVALUE   OFF
说明:Create table属性indexing的默认值是ON; partition 或sub partition如果没有指定indexing是继承上级属性。

--创建global索引
create index idx_pAndy_id on p_andy(id);
Index created.
-- 查看索引状态
SQL> 
col index_name for a25
select table_name,index_name,partitioned,blevel,leaf_blocks,indexing from user_Indexes where index_name ='IDX_PANDY_ID';
TABLE_NAME                INDEX_NAME                PAR     BLEVEL LEAF_BLOCKS INDEXIN
------------------------- ------------------------- --- ---------- ----------- -------
P_ANDY                    IDX_PANDY_ID              NO           1           3 FULL

SQL> drop index IDX_PANDY_ID;
Index dropped.
SQL> create index idx_pAndy_id01 on p_andy(id) GLOBAL INDEXING FULL;
Index created.
-- 查看索引状态
SQL> 
col index_name for a25
select table_name,index_name,partitioned,blevel,leaf_blocks,indexing from user_Indexes where index_name ='IDX_PANDY_ID01';
TABLE_NAME                INDEX_NAME                PAR     BLEVEL LEAF_BLOCKS INDEXIN
------------------------- ------------------------- --- ---------- ----------- -------
P_ANDY                    IDX_PANDY_ID01            NO           1           3 FULL

SQL> drop index IDX_PANDY_ID01;
Index dropped.
SQL> create index idx_pAndy_id02 on p_andy(id) GLOBAL INDEXING PARTIAL;
Index created.
-- 查看索引状态
SQL> 
col index_name for a25
select table_name,index_name,partitioned,blevel,leaf_blocks,indexing from user_Indexes where index_name ='IDX_PANDY_ID02';
TABLE_NAME                INDEX_NAME                PAR     BLEVEL LEAF_BLOCKS INDEXIN
------------------------- ------------------------- --- ---------- ----------- -------
P_ANDY                    IDX_PANDY_ID02            NO           0           1 PARTIAL
SQL> drop index IDX_PANDY_ID02;
Index dropped.

说明:Create Index 引入了INDEXING ON|OFF|PARTIAL|FULL值, INDEXING FULL是默认值. 上面创建了global index full和index partial 可以看到部分索引的index leaf block和number of rows都明显减少。

创建LOCAL索引
SQL> create index idx_pAndy_id03 on p_andy(id) local;
Index created.
-- 查看索引状态
SQL> 
col index_name for a25
select table_name,index_name,partitioned,blevel,leaf_blocks,indexing from user_Indexes where index_name ='IDX_PANDY_ID03';
TABLE_NAME                INDEX_NAME                PAR     BLEVEL LEAF_BLOCKS INDEXIN
------------------------- ------------------------- --- ---------- ----------- -------
P_ANDY                    IDX_PANDY_ID03            YES          0           5 FULL

SQL> drop index idx_pAndy_id03;
Index dropped.
创建LOCAL索引
SQL> create index idx_pAndy_id03 on p_andy(id) local INDEXING PARTIAL;
Index created.
-- 查看索引状态
SQL> 
col index_name for a25
select table_name,index_name,partitioned,blevel,leaf_blocks,indexing from user_Indexes where index_name ='IDX_PANDY_ID03';
TABLE_NAME                INDEX_NAME                PAR     BLEVEL LEAF_BLOCKS INDEXIN
------------------------- ------------------------- --- ---------- ----------- -------
P_ANDY                    IDX_PANDY_ID03            YES          0           1 PARTIAL
说明:创建了local index full和index partial 可以看到部分索引的index leaf blocks和number of rows都明显减少.

二: 部分索引执行计划探讨

测试:索引相关的执行计划
-- 查看表索引
col column_name for a30
col index_name for a20
SELECT index_name, column_name, descend  FROM user_ind_columns  WHERE table_name = 'P_ANDY';
INDEX_NAME           COLUMN_NAME                    DESC
-------------------- ------------------------------ ----
IDX_PANDY_ID_L       ID                             ASC
IDX_PANDY_ID_G       ID                             ASC
-- 删除所有索引,为了方便下面创建部分索引。
SQL> drop index IDX_PANDY_ID_L ;
Index dropped.
SQL> drop index IDX_PANDY_ID_G;
Index dropped.
-- 查看现在表的分区:
SQL> 
col table_name for a25
col partition_name for a25
col HIGH_VALUE for a10
select table_name,partition_name,PARTITION_POSITION,tablespace_name,HIGH_VALUE,indexing from user_tab_partitions where table_name='P_ANDY';
TABLE_NAME                PARTITION_NAME            PARTITION_POSITION TABLESPACE_NAME                HIGH_VALUE INDE
------------------------- ------------------------- ------------------ ------------------------------ ---------- ----
P_ANDY                    P1                                         1 USERS                          200        OFF
P_ANDY                    P2                                         2 USERS                          400        OFF
P_ANDY                    P3                                         3 USERS                          600        ON
P_ANDY                    P4                                         4 USERS                          800        OFF
P_ANDY                    P5                                         5 USERS                          MAXVALUE   OFF

-- 创建全局索引
SQL> create index idx_pAndy_id_G on p_andy(id) GLOBAL INDEXING PARTIAL;
Index created.
-- 验证是不是部分全局索引
SQL>  select index_name,indexing from dba_indexes where index_name='IDX_PANDY_ID_G';
INDEX_NAME           INDEXIN
-------------------- -------
IDX_PANDY_ID_G       PARTIAL   > 说明是部分全局索引

SQL> 
col index_name for a25
select table_name,index_name,status,blevel,leaf_blocks from user_Indexes where index_name ='IDX_PANDY_ID_G';
TABLE_NAME                INDEX_NAME                STATUS       BLEVEL LEAF_BLOCKS
------------------------- ------------------------- -------- ---------- -----------
P_ANDY                    IDX_PANDY_ID_G            VALID             0           1
-- 改变分区 indexing 属性
SQL> alter table P_ANDY modify partition P3 indexing off;
Table altered.
SQL> alter table P_ANDY modify partition P4 indexing on;
Table altered.
SQL> select table_name,index_name,status,blevel,leaf_blocks from user_Indexes where index_name ='IDX_PANDY_ID_G';
TABLE_NAME                INDEX_NAME                STATUS       BLEVEL LEAF_BLOCKS
------------------------- ------------------------- -------- ---------- -----------
P_ANDY                    IDX_PANDY_ID_G            VALID             0           1
SQL> select table_name,partition_name,PARTITION_POSITION,tablespace_name,HIGH_VALUE,indexing from user_tab_partitions where table_name='P_ANDY';
TABLE_NAME                PARTITION_NAME  PARTITION_POSITION TABLESPACE_NAME                HIGH_VALUE INDE
------------------------- --------------- ------------------ ------------------------------ ---------- ----
P_ANDY                    P1                               1 USERS                          200        OFF
P_ANDY                    P2                               2 USERS                          400        OFF
P_ANDY                    P3                               3 USERS                          600        OFF
P_ANDY                    P4                               4 USERS                          800        ON
P_ANDY                    P5                               5 USERS                          MAXVALUE   OFF
-- 改变分区 indexing 属性
-- 创建local 索引
SQL> create index idx_pAndy_id_L on p_andy(id) local INDEXING PARTIAL;
ORA-01408: such column list already indexed
SQL> create index idx_pAndy_id_L on p_andy(id) local INDEXING PARTIAL invisible;
Index created.
说明:同一字段上不能创建两个相同种类的索引,这里把第二次创建的索引设置为 invisible 。
SQL> 
COL INDEX_NAME FOR A25
COL PARTITION_NAME FOR A15
select index_name, partition_name,STATUS from user_ind_partitions  where index_name = 'IDX_PANDY_ID_L';
INDEX_NAME                PARTITION_NAME  STATUS
------------------------- --------------- --------
IDX_PANDY_ID_L            P1              UNUSABLE
IDX_PANDY_ID_L            P2              UNUSABLE
IDX_PANDY_ID_L            P3              UNUSABLE
IDX_PANDY_ID_L            P4              USABLE
IDX_PANDY_ID_L            P5              UNUSABLE
说明:local index 标记为INDEXING OFF的分区索引状态将显示为UNUSABLE, 标记为INDEXING ON的分区索引状态显示为USABLE。


SQL> set autotrace  only on explain;
SQL> select * from p_andy where id>550;
Execution Plan
----------------------------------------------------------
Plan hash value: 4213581160

-------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                             |                |   450 |  4500 |   549   (0)| 00:00:01 |       |       |
|   1 |  VIEW                                        | VW_TE_2        |   453 | 13590 |   549   (0)| 00:00:01 |       |       |
|   2 |   UNION-ALL                                  |                |       |       |            |       |       |       |
|   3 |    TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| P_ANDY         |   200 |  2000 |     2   (0)| 00:00:01 |     4 |     4 |
|*  4 |     INDEX RANGE SCAN                         | IDX_PANDY_ID_G |    40 |       |     1   (0)| 00:00:01 |       |       |
|   5 |    PARTITION RANGE OR                        |                |   253 |  2530 |   547   (0)| 00:00:01 |KEY(OR)|KEY(OR)|
|*  6 |     TABLE ACCESS FULL                        | P_ANDY         |   253 |  2530 |   547   (0)| 00:00:01 |KEY(OR)|KEY(OR)|
--------------------------------------------------------------------------------------------------------------------------

SQL> select * from p_andy where id=550;
Execution Plan
----------------------------------------------------------
Plan hash value: 2878316774

------------------------------------------------------------------------------------------------
| Id  | Operation              | Name   | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |        |     1 |    10 |   274   (0)| 00:00:01 |       |
|   1 |  PARTITION RANGE SINGLE|        |     1 |    10 |   274   (0)| 00:00:01 |     3 |     3
|*  2 |   TABLE ACCESS FULL    | P_ANDY |     1 |    10 |   274   (0)| 00:00:01 |     3 |     3
------------------------------------------------------------------------------------------------

SQL> alter index IDX_PANDY_ID_G invisible;
Index altered.
SQL> alter index IDX_PANDY_ID_L visible;
Index altered.

SQL> select * from p_andy where id=770;
Execution Plan
----------------------------------------------------------
Plan hash value: 3981876911

--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name           | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop
--------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |                |     1 |    10 |     2   (0)| 00:00:01 |       |
|   1 |  PARTITION RANGE SINGLE                    |                |     1 |    10 |     2   (0)| 00:00:01 |     4 |     4
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| P_ANDY         |     1 |    10 |     2   (0)| 00:00:01 |     4 |     4
|*  3 |    INDEX RANGE SCAN                        | IDX_PANDY_ID_L |     1 |       |     1   (0)| 00:00:01 |     4 |     4
--------------------------------------------------------------------------------------------------------------------------
SQL> select * from p_andy where id>770;
--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                             |                |   230 |  2300 |   276   (0)| 00:00:01 |       |       |
|   1 |  VIEW                                        | VW_TE_2        |   230 |  6900 |   276   (0)| 00:00:01 |       |       |
|   2 |   UNION-ALL                                  |                |       |       |            |       |       |       |
|   3 |    PARTITION RANGE SINGLE                    |                |    29 |   290 |     2   (0)| 00:00:01 |     4 |     4 |
|   4 |     TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| P_ANDY         |    29 |   290 |     2   (0)| 00:00:01 |     4 |     4 |
|*  5 |      INDEX RANGE SCAN                        | IDX_PANDY_ID_L |    29 |       |     1   (0)| 00:00:01 |     4 |     4 |
|   6 |    PARTITION RANGE SINGLE                    |                |   201 |  2010 |   274   (0)| 00:00:01 |     5 |     5 |
|*  7 |     TABLE ACCESS FULL                        | P_ANDY         |   201 |  2010 |   274   (0)| 00:00:01 |     5 |     5 |
--------------------------------------------------------------------------------------------------------------------------
说明:分区上有索引用索引(也可能存在某种情况不走索引),无索引全表扫,跨多分区中indexing有开有关时有用索引和全表的子集union all。

文章可以转载,必须以链接形式标明出处。


本文转自 张冲andy 博客园博客,原文链接:  http://www.cnblogs.com/andy6/p/6857209.html  ,如需转载请自行联系原作者
相关文章
|
3月前
|
SQL 机器学习/深度学习 Oracle
关系型数据库Oracle关键特性
【7月更文挑战第5天】
56 3
|
26天前
|
存储 Oracle 关系型数据库
Oracle和MySQL有哪些区别?从基本特性、技术选型、字段类型、事务、语句等角度详细对比Oracle和MySQL
从基本特性、技术选型、字段类型、事务提交方式、SQL语句、分页方法等方面对比Oracle和MySQL的区别。
Oracle和MySQL有哪些区别?从基本特性、技术选型、字段类型、事务、语句等角度详细对比Oracle和MySQL
|
3月前
|
存储 Oracle 关系型数据库
Oracle 12c支持哪些数据类型?
【7月更文挑战第20天】Oracle 12c支持哪些数据类型?
59 2
|
3月前
|
SQL Oracle 关系型数据库
Oracle 12c有哪些新特性?
【7月更文挑战第20天】Oracle 12c有哪些新特性?
47 2
|
3月前
|
存储 Oracle 关系型数据库
Oracle数据库ACID特性
【7月更文挑战第6天】
70 6
|
5月前
|
存储 Oracle 关系型数据库
Oracle索引知识看这一篇就足够
Oracle索引知识看这一篇就足够
|
5月前
|
监控 Oracle 关系型数据库
Oracle 12c的Adaptive执行计划:数据的“聪明导航员”
【4月更文挑战第19天】Oracle 12c的Adaptive执行计划是数据库查询的智能优化工具,能根据实际运行情况动态调整执行策略。它像一个聪明的导航系统,不仅生成初始执行计划,还能实时监控并适应统计信息和资源变化,例如自动切换索引或调整并行度。此外,它支持自适应连接和统计信息收集,提升处理复杂查询和变化数据环境的能力。数据管理员应充分利用这一特性来优化查询性能和用户体验。
|
5月前
|
存储 NoSQL Oracle
Oracle 12c的内存列存储:数据的“闪电侠”
【4月更文挑战第19天】Oracle 12c的内存列存储以超高速度革新数据处理,结合列存储与内存技术,实现快速查询与压缩。它支持向量化查询和并行处理,提升效率,但需合理配置以平衡系统资源。作为数据管理员,应善用此功能,适应业务需求和技术发展。
|
5月前
|
存储 Oracle 关系型数据库
Oracle 12c的多重索引:数据的“多维导航仪”
【4月更文挑战第19天】Oracle 12c的多重索引提升数据查询效率,如同多维导航仪。在同一表上创建针对不同列的多个索引,加速检索过程。虽然过多索引会增加存储和维护成本,但合理选择和使用索引策略,结合位图、函数索引等高级特性,能优化查询,应对复杂场景。数据管理员应善用这些工具,根据需求进行索引管理,支持企业数据分析。
|
5月前
|
存储 Oracle 安全
Oracle 12c的不可见字段:数据的“隐形斗篷”
【4月更文挑战第19天】Oracle 12c引入了不可见字段,这是一种隐藏数据列的特性,用于增强数据安全性和实现业务逻辑。不可见字段在常规查询中不显示,但参与数据操作,适合存储敏感信息或内部元数据。创建时只需在列定义中指定“INVISIBLE”属性。虽然有其限制,如特定查询可能暴露,但正确使用能有效提升数据安全性。了解和利用这一功能对数据管理员至关重要。

推荐镜像

更多