浅谈Oracle中隐式类型转换规律和影响

最新推荐文章于 2024-05-04 09:38:05 发布
最新推荐文章于 2024-05-04 09:38:05 发布
阅读量129 收藏
点赞数
文章标签： 数据库 数据结构与算法

据大多数的Oracle性能问题，根源无外乎三类：架构设计问题、详细模块设计细节和SQL语句。从数量上看，三类性能问题中，SQL语句问题是我们最常见的一种性能问题点。开发DBA很大一部分日常工作精力是消耗在SQL语句调优和问题调整上。

 

Oracle数据表列类型是一种强类型数据结构，不同类型的数据有不同的操作使用方法。当不同类型的数据进行直接的操作，没有显式的进行转换时，我们称之为“隐式类型转换”。SQL隐式类型转换可以帮助SQL执行过程，很多时候会引起一些问题。本篇主要想介绍一些问题点。

 

1、环境准备

 

我们依然选择Oracle 11R2进行试验。

 

 

SQL> select * from v$version;

 

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

PL/SQL Release 11.2.0.1.0 - Production

CORE        11.2.0.1.0         Production

TNS for Linux: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 – Production

 

 

创建数据表t，其中包括了我们最常见的varchar2和number两种类型。

 

 

SQL> create table t as select * from dba_objects;

 

Table created

 

SQL> desc t;

Name           Type          Nullable Default Comments

-------------- ------------- -------- ------- --------

OWNER          VARCHAR2(30)  Y                        

OBJECT_NAME    VARCHAR2(128) Y                        

SUBOBJECT_NAME VARCHAR2(30)  Y                        

OBJECT_ID      NUMBER        Y                        

DATA_OBJECT_ID NUMBER        Y                        

OBJECT_TYPE    VARCHAR2(19)  Y                        

（篇幅原因，有省略……）

NAMESPACE      NUMBER        Y                        

EDITION_NAME   VARCHAR2(30)  Y                        

 

 

2、字符串转数字

 

我们首先看一下，如果目标列是一个数字类型，但是输入值是一个字符串，应该如何处理？

 

为了帮助我们理解对SQL执行计划的作用，我们添加一些索引对象。

 

 

--object_id列是一个数字类型对象

SQL> create index idx_t_id on t(object_id);

Index created

 

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);

PL/SQL procedure successfully completed

 

SQL> explain plan for select * from t where object_id=1000; --类型匹配，无隐式转换；

Explained

 

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 514881935

--------------------------------------------------------------------------------

| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Ti

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |          |     1 |    97 |     2   (0)| 00

|   1 |  TABLE ACCESS BY INDEX ROWID| T        |     1 |    97 |     2   (0)| 00

|*  2 |   INDEX RANGE SCAN          | IDX_T_ID |     1 |       |     1   (0)| 00

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("OBJECT_ID"=1000)

 

14 rows selected

 

 

在没有发生类型转换的时候，是可以正确发挥索引路径的作用。下面我们如果对object_id数字类型的对应一个字符串’1000’，结果如何呢？

 

SQL> explain plan for select * from t where object_id='1000';

Explained

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 514881935

--------------------------------------------------------------------------------

| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Ti

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |          |     1 |   103 |     2   (0)| 00

|   1 |  TABLE ACCESS BY INDEX ROWID| T        |     1 |   103 |     2   (0)| 00

|*  2 |   INDEX RANGE SCAN          | IDX_T_ID |     1 |       |     1   (0)| 00

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("OBJECT_ID"=1000)

 

14 rows selected

 

 

注意结果：首先，SQL语句发生了隐式转换，而且转换的地方在字符串1000上面，转换为数字1000。这样的变化没有发生在数字列上面。其次，这种转换没有发生在列上面，没有影响到idx_t_id的路径。

 

如果我们使用绑定变量，现象如何呢？

 

 

--定义绑定变量x，是字符串类型；

SQL> var x varchar2(10);

SQL> exec :x := '1000';

 

PL/SQL procedure successfully completed

x

---------

1000

 

 

执行SQL语句。

 

 

SQL> select /*+Demo*/count(*) from t where object_id=:x;

 

  COUNT(*)

----------

         1

x

---------

1000

 

SQL> select sql_id, executions from v$sqlarea where sql_text like 'select /*+Demo*/count(*) from t%';

 

SQL_ID        EXECUTIONS

------------- ----------

7cj6jfauhjvua          1

 

SQL> select * from table(dbms_xplan.display_cursor(sql_id => '7cj6jfauhjvua'));

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

SQL_ID  7cj6jfauhjvua, child number 0

-------------------------------------

select /*+Demo*/count(*) from t where object_id=:x

