Oracle直方图导致SQL不走索引.

在ITPUB 上看到一个帖子 http://www.itpub.net/thread-1875212-1-1.html

同一条SQL语句，只有查询条件不一样，查询返回的结果集都为0，一个走了全表扫描，一个走索引。查看全表扫描的SQL语句：
SQL走全表，产生了2422609个逻辑读，cost为535K
SQL> SELECT URL,YHZH,HFRZY,HFLR,SPURL,TPURL,YPURL,SCSJ,LY,JCSJ FROM YHXX_HFXX T 
2       WHERE T.URL=‘http://club.kdnet.net/dispbbs.asp?id=10165509_boardid=1‘   
3        /
no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 2068618995
-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |   917K|   266M|   535K  (1)| 01:47:05 |
|*  1 |  TABLE ACCESS FULL| YHXX_HFXX |   917K|   266M|   535K  (1)| 01:47:05 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("T"."URL"=‘http://club.kdnet.net/dispbbs.asp?id=10165509_b oardid=1‘)
             
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
    2422609  consistent gets
          3  physical reads
       5520  redo size
        880  bytes sent via SQL*Net to client
        458  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

强制HINT使用索引，产生4个逻辑读，但是cost比全表扫描高：643K --显然这个计划才是最好的，但是为什么cost会这么高呢？下面会分析.

SQL> SELECT  /*+index(YHXX_HFXX IDX_YHXX_HFXX_URL)*/
2       URL,YHZH,HFRZY,HFLR,SPURL,TPURL,YPURL,SCSJ,LY,JCSJ
3       FROM YHXX_HFXX 
4      WHERE URL=‘http://club.kdnet.net/dispbbs.asp?id=10165509_boardid=1‘  

5       /
no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 518948569
-------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                   |   917K|   266M|   643K  (1)| 02:08:48 |
|   1 |  TABLE ACCESS BY INDEX ROWID| YHXX_HFXX         |   917K|   266M|   643K  (1)| 02:08:48 |
|*  2 |   INDEX RANGE SCAN          | IDX_YHXX_HFXX_URL |   917K|       | 10735   (1)| 00:02:09 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("URL"=‘http://club.kdnet.net/dispbbs.asp?id=10165509_boardid=1‘)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        880  bytes sent via SQL*Net to client
        458  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

这里最奇怪的地方是当我们强制用hint的时候，虽然也走了索引，但是执行计划显示的estimated rows居然是917K和全部扫描一样多！

那就意味着优化器根据统计信息推断出里面所有的行的URL字段和where 条件里的URL=‘http://club.kdnet.net/dispbbs.asp?id=10165509_boardid=1‘ 值完全相同.这显然不可能。

这里我们注意到URL这个字符串值很长，在有直方图信息的时候，estimated row 首先考虑直方图信息，而且直方图只会取前32个字符生产一个浮点数，用这个浮

点数来计算直方图的统计信息。所以如果URL字段收集了直方图信息，并且URL的前32位完全相同，那么以为着在直方图统计的时候，会认为它们是一样的。

这里URL的前32都是“http://club.kdnet.net/dispbbs.as”，里面所有数据的URL都是针对同一个地址不同的id，这样就被认为是完全一样的了。

那么Oracle就认为数据要全部选出来，当然这种情况下扫描表是最好的了。因为选了第一个计划。

解决方案: 删除直方图统计信息

exec dbms_stats.gather_table_stats(ownname => ‘YQJK‘,tabname => ‘YHXX_HFXX‘,estimate_percent => 10,method_opt=>‘for columns  URL size 1‘,cascade=>TRUE) ;

删除直方图信息请参考 http://www.cnblogs.com/princessd8251/articles/3855707.html

Oracle直方图导致SQL不走索引.,古老的榕树,5-wow.com