【SQL开发实战技巧】系列(四十九):Oracle12C常用新特性☞表分区部分索引(Partial 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, descendFROM user_ind_columnsWHERE table_name = 'PART_IND';

在这里插入图片描述

删除所有索引,为了方便下面创建部分索引。

drop index IDX_P_ID_LOC

查看现在表的分区:

select table_name,partition_name,PARTITION_POSITION,tablespace_name,HIGH_VALUE,indexingfrom user_tab_partitionswhere 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_blocksfrom user_Indexeswhere 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_blocksfrom user_Indexeswhere index_name = 'IDX_P_ID_G';

在这里插入图片描述

select table_name,partition_name,PARTITION_POSITION,tablespace_name,HIGH_VALUE,indexingfrom user_tab_partitionswhere 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, STATUSfrom user_ind_partitionswhere 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/493926

相关文章

SQL BETWEEN 的常见用法小结

《SQLBETWEEN的常见用法小结》BETWEEN操作符是SQL中非常有用的工具,它允许你快速选取某个范围内的值,本文给大家介绍SQLBETWEEN的常见用法,感兴趣的朋友一起看看吧... 在SQL中,BETWEEN是一个操作符,用于选取介于两个值之间的数据。它包含这两个边界值。BETWEEN操作符常用

MySQL索引的优化之LIKE模糊查询功能实现

《MySQL索引的优化之LIKE模糊查询功能实现》:本文主要介绍MySQL索引的优化之LIKE模糊查询功能实现,本文通过示例代码给大家介绍的非常详细,感兴趣的朋友一起看看吧... 目录一、前缀匹配优化二、后缀匹配优化三、中间匹配优化四、覆盖索引优化五、减少查询范围六、避免通配符开头七、使用外部搜索引擎八、分

MySql match against工具详细用法

《MySqlmatchagainst工具详细用法》在MySQL中,MATCH……AGAINST是全文索引(Full-Textindex)的查询语法,它允许你对文本进行高效的全文搜素,支持自然语言搜... 目录一、全文索引的基本概念二、创建全文索引三、自然语言搜索四、布尔搜索五、相关性排序六、全文索引的限制七

Java字符串操作技巧之语法、示例与应用场景分析

《Java字符串操作技巧之语法、示例与应用场景分析》在Java算法题和日常开发中,字符串处理是必备的核心技能,本文全面梳理Java中字符串的常用操作语法,结合代码示例、应用场景和避坑指南,可快速掌握字... 目录引言1. 基础操作1.1 创建字符串1.2 获取长度1.3 访问字符2. 字符串处理2.1 子字

Java Optional的使用技巧与最佳实践

《JavaOptional的使用技巧与最佳实践》在Java中,Optional是用于优雅处理null的容器类,其核心目标是显式提醒开发者处理空值场景,避免NullPointerExce... 目录一、Optional 的核心用途二、使用技巧与最佳实践三、常见误区与反模式四、替代方案与扩展五、总结在 Java

一文详解如何在Python中从字符串中提取部分内容

《一文详解如何在Python中从字符串中提取部分内容》:本文主要介绍如何在Python中从字符串中提取部分内容的相关资料,包括使用正则表达式、Pyparsing库、AST(抽象语法树)、字符串操作... 目录前言解决方案方法一:使用正则表达式方法二:使用 Pyparsing方法三:使用 AST方法四:使用字

数据库面试必备之MySQL中的乐观锁与悲观锁

《数据库面试必备之MySQL中的乐观锁与悲观锁》:本文主要介绍数据库面试必备之MySQL中乐观锁与悲观锁的相关资料,乐观锁适用于读多写少的场景,通过版本号检查避免冲突,而悲观锁适用于写多读少且对数... 目录一、引言二、乐观锁(一)原理(二)应用场景(三)示例代码三、悲观锁(一)原理(二)应用场景(三)示例

Python列表去重的4种核心方法与实战指南详解

《Python列表去重的4种核心方法与实战指南详解》在Python开发中,处理列表数据时经常需要去除重复元素,本文将详细介绍4种最实用的列表去重方法,有需要的小伙伴可以根据自己的需要进行选择... 目录方法1:集合(set)去重法(最快速)方法2:顺序遍历法(保持顺序)方法3:副本删除法(原地修改)方法4:

在Spring Boot中浅尝内存泄漏的实战记录

《在SpringBoot中浅尝内存泄漏的实战记录》本文给大家分享在SpringBoot中浅尝内存泄漏的实战记录,结合实例代码给大家介绍的非常详细,感兴趣的朋友一起看看吧... 目录使用静态集合持有对象引用,阻止GC回收关键点:可执行代码:验证:1,运行程序(启动时添加JVM参数限制堆大小):2,访问 htt

SQL表间关联查询实例详解

《SQL表间关联查询实例详解》本文主要讲解SQL语句中常用的表间关联查询方式,包括:左连接(leftjoin)、右连接(rightjoin)、全连接(fulljoin)、内连接(innerjoin)、... 目录简介样例准备左外连接右外连接全外连接内连接交叉连接自然连接简介本文主要讲解SQL语句中常用的表