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

測試環境模擬： 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_c

測試環境模擬：
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

聲明：本網頁內容旨在傳播知識，若有侵權等問題請及時與本網聯系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com