索引关键字的隐式转换分析

从等待事件找到具体session的sql语句进行调控：

看eygle深入浅出中关于最后sql性能诊断部分，其中提到的几个案例，对于大表缺少必要索引而导致全表扫描影响性能；大表相应字段存在索引但是oracle并不走索引，关键字中如果存在转换，很可能oracle会使索引失效（选择hint都会失效）而选择全表扫描。

对于大表缺少索引根据v$session和v$session_wait视图找到sid的等待事件，如果对于大表全表扫描user I/O等待事件db file scattered需要过多关注通过v$sqltext的hash_value和address和v$session的sql_hash_value和sql_address找到sql语句，然后对相应的关键列建立合适的索引，减少全表扫描。

这里自己结合性能诊断和测试例子来模拟下关键字转换刀子索引失效而全表扫描。

Sql>create table test20(id varchar2(10));

SQL> declare

2 begin

3 for i in 1..1000 loop

4 insert into test20 values(to_char(i));

5 commit;

6 end loop;

7 end;

8 /

PL/SQL procedure successfully completed

SQL> create index index_test20 on test20(id);

Index created

SQL> execute dbms_stats.gather_table_stats('xiaoyu','test20');

PL/SQL procedure successfully completed

Test20列id上建立了合适的索引，看看下面的执行计划

SQL> explain plan for select * from test20 where id='456';

Explained

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT

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

Plan hash value: 2611328442

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time

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

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

|* 1 | INDEX RANGE SCAN| INDEX_TEST20 | 1 | 3 | 1 (0)| 00:00:01

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

Predicate Information (identified by operation id):

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

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

13 rows selected

Id上存在索引，oracle选择了高效索引。

SQL> explain plan for select * from test20 where id=456;

Explained

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT

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

Plan hash value: 883513759

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

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

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

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

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

Predicate Information (identified by operation id):

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

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

13 rows selected

由于ID列是varchar2（10）类型的，此时where查询条件进行了隐式转换（把varchar2（10）转换为number类型的），此时oracle会放弃索引而选择全表扫描然后隐式转换varchar2（10）为number类型再进行比较，此时我们进行hint下试试看能否启动索引。

强制hint走索引

select /*+index(test20)*/* from test20 where id=456

SELECT STATEMENT, GOAL = ALL_ROWS 3 1 3

TABLE ACCESS FULL XIAOYU TEST20 3 1 3

此时oracle还是选择了全表扫描，看来varchar2（10）转换number已经让索引无法利用了。

SQL> desc test18;

Name Type Nullable Default Comments

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

OBJECT_NAME VARCHAR2(128) Y

OBJECT_ID NUMBER Y

SQL> select b.index_name, b.table_name, a.column_name

2 from user_ind_columns a, user_indexes b

3 where b.table_name = 'TEST18'

4 and a.index_name = b.index_name

5 ;

INDEX_NAME TABLE_NAME COLUMN_NAME

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

INDEX_TEST18 TEST18 OBJECT_ID

INDEX_TEST18_OBJ TEST18 OBJECT_NAME

下面再来看看number转换为varchar2（10）的隐式转换

SQL> explain plan for select * from test18 where object_id=456;

Explained

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT

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

Plan hash value: 2319938169

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)

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

| 0 | SELECT STATEMENT | | 1 | 29 | 2 (0)

| 1 | TABLE ACCESS BY INDEX ROWID| TEST18 | 1 | 29 | 2 (0)

|* 2 | INDEX RANGE SCAN | INDEX_TEST18 | 1 | | 1 (0)

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

Predicate Information (identified by operation id):

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

2 - access("OBJECT_ID"=456)

14 rows selected

SQL> explain plan for select * from test18 where object_id='456';

Explained

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT

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

Plan hash value: 2319938169

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)

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

| 0 | SELECT STATEMENT | | 1 | 29 | 2 (0)

| 1 | TABLE ACCESS BY INDEX ROWID| TEST18 | 1 | 29 | 2 (0)

|* 2 | INDEX RANGE SCAN | INDEX_TEST18 | 1 | | 1 (0)

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

Predicate Information (identified by operation id):

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

2 - access("OBJECT_ID"=456)

14 rows selected

看来即使此时关键字上存在隐式转换，oracle依然选择了索引，从上述实验我们需要注意的是，索引关键字上必须减少甚至杜绝隐式转换。

这里再阐述下上述两个例子的隐式转换和索引利用的区别

根据隐式转换的是如下进行的：

Select * from test20 where id=6789

此时的id为varchar2（10）类型，隐式转换的原则是select * from test20 where to_number(id)=6789，由于索引上存储的都是varchar2（10）类型的ID关键字值，很自然索引无法利用。看来在select语句中，number和varchar的隐式转换的优先级：varchar隐式转

换为number类型的。

下面就可以很自然的解释为什么select * from test18 where object_id=’456’的隐式转换为：select * from test18 where object_id=to_number(‘456’),此时索引关键字并没有进行转换，转换的是字符型’456’隐式转换为to_number(‘456’)，索引可以高效利用。

下面有关于dml语句的隐式转换，其实和select语句一样，varchar2优先转换为number类型

A：

SQL> explain plan for update test20 set id='7890' where id=123;

Explained

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT

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

Plan hash value: 3897430916

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

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

| 0 | UPDATE STATEMENT | | 1 | 3 | 3 (0)| 00:00:01 |

| 1 | UPDATE | TEST20 | | | | |

|* 2 | TABLE ACCESS FULL| TEST20 | 1 | 3 | 3 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - filter(TO_NUMBER("ID")=123)

14 rows selected

B:

SQL> explain plan for update test18 set object_id=789 where object_id='123';

Explained

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT

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

Plan hash value: 176192607

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time

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

| 0 | UPDATE STATEMENT | | 1 | 5 | 1 (0)| 00:00:01

| 1 | UPDATE | TEST18 | | | |

|* 2 | INDEX RANGE SCAN| INDEX_TEST18 | 1 | 5 | 1 (0)| 00:00:01

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

Predicate Information (identified by operation id):

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

2 - access("OBJECT_ID"=123)

14 rows selected

还可以测试下关于varchar2类型和date类型的转换，varchar2隐式转换为to_date类型。

这里就不列出例子了。

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25362835/viewspace-1057135/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25362835/viewspace-1057135/