执行计划不走索引

sky13and23

SQL> explain plan for select a.ICPCODE,a.ICPSERVID,ShortcutText from   portal.service a, portal.wap_service b where a.icpcode = b.icpcode and a.icpservid = b.icpservid;

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

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
| Id  | Operation            |  Name                 | Rows  | Bytes | Cost  |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                       | 34517 |  1078K|  5299 |
|   1 |  NESTED LOOPS        |                       | 34517 |  1078K|  5299 |
|   2 |   TABLE ACCESS FULL  | SERVICE               |  1702K|    32M|  5299 |
|*  3 |   INDEX UNIQUE SCAN  | PK_WAPSERVICE_SERVID  |     1 |    12 |       |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
---------------------------------------------------

   3 - access("A"."ICPCODE"="B"."ICPCODE" AND "A"."ICPSERVID"="B"."ICPSERVID")

Note: cpu costing is off

16 rows selected.

为什么service 表不走索引PK_SERVICE_SERVID

下面是一些统计信息：

SQL> select count(*) from portal.service ;

  COUNT(*)
----------
   1702774
SQL> select count(*) from portal.wap_service ;

  COUNT(*)
----------
     34873

SQL> select a.last_analyzed,a.clustering_factor,a.blevel,a.leaf_blocks,a.distinct_keys,
  2  a.avg_leaf_blocks_per_key,a.avg_data_blocks_per_key,a.num_rows,a.table_name,a.index_name
  3   from dba_indexes a where table_name in ('SERVICE','WAP_SERVICE') and owner = 'PORTAL';

LAST_ANALYZED CLUSTERING_FACTOR     BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY   NUM_ROWS TABLE_NAME                     INDEX_NAME
------------- ----------------- ---------- ----------- ------------- ----------------------- ----------------------- ---------- ------------------------------ ------------------------------
2013-5-8 17:5             78750          2        4783          1144                       4                      68    1712433 SERVICE                        IDX_SERVATTR_ICPCODE
2013-5-8 17:5           1530193          2        6723       1719553                       1                       1    1719553 SERVICE                        PK_SERVICE_SERVID
2013-5-7 18:3             15415          1         127         34873                       1                       1      34873 WAP_SERVICE                    PK_WAPSERVICE_SERVID

SQL>
SQL>
SQL> select a.last_analyzed,a.blocks,a.num_rows,a.EMPTY_BLOCKS,a.avg_space,
  2  a.chain_cnt,a.avg_row_len,a.table_name
  3   from dba_tables a where table_name in ('SERVICE','WAP_SERVICE')and owner = 'PORTAL';

LAST_ANALYZED     BLOCKS   NUM_ROWS EMPTY_BLOCKS  AVG_SPACE  CHAIN_CNT AVG_ROW_LEN TABLE_NAME
------------- ---------- ---------- ------------ ---------- ---------- ----------- ------------------------------
2013-5-8 17:5      55079    1702023            0          0          0         222 SERVICE
2013-5-7 18:3       1135      34873            0          0          0         100 WAP_SERVICE

低调小马哥

首先这个sql没有谓词过滤条件，使用的两个关联连接条件，因此使用portal.service表的索引也要全索引扫描，最后回表，所以代价应该更高，所以优化器选择了全表扫描。可以对比下用hash_join连接，试试哪个逻辑读更少。

Maclean Liu(刘相兵

没有 条件 只有关联， SERVICE    充当驱动表的时候怎么走索引？

sky13and23

这个sql是业务所需，没有过滤条件也是和业务有关。

目前执行计划

SQL> select a.ICPCODE,a.ICPSERVID,ShortcutText from   portal.service a, portal.wap_service b where a.icpcode = b.icpcode and a.icpservid = b.icpservid;
34873 rows selected.

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=5299 Card=34517 Bytes=1104544)
   1    0   NESTED LOOPS (Cost=5299 Card=34517 Bytes=1104544)
   2    1     TABLE ACCESS (FULL) OF 'SERVICE' (Cost=5299 Card=1702023 Bytes=34040460)
   3    1     INDEX (UNIQUE SCAN) OF 'PK_WAPSERVICE_SERVID' (UNIQUE)
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
    1762481  consistent gets
      53102  physical reads
          0  redo size
    1162701  bytes sent via SQL*Net to client
      26220  bytes received via SQL*Net from client
       2326  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      34873  rows processed