Plan hash value: 1700799834

------------------------------------------------------------------------------

| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |

------------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |          |       |       |     1 (100)|          |

|   1 |  SORT AGGREGATE   |          |     1 |     5 |            |          |

|*  2 |   INDEX RANGE SCAN| IDX_T_ID |     1 |     5 |     1   (0)| 00:00:01 |

------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("OBJECT_ID"=TO_NUMBER(:X))

 

19 rows selected

 

 

我们使用dbms_xplan.display_cursor从shared pool中抽取出带绑定变量的SQL语句执行计划。在谓词信息中，看到了access动作中to_number处理。

 

说明：Oracle发现类型的不匹配之后，如果数据表列是数字类型，而输入值是一个字符串，Oracle会对字符串进行to_number函数处理。这种情况是正向的，不会影响到索引列的使用。

 

 

说道to_number，难道Oracle就不检查变换是不是符合标准吗？

 

 

SQL> explain plan for select * from t where object_id='kk';

Explained

 

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 514881935

--------------------------------------------------------------------------------

| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Ti

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |          |     1 |   103 |     2   (0)| 00

|   1 |  TABLE ACCESS BY INDEX ROWID| T        |     1 |   103 |     2   (0)| 00

|*  2 |   INDEX RANGE SCAN          | IDX_T_ID |     1 |       |     1   (0)| 00

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("OBJECT_ID"=TO_NUMBER('kk'))

14 rows selected

 

SQL> select * from t where object_id='kk';

select * from t where object_id='kk'

 

ORA-01722: invalid number

 

 

在进行类型转换的时候，Oracle依然用to_number处理。但是在执行过程中，会报错。

 

那么，如果我们把关系返回来，会怎么样？

 

3、数字转字符串

 

我们处理一下字符串情况。

 

 

--edition_name是字符串类型

SQL> update t set edition_name=to_char(object_id);

72775 rows updated

 

SQL> commit;

Commit complete

 

 

在列上添加索引，判断没有隐式转换的情况。

 

 

 

SQL> create index idx_t_edname on t(edition_name);

Index created

 

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);

PL/SQL procedure successfully completed

 

SQL> explain plan for select * from t where edition_name='1000'; --类型匹配过程

Explained

 

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 1587954238

--------------------------------------------------------------------------------

| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |              |     1 |   103 |     2   (0)

|   1 |  TABLE ACCESS BY INDEX ROWID| T            |     1 |   103 |     2   (0)

|*  2 |   INDEX RANGE SCAN          | IDX_T_EDNAME |     1 |       |     1   (0)

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("EDITION_NAME"='1000')

 

14 rows selected

 

 

如果我们在取值上给一个数字类型。

 

 

--不匹配情况

SQL> explain plan for select * from t where edition_name=1000;

Explained

 

 

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 1601196873

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |     1 |   103 |   273   (1)| 00:00:04 |

|*  1 |  TABLE ACCESS FULL| T    |     1 |   103 |   273   (1)| 00:00:04 |

--------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter(TO_NUMBER("EDITION_NAME")=1000)

 

13 rows selected

 

 

注意，我们看到了不同，如果列是一个字符串类型，输入一个数字类型条件值，就会让Oracle在列上面添加to_number函数。也就是说，会对字符串对象进行处理。

 

如果我们使用绑定变量方式，如何呢？

 

 

SQL> var x number;

SQL> exec :x := 1000;

 

PL/SQL procedure successfully completed

x

---------

1000

 

SQL> select /*+Demo2*/count(*) from t where edition_name=:x;

 

  COUNT(*)

----------

         1

x

---------

1000

 

SQL> select sql_id, executions from v$sqlarea where sql_text like 'select /*+Demo2*/count(*) from t%';

 

SQL_ID        EXECUTIONS

------------- ----------

7vbr16s0ra00x          1

 

SQL> select * from table(dbms_xplan.display_cursor(sql_id => '7vbr16s0ra00x'));

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

SQL_ID  7vbr16s0ra00x, child number 0

-------------------------------------

select /*+Demo2*/count(*) from t where edition_name=:x

Plan hash value: 2966233522

---------------------------------------------------------------------------

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |      |       |       |   273 (100)|          |

|   1 |  SORT AGGREGATE    |      |     1 |     6 |            |          |

|*  2 |   TABLE ACCESS FULL| T    |     1 |     6 |   273   (1)| 00:00:04 |

---------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - filter(TO_NUMBER("EDITION_NAME")=:X)

 

19 rows selected

 

 

由于对列进行了to_number处理，索引idx_t_edname不能使用上。

 

