Oracle的隐式转换

简介: 都说Oracle存在NUMBER和VARCHAR2类型的隐式转换,严格意义上需要避免,但为何需要避免,从下面的实验进行验证。1. 创建测试表和索引create table tn (id number, name varchar2(1));create index idx_tn on tn (id);create index idx_tn on tn (name);分别对NUMBER类型的id字段,VARCHAR2类型的name字段创建索引。
都说Oracle存在NUMBER和VARCHAR2类型的隐式转换,严格意义上需要避免,但为何需要避免,从下面的实验进行验证。

1. 创建测试表和索引
create table tn (id number, name varchar2(1));
create index idx_tn on tn (id);
create index idx_tn on tn (name);
分别对NUMBER类型的id字段,VARCHAR2类型的name字段创建索引。

2. 查看VARCHAR2->NUMBER的隐式转换
SQL> select * from tn where id = 1;
no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 3532270966
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IDX_TN | 1 | 13 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------
"where id = 1"用的是列索引范围扫描。

SQL> select * from tn where id = '123';
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 3532270966
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IDX_TN | 1 | 13 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------
"where id = '123'",Oracle会将字符类型的123转换为NUMBER类型进行比较,此处仍可使用索引范围扫描,说明VARCHAR2->NUMBER的隐式转换,未对索引产生影响

3. 查看NUMBER->VARCHAR2的隐式转换
SQL> select * from tn where name = '123';
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 479240418
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 15 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TN | 1 | 15 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_TN_NAME | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
"where name = '123'"使用的是索引范围扫描。

SQL> select * from tn where name = 123;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 2655062619
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 15 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TN | 1 | 15 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
"where name = 123",Oracle会将数值类型的123转换为VARCHAR2字符类型,和name进行比较,此处用了全表扫描,说明name的列索引失效

总结
1. NAME和VARCHAR2之间可以进行隐式转换,其中VARCHAR2->NUMBER不会导致索引失效,NUMBER->VARCHAR2会让索引失效,因此这种隐式转换,是需要注意避免。
2. 之所以VARCHAR2->NUMBER不会让索引失效,我猜测是转换为where id = to_number('123')。NUMBER->VARCHAR2会让索引失效,我猜测是转换为where to_number(name) = 123。
3. 引申知识点,之所以上面id和name使用的是索引范围扫描,是因为建立的是非唯一B树索引,如果是unique索引,则会使用UNIQUE INDEX SCAN的扫描方式。

补充:
经lhrbest的指正,从谓词条件即可看出端倪。
​附:
​SQL> select * from tn where id = '123';
​Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ID"=123)

​SQL> select * from tn where name = 123;
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(TO_NUMBER("NAME")=123)
​可以看出此处对NAME做了TO_NUMBER转换,导致索引失效。
目录
相关文章
|
5天前
|
Oracle 关系型数据库 Java
实时计算 Flink版操作报错之读取Oracle数据库时遇到找不到驱动,是什么原因
在使用实时计算Flink版过程中,可能会遇到各种错误,了解这些错误的原因及解决方法对于高效排错至关重要。针对具体问题,查看Flink的日志是关键,它们通常会提供更详细的错误信息和堆栈跟踪,有助于定位问题。此外,Flink社区文档和官方论坛也是寻求帮助的好去处。以下是一些常见的操作报错及其可能的原因与解决策略。
实时计算 Flink版操作报错之读取Oracle数据库时遇到找不到驱动,是什么原因
|
10天前
|
Oracle 关系型数据库 MySQL
实时计算 Flink版操作报错合集之采集oracle的时候报ORA-65040:不允许从可插入数据库内部执行该操作如何解决
在使用实时计算Flink版过程中,可能会遇到各种错误,了解这些错误的原因及解决方法对于高效排错至关重要。针对具体问题,查看Flink的日志是关键,它们通常会提供更详细的错误信息和堆栈跟踪,有助于定位问题。此外,Flink社区文档和官方论坛也是寻求帮助的好去处。以下是一些常见的操作报错及其可能的原因与解决策略。
33 3
|
13天前
|
SQL Oracle 安全
Oracle11g更改数据库名(详细教程)
Oracle11g更改数据库名(详细教程)
20 1
|
13天前
|
DataWorks Oracle 关系型数据库
DataWorks操作报错合集之尝试从Oracle数据库同步数据到TDSQL的PG版本,并遇到了与RAW字段相关的语法错误,该怎么处理
DataWorks是阿里云提供的一站式大数据开发与治理平台,支持数据集成、数据开发、数据服务、数据质量管理、数据安全管理等全流程数据处理。在使用DataWorks过程中,可能会遇到各种操作报错。以下是一些常见的报错情况及其可能的原因和解决方法。
37 0
|
4天前
|
Oracle 关系型数据库 数据库
实时计算 Flink版产品使用合集之采集Oracle数据库时,归档日志大小暴增的原因是什么
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStreamAPI、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
4天前
|
SQL Oracle 关系型数据库
实时计算 Flink版产品使用合集之从Oracle数据库同步数据时,checkpoint恢复后无法捕获到任务暂停期间的变更日志,如何处理
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStreamAPI、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
4天前
|
SQL Oracle 关系型数据库
实时计算 Flink版产品使用合集之如何SQL同步数据到Oracle数据库中
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStreamAPI、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
5天前
|
Oracle 关系型数据库 数据库
实时计算 Flink版产品使用合集之Oracle归档日志一天就达到了15GB并导致数据库崩溃,是什么导致的
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
10天前
|
SQL Oracle 关系型数据库
实时计算 Flink版产品使用合集之可以通过配置Oracle数据库的schema注册表来监测表结构的变化吗
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
19 1
|
13天前
|
Oracle 关系型数据库 数据库
Oracle数据库协议适配器错误解决方法
Oracle数据库协议适配器错误解决方法
14 2