SQL优化中索引列使用函数之灵异事件
2023-09-11 14:20:31 时间
在SQL优化内容中有一种说法说的是避免在索引列上使用函数、运算等操作，否则Oracle优化器将不使用索引而使用全表扫描，但是也有一些例外的情况，今天我们就来看看该灵异事件。
一般而言，以下情况都会使Oracle的优化器走全表扫描，举例:
1. substr(hbs_bh,1,4)=’5400’，优化处理：hbs_bh like ‘5400%’
2. trunc(sk_rq)=trunc(sysdate)， 优化处理：sk_rq =trunc(sysdate) and sk_rq trunc(sysdate+1)
3. 进行了显式或隐式的运算的字段不能进行索引，如：
ss_df+20 50，优化处理：ss_df 30
X || hbs_bh ’X5400021452’，优化处理：hbs_bh 5400021542
sk_rq+5=sysdate，优化处理：sk_rq=sysdate-5
4. 条件内包括了多个本表的字段运算时不能进行索引，如：ys_df cx_df，无法进行优化
qc_bh || kh_bh=5400250000，优化处理：qc_bh=5400 and kh_bh=250000
5. 避免出现隐式类型转化
hbs_bh=5401002554，优化处理：hbs_bh=5401002554，注：此条件对hbs_bh 进行隐式的to_number转换，因为hbs_bh字段是字符型。
有一些其它的例外情况，如果select 后边只有索引列且where查询中的索引列含有非空约束的时候，以上规则不适用，如下示例：
先给出所有脚本及结论：
drop table t purge;
Create Table t nologging As select * from dba_objects d ;
create index ind_objectname on t(object_name);
select t.object_name from t where t.object_name =T; --走索引
select t.object_name from t where UPPER(t.object_name) =T; --不走索引
select t.object_name from t where UPPER(t.object_name) =T and t.object_name IS NOT NULL ; --走索引 (INDEX FAST FULL SCAN)
select t.object_name from t where UPPER(t.object_name) ||AAA =T||AAA and t.object_name IS NOT NULL ; --走索引 (INDEX FAST FULL SCAN)
select t.object_name,t.owner from t where UPPER(t.object_name) ||AAA =T||AAA and t.object_name IS NOT NULL ; --不走索引
测试代码：
Name Null? Type ----------------------------------------- -------- ---------------------------- OWNER VARCHAR2(30) OBJECT_NAME VARCHAR2(128) SUBOBJECT_NAME VARCHAR2(30) OBJECT_ID NUMBER DATA_OBJECT_ID NUMBER OBJECT_TYPE VARCHAR2(19) CREATED DATE LAST_DDL_TIME DATE TIMESTAMP VARCHAR2(19) STATUS VARCHAR2(7) TEMPORARY VARCHAR2(1) GENERATED VARCHAR2(1) SECONDARY VARCHAR2(1) NAMESPACE NUMBER EDITION_NAME VARCHAR2(30) SQL set autotrace traceonly; SQL select t.object_name from t where t.object_name =T; ---------------------------------------------------------- Plan hash value: 4280870634 ----------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 66 | 3 (0)| 00:00:01 | |* 1 | INDEX RANGE SCAN| IND_OBJECTNAME | 1 | 66 | 3 (0)| 00:00:01 | ----------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("T"."OBJECT_NAME"=T) ----- - dynamic sampling used for this statement (level=2) - SQL plan baseline "SQL_PLAN_503ygb00mbj6k165e82cd" used for this statement ---------------------------------------------------------- 34 recursive calls 43 db block gets 127 consistent gets 398 physical reads 15476 redo size 349 bytes sent via SQL*Net to client 359 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL select t.object_name from t where UPPER(t.object_name) =T; ---------------------------------------------------------- Plan hash value: 1601196873 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 12 | 792 | 305 (1)| 00:00:04 | |* 1 | TABLE ACCESS FULL| T | 12 | 792 | 305 (1)| 00:00:04 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(UPPER("T"."OBJECT_NAME")=T) ----- - dynamic sampling used for this statement (level=2) - SQL plan baseline "SQL_PLAN_9p76pys5gdb2b94ecae5c" used for this statement ---------------------------------------------------------- 29 recursive calls 43 db block gets 1209 consistent gets 1092 physical reads 15484 redo size 349 bytes sent via SQL*Net to client 359 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL select t.object_name from t where UPPER(t.object_name) =T and t.object_name IS NOT NULL ; ---------------------------------------------------------- Plan hash value: 3379870158 --------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 51 | 3366 | 110 (1)| 00:00:02 | |* 1 | INDEX FAST FULL SCAN| IND_OBJECTNAME | 51 | 3366 | 110 (1)| 00:00:02 | --------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("T"."OBJECT_NAME" IS NOT NULL AND UPPER("T"."OBJECT_NAME")=T) ----- - dynamic sampling used for this statement (level=2) - SQL plan baseline "SQL_PLAN_czkarb71kthws18b0c28f" used for this statement ---------------------------------------------------------- 29 recursive calls 43 db block gets 505 consistent gets 384 physical reads 15612 redo size 349 bytes sent via SQL*Net to client 359 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL select t.object_name,t.owner from t where UPPER(t.object_name) ||AAA =T||AAA and t.object_name IS NOT NULL ; ---------------------------------------------------------- Plan hash value: 1601196873 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 51 | 4233 | 304 (1)| 00:00:04 | |* 1 | TABLE ACCESS FULL| T | 51 | 4233 | 304 (1)| 00:00:04 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("T"."OBJECT_NAME" IS NOT NULL AND UPPER("T"."OBJECT_NAME")||AAA=TAAA) ----- - dynamic sampling used for this statement (level=2) - SQL plan baseline "SQL_PLAN_au9a1c4hwdtb894ecae5c" used for this statement ---------------------------------------------------------- 30 recursive calls 44 db block gets 1210 consistent gets 1091 physical reads 15748 redo size 408 bytes sent via SQL*Net to client 359 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL select t.object_name from t where UPPER(t.object_name) ||AAA =T||AAA and t.object_name IS NOT NULL ; ---------------------------------------------------------- Plan hash value: 3379870158 --------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 51 | 3366 | 110 (1)| 00:00:02 | |* 1 | INDEX FAST FULL SCAN| IND_OBJECTNAME | 51 | 3366 | 110 (1)| 00:00:02 | --------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("T"."OBJECT_NAME" IS NOT NULL AND UPPER("T"."OBJECT_NAME")||AAA=TAAA) ----- - dynamic sampling used for this statement (level=2) - SQL plan baseline "SQL_PLAN_1gu36rnh3s2a318b0c28f" used for this statement ---------------------------------------------------------- 28 recursive calls 44 db block gets 505 consistent gets 6 physical reads 15544 redo size 349 bytes sent via SQL*Net to client 359 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
其实很好理解的，索引可以看成是小表，一般而言索引总是比表本身要小得多，如果select 后需要检索的项目在索引中就可以检索的到那么Oracle优化器为啥还去大表中寻找数据呢？ 最新内容请见作者的GitHub页：http://qaseven.github.io/
【SQL开发实战技巧】系列（九）：一个update误把其他列数据更新成空了？Merge改写update！给你五种删除重复数据的写法！ 本篇文章讲解的主要内容是：***你有没有经历过一个update把其他列数据清空了、使用merge更新合并记录、删除违反参照完整性的记录、给你五种删除重复数据的写法*** 【SQL开发实战技巧】这一系列博主当作复习旧知识来进行写作，毕竟SQL开发在数据分析场景非常重要且基础，面试也会经常问SQL开发和调优经验，相信当我写完这一系列文章，也能再有所收获，未来面对SQL面试也能游刃有余~。
【Sql Server】进阶之行数据转为列显示 在开发系统维护阶段，经常需要进行各种数据统计，各种报表之类的。 这个时候，行数据转数据显示就发挥作用了。 场景：行数据的某列值想作为字段列显示
【Sql Server】进阶之行数据转为列显示 在开发系统维护阶段，经常需要进行各种数据统计，各种报表之类的。 这个时候，行数据转数据显示就发挥作用了。 场景：行数据的某列值想作为字段列显示
Flink 1.10.0 SQL DDL中如何定义watermark和计算列 随着Flink1.10.0版本的发布,在SQL上一个重大的优化是支持了watermark语义的计算,在之前的Flink1.9.x版本中是不支持的,当时只能用SQL DDL进行processing time的计算,但是现在可以进行eventtime语义的计算了,那在Flink1.10.0版本中也推出了很多新的特性,这里就不在多介绍了,本篇文章主要是接上一篇文章,FlinkSQL使用DDL语句创建kafka源表,主要来介绍一下Flink1.10.0中怎么定义watermark.
1. 简介 在数据库SQL处理中，常常有行转列（Pivot）和列转行（Unpivot）的数据处理需求。本文以示例说明在Data Lake Analytics（https://www.aliyun.com/product/datalakeanalytics）中，如何使用SQL的一些技巧，达到行转列（Pivot）和列转行（Unpivot）的目的。
前面几节中给出的规则将导致为SQL查询中的所有表达式分配非unknown数据类型， 除非未指定类型文字显示为SELECT命令的简单输出列。例如，在 SELECT Hello World 没有什么可以确定字符串文字应被视为什么类型。
PostgreSQL 10.1 手册_部分 II. SQL 语言_第 5 章 数据定义_5.4. 系统列 5.4. 系统列 每一个表都拥有一些由系统隐式定义的系统列。因此，这些列的名字不能像用户定义的列一样使用（注意这种限制与名称是否为关键词没有关系，即便用引号限定一个名称也无法绕过这种限制）。 事实上用户不需要关心这些列，只需要知道它们存在即可。
第十二届 BigData NoSQL Meetup — 基于hbase的New sql落地实践 立即下载
Name Null? Type ----------------------------------------- -------- ---------------------------- OWNER VARCHAR2(30) OBJECT_NAME VARCHAR2(128) SUBOBJECT_NAME VARCHAR2(30) OBJECT_ID NUMBER DATA_OBJECT_ID NUMBER OBJECT_TYPE VARCHAR2(19) CREATED DATE LAST_DDL_TIME DATE TIMESTAMP VARCHAR2(19) STATUS VARCHAR2(7) TEMPORARY VARCHAR2(1) GENERATED VARCHAR2(1) SECONDARY VARCHAR2(1) NAMESPACE NUMBER EDITION_NAME VARCHAR2(30) SQL set autotrace traceonly; SQL select t.object_name from t where t.object_name =T; ---------------------------------------------------------- Plan hash value: 4280870634 ----------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 66 | 3 (0)| 00:00:01 | |* 1 | INDEX RANGE SCAN| IND_OBJECTNAME | 1 | 66 | 3 (0)| 00:00:01 | ----------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("T"."OBJECT_NAME"=T) ----- - dynamic sampling used for this statement (level=2) - SQL plan baseline "SQL_PLAN_503ygb00mbj6k165e82cd" used for this statement ---------------------------------------------------------- 34 recursive calls 43 db block gets 127 consistent gets 398 physical reads 15476 redo size 349 bytes sent via SQL*Net to client 359 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL select t.object_name from t where UPPER(t.object_name) =T; ---------------------------------------------------------- Plan hash value: 1601196873 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 12 | 792 | 305 (1)| 00:00:04 | |* 1 | TABLE ACCESS FULL| T | 12 | 792 | 305 (1)| 00:00:04 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(UPPER("T"."OBJECT_NAME")=T) ----- - dynamic sampling used for this statement (level=2) - SQL plan baseline "SQL_PLAN_9p76pys5gdb2b94ecae5c" used for this statement ---------------------------------------------------------- 29 recursive calls 43 db block gets 1209 consistent gets 1092 physical reads 15484 redo size 349 bytes sent via SQL*Net to client 359 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL select t.object_name from t where UPPER(t.object_name) =T and t.object_name IS NOT NULL ; ---------------------------------------------------------- Plan hash value: 3379870158 --------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 51 | 3366 | 110 (1)| 00:00:02 | |* 1 | INDEX FAST FULL SCAN| IND_OBJECTNAME | 51 | 3366 | 110 (1)| 00:00:02 | --------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("T"."OBJECT_NAME" IS NOT NULL AND UPPER("T"."OBJECT_NAME")=T) ----- - dynamic sampling used for this statement (level=2) - SQL plan baseline "SQL_PLAN_czkarb71kthws18b0c28f" used for this statement ---------------------------------------------------------- 29 recursive calls 43 db block gets 505 consistent gets 384 physical reads 15612 redo size 349 bytes sent via SQL*Net to client 359 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL select t.object_name,t.owner from t where UPPER(t.object_name) ||AAA =T||AAA and t.object_name IS NOT NULL ; ---------------------------------------------------------- Plan hash value: 1601196873 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 51 | 4233 | 304 (1)| 00:00:04 | |* 1 | TABLE ACCESS FULL| T | 51 | 4233 | 304 (1)| 00:00:04 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("T"."OBJECT_NAME" IS NOT NULL AND UPPER("T"."OBJECT_NAME")||AAA=TAAA) ----- - dynamic sampling used for this statement (level=2) - SQL plan baseline "SQL_PLAN_au9a1c4hwdtb894ecae5c" used for this statement ---------------------------------------------------------- 30 recursive calls 44 db block gets 1210 consistent gets 1091 physical reads 15748 redo size 408 bytes sent via SQL*Net to client 359 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL select t.object_name from t where UPPER(t.object_name) ||AAA =T||AAA and t.object_name IS NOT NULL ; ---------------------------------------------------------- Plan hash value: 3379870158 --------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 51 | 3366 | 110 (1)| 00:00:02 | |* 1 | INDEX FAST FULL SCAN| IND_OBJECTNAME | 51 | 3366 | 110 (1)| 00:00:02 | --------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("T"."OBJECT_NAME" IS NOT NULL AND UPPER("T"."OBJECT_NAME")||AAA=TAAA) ----- - dynamic sampling used for this statement (level=2) - SQL plan baseline "SQL_PLAN_1gu36rnh3s2a318b0c28f" used for this statement ---------------------------------------------------------- 28 recursive calls 44 db block gets 505 consistent gets 6 physical reads 15544 redo size 349 bytes sent via SQL*Net to client 359 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
其实很好理解的，索引可以看成是小表，一般而言索引总是比表本身要小得多，如果select 后需要检索的项目在索引中就可以检索的到那么Oracle优化器为啥还去大表中寻找数据呢？ 最新内容请见作者的GitHub页：http://qaseven.github.io/
【SQL开发实战技巧】系列（九）：一个update误把其他列数据更新成空了？Merge改写update！给你五种删除重复数据的写法！ 本篇文章讲解的主要内容是：***你有没有经历过一个update把其他列数据清空了、使用merge更新合并记录、删除违反参照完整性的记录、给你五种删除重复数据的写法*** 【SQL开发实战技巧】这一系列博主当作复习旧知识来进行写作，毕竟SQL开发在数据分析场景非常重要且基础，面试也会经常问SQL开发和调优经验，相信当我写完这一系列文章，也能再有所收获，未来面对SQL面试也能游刃有余~。
【Sql Server】进阶之行数据转为列显示 在开发系统维护阶段，经常需要进行各种数据统计，各种报表之类的。 这个时候，行数据转数据显示就发挥作用了。 场景：行数据的某列值想作为字段列显示
【Sql Server】进阶之行数据转为列显示 在开发系统维护阶段，经常需要进行各种数据统计，各种报表之类的。 这个时候，行数据转数据显示就发挥作用了。 场景：行数据的某列值想作为字段列显示
Flink 1.10.0 SQL DDL中如何定义watermark和计算列 随着Flink1.10.0版本的发布,在SQL上一个重大的优化是支持了watermark语义的计算,在之前的Flink1.9.x版本中是不支持的,当时只能用SQL DDL进行processing time的计算,但是现在可以进行eventtime语义的计算了,那在Flink1.10.0版本中也推出了很多新的特性,这里就不在多介绍了,本篇文章主要是接上一篇文章,FlinkSQL使用DDL语句创建kafka源表,主要来介绍一下Flink1.10.0中怎么定义watermark.
1. 简介 在数据库SQL处理中，常常有行转列（Pivot）和列转行（Unpivot）的数据处理需求。本文以示例说明在Data Lake Analytics（https://www.aliyun.com/product/datalakeanalytics）中，如何使用SQL的一些技巧，达到行转列（Pivot）和列转行（Unpivot）的目的。
前面几节中给出的规则将导致为SQL查询中的所有表达式分配非unknown数据类型， 除非未指定类型文字显示为SELECT命令的简单输出列。例如，在 SELECT Hello World 没有什么可以确定字符串文字应被视为什么类型。
PostgreSQL 10.1 手册_部分 II. SQL 语言_第 5 章 数据定义_5.4. 系统列 5.4. 系统列 每一个表都拥有一些由系统隐式定义的系统列。因此，这些列的名字不能像用户定义的列一样使用（注意这种限制与名称是否为关键词没有关系，即便用引号限定一个名称也无法绕过这种限制）。 事实上用户不需要关心这些列，只需要知道它们存在即可。
第十二届 BigData NoSQL Meetup — 基于hbase的New sql落地实践 立即下载
相关文章
- 【索引】分区表索引重建过程的10704事件跟踪
- jQuery事件委托
- jquery 事件，注册 与重复事件处理
- 浏览器事件是否冒泡
- 【STM32H7】第17章 ThreadX事件标志组
- C#中的委托、事件及事件的订阅
- 小程序页面传参之事件传参
- DevExpress GridView 列标题点击事件
- Uniapp（H5）判断软键盘是否唤起、关闭事件
- iOS（swift）给view添加点击事件（手势）UIGestureRecognizer
- javascript 获取焦点和失去焦点事件
- Android输入事件InputReader和InputDispatcher分析
- e644. 处理Action事件
- 彻底搞清楚c#中的委托和事件
- React（一）react概述、组件、事件