同样，这个处理是对edition_name进行选择to_number处理。如果:x是一个数字，但是edition_name存在一些不能转换的字符串，会如何呢？

 

 

--可以执行；

SQL> select edition_name from t where edition_name=1000;

EDITION_NAME

------------------------------

1000

 

 

我们对数据进行一些处理，不影响最终结果，但是可能引起执行计划中的矛盾。

 

 

SQL> insert into t (edition_name) values ('kkk');

1 row inserted

 

SQL> commit;

Commit complete

 

SQL> select * from t where edition_name=1000;

 

select * from t where edition_name=1000

 

ORA-01722: invalid number

 

 

报错，相同的数据结果。但是使用字符串1000，结果集合可以正常获得。

 

 

SQL> select edition_name from t where edition_name='1000';

 

EDITION_NAME

------------------------------

1000

 

 

这个就是由于Oracle执行计划而带来的问题。当发生类型转换的时候，Oracle倾向于对字符串类型进行函数处理转换类型，这种处理可能发生在条件值上，也可能发生在列上。

 

5、结论

 

Oracle SQL语句和执行计划是一个非常精巧的体系和结构，虽然提供了类型隐式转化功能，但是对我们开发人员而言，尽量不要使用。

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/17203031/viewspace-766790/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/17203031/viewspace-766790/

