高效的SQL（隱式轉換導致不走索引）

高效的SQL（隱式轉換導致不走索引）

1、建立實驗表doudou,索引建立在varchar2型別的id列上，索引名DOUDOU_INDEX

doudou@TEST> select count(*) from doudou;

COUNT(*)

----------

1000

doudou@TEST> select index_name,table_owner,table_name,TABLE_TYPE, PCT_FREE,BUFFER_POOL,TEMPORARY from user_indexes where table_name='DOUDOU';

INDEX_NAME TABLE_OWNE TABLE_NAME TABLE_TYPE PCT_FREE BUFFER_POOL TE

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

DOUDOU_INDEX DOUDOU DOUDOU TABLE 10 DEFAULT N

2、開啟執行計劃，並執行2不同的SQL，並觀察執行計劃

doudou@TEST> set timing on

doudou@TEST> set autot on

doudou@TEST> select id from doudou where id=3;

ID

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

3

Elapsed: 00:00:00.01

Execution Plan

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

Plan hash value: 845489848

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

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

| 0 | SELECT STATEMENT | | 1 | 12 | 3 (0)| 00:00:01 |

|* 1 | TABLE ACCESS FULL| DOUDOU | 1 | 12 | 3 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

1 - filter(TO_NUMBER("ID")=3)

【這裡有to_number的轉換，導致沒有走索引，所以，編寫高效的SQL也要注意索引列的型別，不要讓資料庫幫你隱式轉換。隱式轉換之後不走索引。】

Note

-----

- dynamic sampling used for this statement

Statistics

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

314 recursive calls

0 db block gets

58 consistent gets

0 physical reads

0 redo size

404 bytes sent via SQL*Net to client

400 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

6 sorts (memory)

0 sorts (disk)

1 rows processed

doudou@TEST> select id from doudou where id='3';

ID

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

3

Elapsed: 00:00:00.01

Execution Plan

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

Plan hash value: 3961861220

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

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

| 0 | SELECT STATEMENT | | 1 | 12 | 1 (0)| 00:00:01 |

|* 1 | INDEX RANGE SCAN| DOUDOU_INDEX | 1 | 12 | 1 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

1 - access("ID"='3')

Note

-----

- dynamic sampling used for this statement

Statistics

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

9 recursive calls

0 db block gets

14 consistent gets

0 physical reads

0 redo size

404 bytes sent via SQL*Net to client

400 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

總結：

編寫SQL的時，where語句中過濾條件中請注意索引列的型別，不要讓oracle做隱式轉換，因為隱式轉換是不走索引的。

附表：

實驗環境

sys@TEST> select * from v$version;

BANNER

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

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod

PL/SQL Release 10.2.0.4.0 - Production

CORE 10.2.0.4.0 Production

TNS for Linux: Version 10.2.0.4.0 - Production

NLSRTL Version 10.2.0.4.0 – Production

sys@TEST> show parameter optimizer_mode

NAME TYPE VALUE

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

optimizer_mode string ALL_ROWS

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26442936/viewspace-750131/，如需轉載，請註明出處，否則將追究法律責任。

高效的SQL（隱式轉換導致不走索引）

相關文章