修改驱动表后执行计划

SQL> select  /*+leading(b)*/a.ICPCODE,a.ICPSERVID,ShortcutText from   portal.service a, portal.wap_service b where a.icpcode = b.icpcode and a.icpservid = b.icpservid;
34873 rows selected.
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=5666 Card=34517 Bytes=1104544)
   1    0   HASH JOIN (Cost=5666 Card=34517 Bytes=1104544)
   2    1     INDEX (FAST FULL SCAN) OF 'PK_WAPSERVICE_SERVID' (UNIQUE) (Cost=14 Card=34873 Bytes=418476)
   3    1     TABLE ACCESS (FULL) OF 'SERVICE' (Cost=5299 Card=1702023 Bytes=34040460)
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      57512  consistent gets
      53104  physical reads
          0  redo size
    1162701  bytes sent via SQL*Net to client
      26220  bytes received via SQL*Net from client
       2326  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      34873  rows processed

我想要的执行计划
SQL> select  /*+index(a PK_SERVICE_SERVID)*/a.ICPCODE,a.ICPSERVID,ShortcutText from   portal.wap_service b,portal.service a where a.icpcode = b.icpcode and a.icpservid = b.icpservid;
34873 rows selected.
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=34888 Card=34517 Bytes=1104544)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'SERVICE' (Cost=2 Card=1 Bytes=20)
   2    1     NESTED LOOPS (Cost=34888 Card=34517 Bytes=1104544)
   3    2       INDEX (FAST FULL SCAN) OF 'PK_WAPSERVICE_SERVID' (UNIQUE) (Cost=14 Card=34873 Bytes=418476)
   4    2       INDEX (RANGE SCAN) OF 'PK_SERVICE_SERVID' (UNIQUE) (Cost=1 Card=1)
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      93933  consistent gets
         82  physical reads
          0  redo size
    1162512  bytes sent via SQL*Net to client
      26220  bytes received via SQL*Net from client
       2326  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      34873  rows processed

不知道为什么数据库不走我想要的执行计划，有什么办法可以得到我想要的执行计划

foxhuntwang

建议你先把2个表 重新做一次 统计信息分析 再看看执行计划

sky13and23

这两个表都是最新的统计分析

kevinlin.ora

在正常情况下，数据库选择COST最小的执行计划，由于各种原因，其不一定就是我们想要的。
建立索引PK_SERVICE_SERVID(icpcode,icpservid,ShortcutText)，(前两列的顺序按实际排)，试一下，低调小马哥在2#楼提到需要回表，回表的代价有时也很高。

sky13and23

这个做法也测试了，cost减小一些，但是consistent gets和physical reads还是很高，我主要是希望得到
/*+index(a PK_SERVICE_SERVID)*/这样的执行计划，这样可以降低不少consistent gets和physical reads。由于sql是JDBC发出，又不能加hint 所以想请叫一些有没有什么办法能够使执行计划和我想要强制索引的执行计划走一样的

Stone

   3 - access("A"."ICPCODE"="B"."ICPCODE" AND "A"."ICPSERVID"="B"."ICPSERVID")

Note: cpu costing is off

根据上面explain里的 "Note: cpu costing is off"，建议看看是不是系统信息没有收集，或者看看参数“_optimizer_cost_model”的设置。具体可以参考：
https://forums.oracle.com/forums/thread.jspa?threadID=1128379
http://www.itpub.net/thread-911829-1-1.html
http://yong321.freeshell.org/oranotes/SystemStats.txt

希望有所帮助。

另外，感觉最好说明一下Oracle的版本，运行的平台，这样可以帮助分析下问题。

如果还是感觉有问题，做一个10053的trace，可以更深入地看下问什么Oracle认为这样的计划跟好，毕竟Oracle是根据信息做判断嘛 :)