ciqu9915
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
oracle数据隐式转换规则
12-24
该文档描述了oracle数据的转换的一些规则，如字符和数字的转换，字符和日期的转换
oracle不能隐式转换,ORACLE 尽量不使用隐式转换
weixin_28706541的博客
04-11 320
/*************************************************一、主题：ORACLE 尽量不使用隐式转换类型转换的两种方式：显示类型转换(Explicit) 自动类型转换(Implicit)*************************************************/====================================...
Oracle中的数据类型隐式转换(implicit conversion)
lzhdim的专栏
12-24 617
和其他的关系型数据库一样，oracle中也能进行一些隐式的数据转换，这对我们写SQL语句有非常用，我们可以不必麻烦地手动转化很多类型的字符。虽然前面我们介绍了一些使用例如to_char,to_date的函数进行强制转换的方法，但是隐式转换也还是不错的。 Oracle可以隐式地进行一些变量类别之间转化，例如从字符串转换到数值,看下面的例子。 SQL> select ename,sal fr...
oracle隐式转换能禁用吗,Oracle隐式转换
weixin_36255837的博客
04-06 330
和其他的关系型数据库一样， oracle 中也能进行一些隐式的数据转换，这对我们写 SQL 语句有 非常 用，我们可以不必麻烦地手动转化很多类型的字符。虽然前面我们介绍了一些使用例如to_char,to_date的函数进行强制转换的方法，但是隐式转换也还是不错的。 Orac和其他的关系型数据库一样，oracle中也能进行一些隐式的数据转换，这对我们写SQL语句有非常用，我们可以不必麻烦地手动转化很...
关于oracle数据库的隐式转换的操作
q1054261752的博客
10-31 2188
由于oracle数据库制作得比较智能，所以各个数据库类型之间除了使用转换函数这些显式转换之外，也存在着各种隐式的转换规则，这些常用规则如下：     1.字符型(varchar2,   varchar)如果由数字组成，则可以直接转换成数字(number)。      2.字符型(varchar2,  varchar)如果按照指定的日期格式(例如：“08-9月-81”)则可以自动转换为date类
Mysql隐式类型转换原则
12-14
本文将要为您介绍的是Mysql隐式类型转换原则，具体实现方法: 　MySQL 的隐式类型转换原则： 　- 两个参数至少有一个是 NULL 时，比较的结果也是 NULL，例外是使用 <=> 对两个 NULL 做比较时会返回 1，这两种...
JavaScript强制类型转换和隐式类型转换操作示例
11-28
本文实例讲述了JavaScript强制类型转换和隐式类型转换。分享给大家供大家参考，具体如下： 在JavaScript中声明变量不需指定类型， 对变量赋值也没有类型检查，同时JavaScript允许隐式类型转换。这些特征说明...
JavaScript中运算符规则和隐式类型转换示例详解
10-19
JavaScript中运算符规则的... 这是每个学习Javascript的新手们都应该知道的一个问题，下面这篇文章主要给大家介绍了关于JavaScript中运算符规则和隐式类型转换的相关资料，需要的朋友可以参考借鉴，下面来一起看看吧。
MySQL隐式类型的转换陷阱和规则
09-10
先是通过一个实例给大家讲述在MySQL隐式类型的转换时遇到的陷阱，而后给大家介绍了MySQL隐式类型的规则，对大家操作mysql隐式类型的时候具有一定的参考借鉴价值，下面来一起看看吧。
C++中隐式类型转换学习笔记
01-20
1.1 std::ref源码中reference_wrapper隐式类型转换 在std::ref的实现中有如下一段代码： template class reference_wrapper : public _Reference_wrapper_base<typename>::type> { _Tp* _M_data; public: ...
Oracle数据类型隐式转换和显示转换
weixin_34119545的博客
12-15 624
2019独角兽企业重金招聘Python工程师标准>>> ...
oracle 数据隐式转换bug
huhu07的专栏
06-01 378
1. select * from t1 where channelid = 893;当数据类型是varchar2(32) 并且数据 channelid 字段有值 'xif' 执行sql语句时会报错 ,错误如下三行ORA-01722: 无效数字01722. 00000 -  "invalid number"*Cause:    *Action:...
Oracle 隐式转换与显式转换
滴水石穿
12-01 3241
隐式转换的发生条件          当源数据的类型和目标数据的类型不同的时候，如果没有转换函数，就会发生隐式转换，也称自动转换。当然，有些情况下有些类型是不可以发生转换的，比如说从DATE类型转换到NUMBER类型就会报错。   隐式转换的问题          第一，  隐式转换的最大问题就是转换时会导致索引的无效，进而可能导致全表扫描。当表的数据量很大的时候，产生会很大的性能问题
oracle隐式转换 性能,关于ORACLE隐式转换后性能问题
weixin_34236572的博客
04-10 264
SELECT TM.MONEY_CODE FROM T_CONTRACT_MASTER T,T_MONEY TM WHERE T.MONEY_ID = TM.MONEY_ID AND T.POLICY_CODE = ?问题出现：今儿生产代码性能扫描这段脚本被揪出来了，原因是这玩意儿执行时间过长，把后面的代码兄弟都给堵住了，然后发现这家伙在做全表扫，一开始纳闷，这不对啊，T.POLICY_CODE上...
oracle隐式类型转换
weixin_30532973的博客
01-15 74
我们的系统的的选择框的选项是存储在一个数据库表中的，其中大致包含了选择框的id以及选择选项的数据，大致如下表。其中fieldid的类型为int，optionVal为int，optionName为varchar。 fieldid optionVal optionName 1 0 北京 1 1 南京 1 2 天津 1 3 杭州 2 0 拉面 2 1 米饭 今天在...
Oracle因数据不一致而导致的隐式转换错误一例
cmg3399的博客
09-27 280
今天，开发同事说他在测试库执行一条SQL的时候，报ORA-01722: invalid number错误，但是在生产库和灰度库执行同一条SQL却能够正常执行，SQL如下： select ItemStatus ...
oracle 隐式转换 bug,隐式转换bug
weixin_42136477的博客
04-15 124
执行下段SQL，结果报错，ORA-01722无效数字SELECT *FROM (SELECT ROWNUM R, H.*FROM (SELECT T.ESEQID, T.ATTACHNAME, T.INFOCODEFROM NEWSADMIN.ANN_BASINFO TWHERE T.ATTACHTYPE = 0AND T.NOTICEDATE >= TO_DATE('2013-04-09'...
Redis 实战之压缩列表
最新发布
奔走的蚂蚁的博客
05-04 804
压缩列表是一种为节约内存而开发的顺序型数据结构。压缩列表被用作列表键和哈希键的底层实现之一。压缩列表可以包含多个节点，每个节点可以保存一个字节数组或者整数值。添加新节点到压缩列表， 或者从压缩列表中删除节点， 可能会引发连锁更新操作， 但这种操作出现的几率并不高。
报表控件Stimulsoft在JavaScript报告工具中的事件：查看器事件（上）
励志做最业余的专业博主，控件产品可以私我~
04-28 842
在本文中，我们为JS报告工具中的查看器事件提供了全面的指南，包括它们的详细描述、参数列表等一系列详细内容。
显式类型转换和隐式类型转换区别
10-17
显式类型转换和隐式类型转换的区别在于，显式类型转换需要程序员手动指定要转换的数据类型，而隐式类型转换则是由编译器自动完成的，不需要程序员手动干预。 在显式类型转换中，程序员需要使用强制类型转换符号来指定要转换的数据类型，例如将一个整数转换为浮点数可以使用以下语句： ```c++ int a = 10; float b = (float)a; ``` 在这个例子中，我们使用了强制类型转换符号 `(float)` 将整数 `a` 转换为浮点数。 而在隐式类型转换中，编译器会自动将一种数据类型转换为另一种数据类型，例如将一个整数和一个浮点数相加时，编译器会自动将整数转换为浮点数，然后再进行加法运算。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值