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
