【SQL开发实战技巧】系列（四十九）：Oracle12C常用新特性☞表分区部分索引(Partial Indexes)

2023-12-08 08:44
文章标签 sql 实战 特性 技巧 开发 系列 索引 常用 部分 分区 partial database 四十九 oracle12c indexes

本文主要是介绍【SQL开发实战技巧】系列（四十九）：Oracle12C常用新特性☞表分区部分索引(Partial Indexes)，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

系列文章目录

【SQL开发实战技巧】系列（一）:关于SQL不得不说的那些事
【SQL开发实战技巧】系列（二）：简单单表查询
【SQL开发实战技巧】系列（三）：SQL排序的那些事
【SQL开发实战技巧】系列（四）：从执行计划讨论UNION ALL与空字符串&UNION与OR的使用注意事项
【SQL开发实战技巧】系列（五）：从执行计划看IN、EXISTS 和 INNER JOIN效率，我们要分场景不要死记网上结论
【SQL开发实战技巧】系列（六）：从执行计划看NOT IN、NOT EXISTS 和 LEFT JOIN效率，记住内外关联条件不要乱放
【SQL开发实战技巧】系列（七）：从有重复数据前提下如何比较出两个表中的差异数据及对应条数聊起
【SQL开发实战技巧】系列（八）：聊聊如何插入数据时比约束更灵活的限制数据插入以及怎么一个insert语句同时插入多张表
【SQL开发实战技巧】系列（九）：一个update误把其他列数据更新成空了？Merge改写update！给你五种删除重复数据的写法！
【SQL开发实战技巧】系列（十）：从拆分字符串、替换字符串以及统计字符串出现次数说起
【SQL开发实战技巧】系列（十一）：拿几个案例讲讲translate|regexp_replace|listagg|wmsys.wm_concat|substr|regexp_substr常用函数
【SQL开发实战技巧】系列（十二）：三问(如何对字符串字母去重后按字母顺序排列字符串？如何识别哪些字符串中包含数字？如何将分隔数据转换为多值IN列表?)
【SQL开发实战技巧】系列（十三）：讨论一下常用聚集函数&通过执行计划看sum()over()对员工工资进行累加
【SQL开发实战技巧】系列（十四）：计算消费后的余额&计算银行流水累计和&计算各部门工资排名前三位的员工
【SQL开发实战技巧】系列（十五）：查找最值所在行数据信息及快速计算总和百之max/min() keep() over()、fisrt_value、last_value、ratio_to_report
【SQL开发实战技巧】系列（十六）：数据仓库中时间类型操作（初级）日、月、年、时、分、秒之差及时间间隔计算
【SQL开发实战技巧】系列（十七）：数据仓库中时间类型操作（初级）确定两个日期之间的工作天数、计算—年中周内各日期出现次数、确定当前记录和下一条记录之间相差的天数
【SQL开发实战技巧】系列（十八）：数据仓库中时间类型操作（进阶）INTERVAL、EXTRACT以及如何确定一年是否为闰年及周的计算
【SQL开发实战技巧】系列（十九）：数据仓库中时间类型操作（进阶）如何一个SQL打印当月或一年的日历？如何确定某月内第一个和最后—个周内某天的日期？
【SQL开发实战技巧】系列（二十）：数据仓库中时间类型操作（进阶）获取季度开始结束时间以及如何统计非连续性时间的数据
【SQL开发实战技巧】系列（二十一）：数据仓库中时间类型操作（进阶）识别重叠的日期范围，按指定10分钟时间间隔汇总数据
【SQL开发实战技巧】系列（二十二）：数仓报表场景☞ 从分析函数效率一定快吗聊一聊结果集分页和隔行抽样实现方式
【SQL开发实战技巧】系列（二十三）：数仓报表场景☞ 如何对数据排列组合去重以及通过如何找到包含最大值和最小值的记录这个问题再次用执行计划给你证明分析函数性能不一定高
【SQL开发实战技巧】系列（二十四）：数仓报表场景☞通过案例执行计划详解”行转列”,”列转行”是如何实现的
【SQL开发实战技巧】系列（二十五）：数仓报表场景☞结果集中的重复数据只显示一次以及计算部门薪资差异高效的写法以及如何对数据进行快速分组
【SQL开发实战技巧】系列（二十六）：数仓报表场景☞聊聊ROLLUP、UNION ALL是如何分别做分组合计的以及如何识别哪些行是做汇总的结果行
【SQL开发实战技巧】系列（二十七）：数仓报表场景☞通过对移动范围进行聚集来详解分析函数开窗原理以及如何一个SQL打印九九乘法表
【SQL开发实战技巧】系列（二十八）：数仓报表场景☞人员分布问题以及不同组(分区)同时聚集如何实现
【SQL开发实战技巧】系列（二十九）：数仓报表场景☞简单的树形(分层)查询以及如何确定根节点、分支节点和叶子节点
【SQL开发实战技巧】系列（三十）：数仓报表场景☞树形(分层)查询如何排序？以及如何在树形查询中正确的使用where条件
【SQL开发实战技巧】系列（三十一）：数仓报表场景☞分层查询如何只查询树形结构某一个分支？如何剪掉一个分支？
【SQL开发实战技巧】系列（三十二）：数仓报表场景☞对表中某个字段内的值去重
【SQL开发实战技巧】系列（三十三）：数仓报表场景☞从不固定位置提取字符串的元素以及搜索满足字母在前数字在后等条件的数据
【SQL开发实战技巧】系列（三十四）：数仓报表场景☞如何对数据分级并行转为列
【SQL开发实战技巧】系列（三十五）：数仓报表场景☞根据条件返回不同列的数据以及Left /Full Join注意事项
【SQL开发实战技巧】系列（三十六）：数仓报表场景☞整理垃圾数据：查找数据的连续性时间和重叠时间的关系，初始化开始结束时间
【SQL开发实战技巧】系列（三十七）：数仓报表场景☞从表内始终只有近两年的数据，要求用两列分别显示其中一年的数据聊行转列隐含信息的重要性
【SQL开发实战技巧】系列（三十八）：数仓报表场景☞拆分字符串进行连接
【SQL开发实战技巧】系列（三十九）：Oracle12C常用新特性☞新增分页查询
【SQL开发实战技巧】系列（四十）：Oracle12C常用新特性☞可以在同样的列（列组合）上创建多个索引以及可以对DDL操作进行日志记录
【SQL开发实战技巧】系列（四十一）：Oracle12C常用新特性☞APPROX_COUNT_DISTINCT以及TEMP UNDO(临时undo记录可以存储在一个临时表中)
【SQL开发实战技巧】系列（四十二）：Oracle12C常用新特性☞With FUNCTION新特性
【SQL开发实战技巧】系列（四十三）：Oracle12C常用新特性☞转换函数的增强和不可见字段
【SQL开发实战技巧】系列（四十四）：Oracle12C常用新特性☞你知道吗Oralcle新增了类似Mysql的自增列（Identity Columns）功能
【SQL开发实战技巧】系列（四十五）：Oracle12C常用新特性☞varchar2/nvarchar2类型最大长度由4000字节扩展到32767字节
【SQL开发实战技巧】系列（四十六）：Oracle12C常用新特性☞会话级序列及数据泵(DataPump增强)支持像表一样导出视图
【SQL开发实战技巧】系列（四十七）：Oracle12C常用新特性☞非分区表在线转分区表online clause（不停业务+索引有效）
【SQL开发实战技巧】系列（四十八）：Oracle12C常用新特性☞多分区操作和管理
【SQL开发实战技巧】系列（四十九）：Oracle12C常用新特性☞表分区部分索引(Partial Indexes)

