索引列要避免隐式转换

有不少人发现遇到索引使用不到的情况,这个时候除了NULL,索引代价比全表扫高外,多半就是遇到了隐式转换问题了,什么叫隐式转换,比如select * from ljb_test where id=123,而实际这个时候id的列为varchar2(20)而不是number型,这就出现了隐式转换。实际转化为select * from ljb_test where to_number(id)=123在这种情况下,这个索引列id就用不到索引了!

举例操作如下

SQL> drop table ljb_test;
Table dropped
SQL> create table ljb_test (id varchar2(20));
Table created
SQL> create index idx_ljb_test on ljb_test(id);
Index created
SQL> explain plan for select * from ljb_test where id='10';
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------
Plan hash value: 36299861
----------------------------------------------------------------------------------------------------------------
| Id  | Operation        | Name         | Rows  | Bytes | Cost (%CPU)| Time
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |              |     1 |    12 |     1   (0)| 00:00:01
|*  1 |  INDEX RANGE SCAN| IDX_LJB_TEST |     1 |    12 |     1   (0)| 00:00:01
------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("ID"='10')
Note
-----
   - dynamic sampling used for this statement
17 rows selected

SQL> explain plan for select * from ljb_test where id=10;
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------
Plan hash value: 2595830654
-------------------------------------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |     1 |    12 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| LJB_TEST |     1 |    12 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(TO_NUMBER("ID")=10)
Note
-----
   - dynamic sampling used for this statement
17 rows selected

请特别注意两个执行计划的不同,第一个1 – access(“ID”=’10′),而第二个是1 – filter(TO_NUMBER(“ID”)=10) ,可以很清楚的看出,第二个其实根本就是一个函数,所以这第二种写法是肯定用不到索引的!否则也就不存在什么函数索引的概念了!

总结:这样的错误在现实中应该是很常见的,很多被DBA逮住的性能低下造成大表全表扫描的语句大多都是这种情况!在写SQL的时候要特别留意关键谓词处字段的类型,查询中遵循一个简单的规则,是number的就填number,是varchar的就添加引号写varchar,避免隐式转换!说个小插曲,宁夏电信曾经出现一个执行频度很高的查询不走选择性索引,影响整体性能的情况,后来定位出该查询语句不走的原因是走了隐式转换的语句(类似filter(TO_NUMBER(“ID”)=10),该业务是新上的,原因居然是测试环境和生产环境表结构不一样,在测试环境中是number型,而生产环境却是varchar型,所以没加引号的查询在测试环境用的上索引,到生产就用不到了。最后处理方法很简单,避免隐式转换解决问题,不过这里还要强调另外一点,务必要保证生产和测试环境一致,否则很容易出问题,这个是我提的另一个注意点!

Trackback

no comment untill now

Add your comment now

切换到手机版