类型转换导致运行计划不走索引測试案例

SQL索引使用详解

最新推荐文章于 2024-10-18 13:27:52 发布

weixin_30678821

最新推荐文章于 2024-10-18 13:27:52 发布

阅读量55

点赞数

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/jhcelue/p/6868813.html

本文通过实例演示了在Oracle数据库中如何正确利用索引提高查询效率。重点分析了由于类型不匹配导致索引失效的问题，并给出了正确的解决方案。

測试环境模拟：
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