類型轉換導致執行計畫不走索引測試案例

標籤：des   os   io   for   ar   amp   sp   line   on   

測試環境類比：
SQL> drop table t_col_type purge;
create table t_col_type(id varchar2(20),col2 varchar2(20),col3 varchar2(20));
insert into t_col_type select rownum,‘abc‘,‘efg‘ from dual connect by level<=10000;
commit;
create index idx_id on t_col_type(id);
set linesize 1000
set autotrace traceonlydrop table t_col_type purge
           *
ERROR at line 1:
ORA-00942: table or view does not exist

 

SQL> select * from t_col_type where id=6;

Execution Plan
----------------------------------------------------------
Plan hash value: 3191204463

--------------------------------------------------------------------------------
| Id  | Operation                                  | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                        |     1    |    36 |     8   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL            | T_COL_TYPE |     1    |    36 |     8   (0)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

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

Note
-----
   - dynamic sampling used for this statement

Statistics
----------------------------------------------------------
          5  recursive calls
          0  db block gets
         64  consistent gets
          0  physical reads
          0  redo size
        640  bytes sent via SQL*Net to client
        469  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

這裡好像有點奇怪啊，明明建了index [create index idx_id on t_col_type(id);]但是為啥沒有用到呢？

---查看錶上列是否有索引
SQL> select index_name , table_name,column_name from all_ind_columns where table_name =‘T_COL_TYPE‘;

INDEX_NAME
------------------------------------------------------------
TABLE_NAME
------------------------------------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
IDX_ID
T_COL_TYPE
ID

----查看錶結構
SQL> desc scott.T_COL_TYPE
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 VARCHAR2(20)----------注意這裡的字元類型
 COL2                                               VARCHAR2(20)
 COL3                                               VARCHAR2(20)

再次關注下 執行計畫中的謂語資訊：
1 - filter(TO_NUMBER("ID")=6)  ----------這裡發生了類型轉換

所以在執行計畫中就無法用已有的索引，那麼如何才能讓他正確走索引呢？

 

select * from t_col_type where id=‘6‘;------注意下這裡的區別加了單引號，表明這是個字元，

Execution Plan
----------------------------------------------------------
Plan hash value: 3998173245

------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 |  SELECT STATEMENT                         |                       |     1 |    36 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID   | T_COL_TYPE |     1 |    36 |     2   (0)| 00:00:01 |
|*  2 |              INDEX RANGE SCAN             | IDX_ID              |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID"=‘6‘)

Note
-----
   - dynamic sampling used for this statement

Statistics
----------------------------------------------------------
          9  recursive calls
          0  db block gets
         39  consistent gets
          1  physical reads
          0  redo size
        640  bytes sent via SQL*Net to client
        469  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

 

 

類型轉換導致執行計畫不走索引測試案例