FILTER 执行次数

select  count(*) from SAVJ_ATOMJOURBAK where ((list_flag='1' and prt_flag='0') and acct_no notin (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 notin (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 version24 rows selected.25 rows selected.SAVB_BASICINFO1 扫描187次一般FILTER 对应的ID 下面有EXISTSEXISTS里面有 绑定变量这种它就表示了子查看没被解套,或者说是子查询没有展开没发生 unnest