文章目录

  • 系列文章目录
  • 前言
  • 【SQL开发实战技巧】系列（四十七）：Oracle12C常用新特性☞表分区部分索引(Partial Indexes)
  • 案例一：比较partial index与global/local index
  • 案例二：执行计划

前言

本篇文章主要给讲解的Oracle12C+的新特性是：Partial Indexes(部分索引), 无论是global还是local都可以有选择性的对部分分区创建索引，并通过比较partial index与global/local index两个案例来看对查询效率的提升！

【SQL开发实战技巧】这一系列博主当作复习旧知识来进行写作，毕竟SQL开发在数据分析场景非常重要且基础，面试也会经常问SQL开发和调优经验，相信当我写完这一系列文章，也能再有所收获，未来面对SQL面试也能游刃有余~。

【SQL开发实战技巧】系列（四十七）：Oracle12C常用新特性☞表分区部分索引(Partial Indexes)

12c之前没办法在部分或指定的分区上创建索引,12c 版本中引入了Partial Indexes(部分索引), 无论是global还是local都可以有选择性的对部分分区创建索引。分区上有索引用索引，无索引全表扫，跨多分区中indexing有开有关时有用索引和全表的子集union all。
partial index有两个限制条件:
1、 表是分区表
2、 不能是唯一索引
3、 建表的时候分区指定 INDEXING OFF or ON 或者 alter table 修改

案例一：比较partial index与global/local index

创建测试用表：

CREATE TABLE part_ind
(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)
);

插入数据：

begin
for i in 1 .. 1000 loop
insert into part_ind values(i,'part_ind');
end loop ;
commit;
end;
/

