SQL> explain plan for select *

  from OPS$CZTEST1.SAVJ_ATOMJOURBAK

 where ((list_flag = '1' and prt_flag = '0') and

       acct_no not in

       (select acct_no

           from OPS$CZTEST1.savb_basicinfo1

          where ((card_no is not null and base_acct_no is null) or

                (book_flag = '1' and bus_code = 21))));  2    3    4    5    6    7    8 

Explained.

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

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Plan hash value: 3909253513

--------------------------------------------------------------------------------------

| Id  | Operation                             | Name                         | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT      |                                       | 54953 |    18M|   594M  (1)      |999:59:59 |

|*  1 |  FILTER                               |                                       |             |            |                           |          |

|*  2 |   TABLE ACCESS FULL   | SAVJ_ATOMJOUR0  | 54954 |    18M|  1042   (3)       | 00:00:13 |

|*  3 |   TABLE ACCESS FULL  | SAVB_BASICINFO1   |     1       |    73   | 11220   (1)     | 00:02:15 |

--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM

              "OPS$CZTEST1"."SAVB_BASICINFO1" "SAVB_BASICINFO1" WHERE ("BASE_ACCT_NO" IS

              NULL AND "CARD_NO" IS NOT NULL OR "BUS_CODE"=21 AND "BOOK_FLAG"='1') AND

              LNNVL("ACCT_NO"<>:B1)))

   2 - filter("PRT_FLAG"='0' AND "LIST_FLAG"='1')

   3 - filter(("BASE_ACCT_NO" IS NULL AND "CARD_NO" IS NOT NULL OR

              "BUS_CODE"=21 AND "BOOK_FLAG"='1') AND LNNVL("ACCT_NO"<>:B1))

21 rows selected.

可以看到这里:

( NOT EXISTS (SELECT /*+ */ 0 FROM

              "OPS$CZTEST1"."SAVB_BASICINFO1" "SAVB_BASICINFO1" WHERE ("BASE_ACCT_NO" IS

              NULL AND "CARD_NO" IS NOT NULL OR "BUS_CODE"=21 AND "BOOK_FLAG"='1') AND

              LNNVL("ACCT_NO"<>:B1)))

子查询没有展开,FILTER 循环类似于NL循环,会导致表SAVB_BASICINFO1访问多次,not in 可以改写成左关联的形式,改写如下:

SQL> explain plan for select count(*)

  from OPS$CZTEST1.SAVJ_ATOMJOURBAK a,

       (select acct_no

          from OPS$CZTEST1.savb_basicinfo1

         where (card_no is not null and base_acct_no is null)

        or

          (book_flag = '1' and bus_code = 21)) b

 where a.list_flag = '1'

   and a.prt_flag = '0'

   and a.acct_no = b.acct_no(+)

   and b.acct_no is null;  2    3    4    5    6    7    8    9   10   11  

Explained.

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

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Plan hash value: 4182698075

-----------------------------------------------------------------------------------------------

| Id  | Operation                           | Name                          | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |

-----------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT     |                                       |     1      |   110 |       | 13060   (1)| 00:02:37 |

|   1 |  SORT AGGREGATE        |                                      |     1       |   110 |       |            |          |

|*  2 |   HASH JOIN ANTI            |                                     |             1 |   110 |  2632K| 13060   (1)| 00:02:37 |

|*  3 |    TABLE ACCESS FULL | SAVJ_ATOMJOUR0  | 54954 |  1985K|       |  1041   (2)| 00:00:13 |

|*  4 |    TABLE ACCESS FULL | SAVB_BASICINFO1 |   166K|    11M|       | 11216   (1)| 00:02:15 |

-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("ACCT_NO"="ACCT_NO")

   3 - filter("PRT_FLAG"='0' AND "LIST_FLAG"='1')

   4 - filter("BASE_ACCT_NO" IS NULL AND "CARD_NO" IS NOT NULL OR "BUS_CODE"=21 AND

              "BOOK_FLAG"='1')

19 rows selected.