FILTER 执行次数

select  count(*) from SAVJ_ATOMJOURBAK where ((list_flag='1' and prt_flag='0') and acct_no not
in (select  acct_no  from savb_basicinfo1 where ((card_no is  not null  and base_acct_no is null
) or (book_flag='1' and bus_code=21))))

select count(*)
  from SAVJ_ATOMJOURBAK
 where list_flag = '1'
   and prt_flag = '0'
   and acct_no not in
       (select acct_no
          from savb_basicinfo1
         where ((card_no is not null and base_acct_no is null) or
               (book_flag = '1' and bus_code = 21)))

SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  8dx0u725jfawb, child number 0
-------------------------------------
select  count(*) from SAVJ_ATOMJOURBAK where ((list_flag='1' and prt_flag='0') and acct_no not
in (select  acct_no  from savb_basicinfo1 where ((card_no is  not null  and base_acct_no is null
) or (book_flag='1' and bus_code=21))))

Plan hash value: 3431125892

----------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
----------------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE     |                 |      1 |      1 |      1 |00:01:40.72 |    5428K|   4184K|
|*  2 |   FILTER            |                 |      1 |        |    254 |00:01:40.71 |    5428K|   4184K|
|*  3 |    TABLE ACCESS FULL| SAVJ_ATOMJOUR1  |      1 |    678 |    659 |00:00:00.01 |      68 |      0 |
|*  4 |    TABLE ACCESS FULL| SAVB_BASICINFO1 |    187 |      1 |    118 |00:01:40.71 |    5428K|   4184K|
----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter( IS NULL)
   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')) AND LNNVL("ACCT_NO"<>:B1)))

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

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

----------------------------------------------------------------------------
| Id  | Operation           | Name            | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                 |     1 |    37 |  2776K  (1)|
|   1 |  SORT AGGREGATE     |                 |     1 |    37 |            |
|*  2 |   FILTER            |                 |       |       |            |
|*  3 |    TABLE ACCESS FULL| SAVJ_ATOMJOUR1  |   678 | 25086 |    16   (0)|
|*  4 |    TABLE ACCESS FULL| SAVB_BASICINFO1 |     1 |    74 |  8186   (1)|
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM "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)))
   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') AND LNNVL("ACCT_NO"<>:B1))

Note
-----
   - 'PLAN_TABLE' is old version

24 rows selected.


25 rows selected.

SAVB_BASICINFO1 扫描187次

一般FILTER 对应的ID 下面有EXISTS
EXISTS里面有 绑定变量这种
它就表示了子查看没被解套,或者说是子查询没有展开
没发生 unnest

转载于:https://www.cnblogs.com/zhaoyangjian724/p/3798014.html