查看现在表分区：

select table_name,partition_name,
PARTITION_POSITION,
tablespace_name,
HIGH_VALUE,
indexing 
from user_tab_partitions 
where table_name='PART_IND';

在这里插入图片描述

Create table属性indexing的默认值是ON;
partition 或sub partition如果没有指定indexing是继承上级属性。
创建global索引

create index idx_p_id on PART_IND(id);

查看索引状态

select table_name,index_name,partitioned,blevel,leaf_blocks,indexing from user_Indexes where index_name ='IDX_P_ID';

在这里插入图片描述

drop index IDX_P_ID;create index IDX_P_ID on part_ind(id) GLOBAL INDEXING FULL;
select 
table_name, 
index_name, 
partitioned, 
blevel, 
leaf_blocks,indexingfrom user_Indexeswhere index_name = 'IDX_P_ID';

在这里插入图片描述

drop index IDX_P_ID;
create index IDX_P_ID on part_ind(id) GLOBAL INDEXING PARTIAL;
select table_name,index_name,partitioned,blevel, 
leaf_blocks, 
indexingfrom user_Indexeswhere index_name = 'IDX_P_ID';

在这里插入图片描述

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

drop index idx_p_id;

创建LOCAL索引

create index idx_p_id_loc on part_ind(id) local;

查看索引状态

select table_name,index_name,partitioned,blevel,leaf_blocks,indexing from user_Indexes where index_name ='IDX_P_ID_LOC';

在这里插入图片描述

drop index idx_p_id_loc;

创建LOCAL索引

create index idx_p_id_loc on part_ind(id) local INDEXING PARTIAL;

查看索引状态

select table_name, index_name, partitioned, blevel, leaf_blocks, indexingfrom user_Indexeswhere index_name = 'IDX_P_ID_LOC';

在这里插入图片描述

创建了local index full和index partial 可以看到部分索引的index leaf blocks和number of rows都明显减少.

案例二：执行计划

查看表索引
SELECT index_name, column_name, descend
FROM user_ind_columns
WHERE table_name = ‘PART_IND’;
在这里插入图片描述

删除所有索引，为了方便下面创建部分索引。
drop index IDX_P_ID_LOC
查看现在表的分区：
select table_name,
partition_name,
PARTITION_POSITION,
tablespace_name,
HIGH_VALUE,
indexing
from user_tab_partitions
where table_name = ‘PART_IND’;
在这里插入图片描述

创建全局索引：
create index idx_p_id_G on PART_IND(id) GLOBAL INDEXING PARTIAL;
验证是不是部分全局索引：
select index_name,indexing from dba_indexes where index_name=‘IDX_P_ID_G’;
在这里插入图片描述
–是
select table_name, index_name, status, blevel, leaf_blocks
from user_Indexes
where index_name = ‘IDX_P_ID_G’;
在这里插入图片描述

改变分区 indexing 属性：
alter table PART_IND modify partition P3 indexing off;
alter table PART_IND modify partition P4 indexing on;
select table_name, index_name, status, blevel, leaf_blocks
from user_Indexes
where index_name = ‘IDX_P_ID_G’;
在这里插入图片描述

select table_name,
partition_name,
PARTITION_POSITION,
tablespace_name,
HIGH_VALUE,
indexing
from user_tab_partitions
where table_name = ‘PART_IND’;
在这里插入图片描述

改变分区 indexing 属性
创建local 索引：
create index idx_p_id_L on PART_IND(id) local INDEXING PARTIAL;
ORA-01408: such column list already indexed
create index idx_p_id_L on PART_IND(id) local INDEXING PARTIAL invisible;
同一字段上不能创建两个相同种类的索引，这里把第二次创建的索引设置为 invisible 。
select index_name, partition_name, STATUS
from user_ind_partitions
where index_name = ‘IDX_P_ID_L’;
在这里插入图片描述

local index 标记为INDEXING OFF的分区索引状态将显示为UNUSABLE， 标记为INDEXING ON的分区索引状态显示为USABLE。
看下sql执行时执行计划：
select * from PART_IND where id>550;
在这里插入图片描述

select * from PART_IND where id=550
在这里插入图片描述

alter index IDX_P_ID_G invisible;
alter index IDX_P_ID_L visible;
select * from PART_IND where id=770;
在这里插入图片描述

select * from PART_IND where id>770;
在这里插入图片描述

分区上有索引用索引（也可能存在某种情况不走索引），无索引全表扫，跨多分区中indexing有开有关时有用索引和全表的子集union all。

这篇关于【SQL开发实战技巧】系列（四十九）：Oracle12C常用新特性☞表分区部分索引(Partial Indexes)的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/469230。

相关文章

