由于数据类型不一致导致的sql查询慢

在进行bbs的sql优化上，有条sql成本较高，在查询表结构时发现有索引，但是sql没有用到，是什么原因导致索引失效呢
下面是解决问题的一点思路，还请指正
1.先看执行计划
sys@BBS> SELECT M.ID, M.SENDUSERID, U.NICKNAME, M.TITLE, M.SENDTIME, M.STATE
  2    FROM BBS_MSGBOX M, BBS_USERINFO U
  3   WHERE U.ID = M.SENDUSERID
  4     AND M.GETUSERID = 26496
  5   ORDER BY SENDTIME DESC;


69 rows selected.


Elapsed: 00:00:00.42


Execution Plan
----------------------------------------------------------
Plan hash value: 26776061


------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                      |    41 |  2911 |  3599   (1)| 00:00:44 |
|   1 |  SORT ORDER BY                |                      |    41 |  2911 |  3599   (1)| 00:00:44 |
|*  2 |   HASH JOIN                   |                      |    41 |  2911 |  3598   (1)| 00:00:44 |
|   3 |    TABLE ACCESS BY INDEX ROWID| BBS_MSGBOX         |    41 |  2173 |    27   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | SYS_MSGBOX_GETUSERID |    43 |       |     3   (0)| 00:00:01 |
|   5 |    TABLE ACCESS FULL          | BBS_USERINFO       |   706K|    12M|  3566   (1)| 00:00:43 |
------------------------------------------------------------------------------------------------------
在此发现BBS_USERINFO全表扫
2.是否有索引
sys@BBS> l
  1* select index_name,column_name from dba_ind_columns where table_owner='CYOUBBS' and  table_name in ('BBS_MSGBOX','BBS_USERINFO')
sys@BBS> /


INDEX_NAME                     COLUMN_NAME
------------------------------ ----------------------------------------
SYS_MSGBOX_SENDID              SENDUSERID
SYS_MSGBOX_GETUSERID           GETUSERID
IDX_USERINFO_NICKNAME          NICKNAME
SYS_C0016002                   ID
USER_NAME_PK                   USERNAME
有索引，索引状态也是有效的
3.统计信息是否准确
sys@BBS> show parameter opt


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
filesystemio_options                 string      none
object_cache_optimal_size            integer     102400
optimizer_dynamic_sampling           integer     2
optimizer_features_enable            string      10.2.0.4
optimizer_index_caching              integer     0
optimizer_index_cost_adj             integer     100
optimizer_mode                       string      ALL_ROWS
optimizer_secure_view_merging        boolean     TRUE
plsql_optimize_level                 integer     2


sys@BBS> select table_name,last_analyzed,stattype_locked locked from dba_tab_statistics where owner='CYOUBBS' and  table_name in ('BBS_MSGBOX','BBS_USERINFO');


TABLE_NAME                     LAST_ANALYZE LOCKE
------------------------------ ------------ -----
BBS_MSGBOX                   10-MAY-12
BBS_USERINFO                 16-MAY-12
发现BBS_MSGBOX统计信息有问题，收集下
sys@BBS> exec dbms_stats.gather_table_stats('CYOUBBS','BBS_MSGBOX',cascade => true);


PL/SQL procedure successfully completed.


sys@BBS> select table_name,last_analyzed,stattype_locked locked from dba_tab_statistics where owner='CYOUBBS' and  table_name in ('BBS_MSGBOX','BBS_USERINFO');


TABLE_NAME                     LAST_ANALYZE LOCKE
------------------------------ ------------ -----
BBS_MSGBOX                   17-MAY-12
BBS_USERINFO                 16-MAY-12


现在统计信息已经ok了




Execution Plan
----------------------------------------------------------
Plan hash value: 26776061


------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                      |    40 |  2840 |  3598   (1)| 00:00:44 |
|   1 |  SORT ORDER BY                |                      |    40 |  2840 |  3598   (1)| 00:00:44 |
|*  2 |   HASH JOIN                   |                      |    40 |  2840 |  3597   (1)| 00:00:44 |
|   3 |    TABLE ACCESS BY INDEX ROWID| BBS_MSGBOX         |    40 |  2120 |    26   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | SYS_MSGBOX_GETUSERID |    40 |       |     3   (0)| 00:00:01 |
|   5 |    TABLE ACCESS FULL          | BBS_USERINFO       |   706K|    12M|  3566   (1)| 00:00:43 |
------------------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   2 - access("M"."SENDUSERID"=TO_NUMBER("U"."ID"))
   4 - access("M"."GETUSERID"=26496)


执行计划依然不变，不应该是这样的，索引状态也是ok的，最后发现一点


看执行计划中的Predicate Information信息
access("M"."SENDUSERID"=TO_NUMBER("U"."ID"))
发现Oracle对谓词进行隐式转换了，问题就在这
确认下
sys@BBS> desc BBS_MSGBOX
Name                                                  Null?    Type
----------------------------------------------------- -------- ------------------------------------
ID                                                             VARCHAR2(20)
SENDUSERID                                                     NUMBER(8)
GETUSERID                                                      NUMBER(8)
。。。。。


sys@BBS> desc BBS_USERINFO
Name                                                  Null?    Type
----------------------------------------------------- -------- ------------------------------------
ID                                                    NOT NULL VARCHAR2(40)
USERNAME                                              NOT NULL VARCHAR2(40)
NICKNAME                                              NOT NULL VARCHAR2(60)
。。。。。
显然两表的数据类型不一致，导致索引失效
4.问题知道了，联系开发人员，确认是否可以修改数据类型.数据类型修改了也就正常了。

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

转载于:http://blog.itpub.net/26390465/viewspace-1769341/