ORACLE:INDEX FULL SCAN--99%是最烂的执行计划

原sql：

SELECT *
  FROM (  SELECT c.id,c.username,c.keyword,c.batchid,i.bc_id,c.title,i.checked,c.srcimg,i.targ etimg,c.providerid,c.pricerange,c.bc_param,c.service
            FROM aladdin_imgcutcheck c,aladdin_imgcomp i,vr_keyword_baidu_aladdin b
           WHERE     c.bc_id = i.bc_id
                 AND c.srcimg = i.srcimg
                 AND c.keyword = b.keyword
                 AND c.batchid = b.batchid
                 AND c.state = '0'
                 AND b.state = 0
                 AND i.checked = '0'
        ORDER BY b.keyword DESC)
 WHERE ROWNUM <= :1
Plan hash value: 1095357246
-------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                           |       |       |  6289 (100)|          |
|*  1 |  COUNT STOPKEY                  |                           |       |       |            |          |
|   2 |   VIEW                          |                           |    10 | 28580 |  6289   (1)| 00:01:16 |
|   3 |    NESTED LOOPS                 |                           |    10 |  6950 |  6289   (1)| 00:01:16 |
|   4 |     NESTED LOOPS                |                           |  1302 |   726K|  3763   (1)| 00:00:46 |
|*  5 |      TABLE ACCESS BY INDEX ROWID| VR_KEYWORD_BAIDU_ALADDIN  | 69228 |  1284K|   239   (0)| 00:00:03 |
|   6 |       INDEX FULL SCAN DESCENDING| IND_KEYWORD               |   352 |       |     3   (0)| 00:00:01 |
|*  7 |      TABLE ACCESS BY INDEX ROWID| ALADDIN_IMGCUTCHECK       |     7 |  3864 |    20   (0)| 00:00:01 |
|*  8 |       INDEX RANGE SCAN          | I_ALADDIN_IMGCUTCHECK_KEY |    25 |       |     2   (0)| 00:00:01 |
|*  9 |     TABLE ACCESS BY INDEX ROWID | ALADDIN_IMGCOMP           |     1 |   124 |     2   (0)| 00:00:01 |
|* 10 |      INDEX UNIQUE SCAN          | I_ALADDIN_IMGCOMP         |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------
   1 - :1 (NUMBER): 10
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=:1)
   5 - filter(TO_NUMBER("B"."STATE")=0)
   7 - filter(("C"."STATE"='0' AND "C"."BATCHID"="B"."BATCHID"))
   8 - access("C"."KEYWORD"="B"."KEYWORD")
   9 - filter("I"."CHECKED"='0')
  10 - access("C"."BC_ID"="I"."BC_ID" AND "C"."SRCIMG"="I"."SRCIMG")

|* 5 | TABLE ACCESS BY INDEX ROWID| VR_KEYWORD_BAIDU_ALADDIN | 69228 | 1284K| 239 (0)| 00:00:03 |
| 6 | INDEX FULL SCAN DESCENDING| IND_KEYWORD | 352 | | 3 (0)| 00:00:01 |
最烂的执行计划，索引全扫描外加回表，由于受ORDER BY b.keyword DESC影响，oracle错误的选择了index扫描 原来5分钟执行不完，修改如下
（新建了一个索引--I_ALADDIN_IMGCOMP_CBST--）

SQL> explain plan for SELECT *
  2    FROM (  SELECT /*+ no_index(b IND_KEYWORD) */ c.id,3                   c.username,4                   c.keyword,5                   c.batchid,6                   i.bc_id,7                   c.title,8                   i.checked,9                   c.srcimg,10                   i.targetimg,11                   c.providerid,12                   c.pricerange,13                   c.bc_param,14                   c.service
 15              FROM match_manual.aladdin_imgcutcheck c,16                   match_manual.aladdin_imgcomp i,17                   match_manual.vr_keyword_baidu_aladdin b
 18             WHERE     c.bc_id = i.bc_id
 19                   AND c.srcimg = i.srcimg
 20                   AND c.keyword = b.keyword
 21                   AND c.batchid = b.batchid
 22                   AND c.state = '0'
 23                   AND b.state = 0
 24                   AND i.checked = '0'
 25          ORDER BY b.keyword DESC)
 26   WHERE ROWNUM <= 10;
Explained.
SQL> set lines 300
SQL> set pagesize 0
SQL> /
Plan hash value: 3545217694
--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name                     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                          |    10 | 28580 |       | 45543   (1)| 00:09:07 |
|*  1 |  COUNT STOPKEY                  |                          |       |       |       |            |          |
|   2 |   VIEW                          |                          |  3941 |    10M|       | 45543   (1)| 00:09:07 |
|*  3 |    SORT ORDER BY STOPKEY        |                          |  3941 |  2674K|  5752K| 45543   (1)| 00:09:07 |
|*  4 |     HASH JOIN                   |                          |  3941 |  2674K|  2096K| 44963   (1)| 00:09:00 |
|*  5 |      TABLE ACCESS FULL          | VR_KEYWORD_BAIDU_ALADDIN | 69228 |  1284K|       |  1012   (2)| 00:00:13 |
|*  6 |      TABLE ACCESS BY INDEX ROWID| ALADDIN_IMGCUTCHECK      |     1 |   552 |       |     5   (0)| 00:00:01 |
|   7 |       NESTED LOOPS              |                          |  7799 |  5148K|       | 43592   (1)| 00:08:44 |
|*  8 |        INDEX RANGE SCAN         | I_ALADDIN_IMGCOMP_CBST   | 10813 |  1309K|       |   207   (0)| 00:00:03 |
|*  9 |        INDEX RANGE SCAN         | INX_BC_ID                |     2 |       |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=10)
   3 - filter(ROWNUM<=10)
   4 - access("C"."KEYWORD"="B"."KEYWORD" AND "C"."BATCHID"="B"."BATCHID")
   5 - filter(TO_NUMBER("B"."STATE")=0)
   6 - filter("C"."STATE"='0' AND "C"."SRCIMG"="I"."SRCIMG")
   8 - access("I"."CHECKED"='0')
   9 - access("C"."BC_ID"="I"."BC_ID")
现在 2秒执行完成

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!