DBA手记:SQL_TRACE跟踪与诊断案例(二)
-----------------------
CATEGORYID NOT NULL NUMBER
ARTICLEID NOT NULL VARCHAR2(14)
ASSIGNTYPE NOT NULL VARCHAR2(1)
AUDITSTATUS NOT NULL NUMBER
SORTID NOT NULL NUMBER
UNPASS VARCHAR2(255)
我们发现了问题所在，因为ARTICLEID是个字符型数据,查询中给入的articleId= 20030700400141 是一个数字值，
Oracle发生潜在的数据类型转换，从而导致了索引失效：
SQL> select auditstatus,categoryid
2 from
3 categoryarticleassign where articleId=20030700400132;
AUDITSTATUS CATEGORYID
----------- ----------
9 94
0 383
0 695
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=110 Card=2 Bytes=38)
1 0 TABLE ACCESS (FULL) OF 'CATEGORYARTICLEASSIGN' (Cost=110 Card=2 Bytes=38)
********************************************************************************
select unpass from
categoryarticleassign where articleid='20030320000682' and categoryid='113'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 2 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 2 0 0
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 20
Rows Row Source Operation
------- ---------------------------------------------------
0 TABLE ACCESS BY INDEX ROWID CATEGORYARTICLEASSIGN
1 INDEX RANGE SCAN (object id 3080)
********************************************************************************
至此，这个问题得到了完满的解决。