據大多數的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

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

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

| 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

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

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

| 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

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

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

| 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

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

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

| 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

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

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

| 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

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

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

| 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

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

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

| 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語句和執行計劃是一個非常精巧的體系和結構，雖然提供了型別隱式轉化功能，但是對我們開發人員而言，儘量不要使用。

淺談Oracle中隱式型別轉換規律和影響

相關文章