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

怼烎@ 2022-09-22 11:47 27阅读 0赞

原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秒执行完成