ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 浅谈Oracle中隐式类型转换规律和影响

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

原创 Linux操作系统 作者:realkid4 时间:2013-07-21 12:52:48 0 删除 编辑

据大多数的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,其中包括了我们最常见的varchar2number两种类型。

 

 

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_cursorshared 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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
求道~

注册时间:2010-11-30

  • 博文量
    545
  • 访问量
    7629847