基于C#实现MQTT通信实战

基于C#实现MQTT通信实战

《基于C#实现MQTT通信实战》MQTT消息队列遥测传输,在物联网领域应用的很广泛,它是基于Publish/Subscribe模式,具有简单易用,支持QoS,传输效率高的特点,下面我们就来看看C#实现... 目录1、连接主机2、订阅消息3、发布消息MQTT(Message Queueing Telemetr
阅读更多...
一文详解如何查看本地MySQL的安装路径

一文详解如何查看本地MySQL的安装路径

《一文详解如何查看本地MySQL的安装路径》本地安装MySQL对于初学者或者开发人员来说是一项基础技能,但在安装过程中可能会遇到各种问题,：本文主要介绍如何查看本地MySQL安装路径的相关资料,需... 目录1. 如何查看本地mysql的安装路径1.1. 方法1：通过查询本地服务1.2. 方法2：通过MyS
阅读更多...
Mysql数据库中数据的操作CRUD详解

Mysql数据库中数据的操作CRUD详解

《Mysql数据库中数据的操作CRUD详解》：本文主要介绍Mysql数据库中数据的操作（CRUD）,详细描述对Mysql数据库中数据的操作（CRUD）,包括插入、修改、删除数据,还有查询数据,包括... 目录一、插入数据（insert）1.插入数据的语法2.注意事项二、修改数据（update）1.语法2.有
阅读更多...
Nginx使用Keepalived部署web集群（高可用高性能负载均衡）实战案例

Nginx使用Keepalived部署web集群（高可用高性能负载均衡）实战案例

《Nginx使用Keepalived部署web集群（高可用高性能负载均衡）实战案例》本文介绍Nginx+Keepalived实现Web集群高可用负载均衡的部署与测试,涵盖架构设计、环境配置、健康检查、... 目录前言一、架构设计二、环境准备三、案例部署配置 前端 Keepalived配置 前端 Nginx
阅读更多...
SQL Server中的PIVOT与UNPIVOT用法具体示例详解

SQL Server中的PIVOT与UNPIVOT用法具体示例详解

《SQLServer中的PIVOT与UNPIVOT用法具体示例详解》这篇文章主要给大家介绍了关于SQLServer中的PIVOT与UNPIVOT用法的具体示例,SQLServer中PIVOT和U... 目录引言一、PIVOT：将行转换为列核心作用语法结构实战示例二、UNPIVOT：将列编程转换为行核心作用语
阅读更多...
Python日期和时间完全指南与实战

Python日期和时间完全指南与实战

《Python日期和时间完全指南与实战》在软件开发领域,‌日期时间处理‌是贯穿系统设计全生命周期的重要基础能力,本文将深入解析Python日期时间的‌七大核心模块‌,通过‌企业级代码案例‌揭示最佳实践... 目录一、背景与核心价值二、核心模块详解与实战2.1 datetime模块四剑客2.2 时区处理黄金法
阅读更多...
SQL 外键Foreign Key全解析

SQL 外键Foreign Key全解析

《SQL外键ForeignKey全解析》外键是数据库表中的一列（或一组列）,用于​​建立两个表之间的关联关系​​,外键的值必须匹配另一个表的主键（PrimaryKey）或唯一约束（UniqueCo... 目录1. 什么是外键？​​ ​​​​2. 外键的语法​​​​3. 外键的约束行为​​​​4. 多列外键​
阅读更多...
C#特性(Attributes)和反射(Reflection)详解

C#特性(Attributes)和反射(Reflection)详解

《C#特性(Attributes)和反射(Reflection)详解》：本文主要介绍C#特性(Attributes)和反射(Reflection),具有很好的参考价值,希望对大家有所帮助,如有错误... 目录特性特性的定义概念目的反射定义概念目的反射的主要功能包括使用反射的基本步骤特性和反射的关系总结特性
阅读更多...
PyTorch高级特性与性能优化方式

PyTorch高级特性与性能优化方式

《PyTorch高级特性与性能优化方式》：本文主要介绍PyTorch高级特性与性能优化方式,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录一、自动化机制1.自动微分机制2.动态计算图二、性能优化1.内存管理2.GPU加速3.多GPU训练三、分布式训练1.分布式数据
阅读更多...
Spring Boot 常用注解整理(最全收藏版)

Spring Boot 常用注解整理(最全收藏版)

《SpringBoot常用注解整理(最全收藏版)》本文系统整理了常用的Spring/SpringBoot注解,按照功能分类进行介绍,每个注解都会涵盖其含义、提供来源、应用场景以及代码示例,帮助开发... 目录Spring & Spring Boot 常用注解整理一、Spring Boot 核心注解二、Spr
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2