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转换,导致索引失效。
目录
相关文章
|
16天前
|
存储 自然语言处理 Oracle
Oracle数据库字符集概述及修改方式
【8月更文挑战第15天】Oracle 数据库字符集定义了数据的编码方案,决定可存储的字符类型及其表示方式。主要作用包括数据存储、检索及跨系统传输时的正确表示。常见字符集如 AL32UTF8 支持多语言,而 WE8MSWIN1252 主用于西欧语言。修改字符集风险高,可能导致数据问题,需事先备份并评估兼容性。可通过 ALTER DATABASE 语句直接修改或采用导出-导入数据的方式进行。完成后应验证数据完整性。此操作复杂,须谨慎处理。
|
12天前
|
数据采集 Oracle 关系型数据库
实时计算 Flink版产品使用问题之怎么实现从Oracle数据库读取多个表并将数据写入到Iceberg表
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
8天前
|
存储 Oracle 关系型数据库
分享几个Oracle数据库日常维护中常见的问题
分享几个Oracle数据库日常维护中常见的问题
26 1
|
22天前
|
SQL Oracle 关系型数据库
"揭秘!一键解锁Oracle日志清理魔法,让海量归档日志无处遁形,守护数据库健康,告别磁盘空间告急噩梦!"
【8月更文挑战第9天】随着Oracle数据库在企业应用中的普及,归档日志管理对保持数据库健康至关重要。归档日志记录所有更改,对数据恢复极为重要,但也可能迅速占用大量磁盘空间影响性能。利用Oracle提供的RMAN工具,可通过编写Shell脚本来自动清理归档日志。脚本包括设置环境变量、连接数据库、检查和删除指定时间前的日志,并记录执行情况。通过Cron作业定时运行脚本,可有效管理日志文件,确保数据库稳定运行。
53 7
|
20天前
|
Oracle 关系型数据库 MySQL
Mysql和Oracle数据库死锁查看以及解决
【8月更文挑战第11天】本文介绍了解决MySQL与Oracle数据库死锁的方法。MySQL可通过`SHOW ENGINE INNODB STATUS`查看死锁详情,并自动回滚一个事务解除死锁;也可手动KILL事务。Oracle则通过查询V$LOCK与V$SESSION视图定位死锁,并用`ALTER SYSTEM KILL SESSION`命令终止相关会话。预防措施包括遵循ACID原则、优化索引及拆分大型事务。
|
22天前
|
监控 Oracle 关系型数据库
"深度剖析:Oracle SGA大小调整策略——从组件解析到动态优化,打造高效数据库性能"
【8月更文挑战第9天】在Oracle数据库性能优化中,系统全局区(SGA)的大小调整至关重要。SGA作为一组共享内存区域,直接影响数据库处理能力和响应速度。本文通过问答形式介绍SGA调整策略:包括SGA的组成(如数据缓冲区、共享池等),如何根据负载与物理内存确定初始大小,手动调整SGA的方法(如使用`ALTER SYSTEM`命令),以及利用自动内存管理(AMM)特性实现智能调整。调整过程中需注意监控与测试,确保稳定性和性能。
64 2
|
2天前
|
SQL Oracle 关系型数据库
实时计算 Flink版产品使用问题之Oracle数据库是集群部署的,怎么进行数据同步
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
2月前
|
Oracle 安全 关系型数据库
|
8天前
|
Oracle 关系型数据库 数据库
Oracle数据库备份脚本分享-Python
Oracle数据库备份脚本分享-Python
10 0
|
2月前
|
开发框架 Oracle 关系型数据库
ABP框架使用Oracle数据库,并实现从SQLServer中进行数据迁移的处理
ABP框架使用Oracle数据库,并实现从SQLServer中进行数据迁移的处理

推荐镜像

更多
下一篇
云函数