not in 2 not exists
07月 2, 2008 – 7:48 pm今天调整sql的时候发现not in的操作,oracle自动会把它调整not exists(可能还更关联的两张表的记录多少有关系)
网上介绍not in和not exists文章比较多,这里我不介绍了
SQL> set autot on
SQL> select t.test_list_id, t.title
2 from test_list t
3 where t.owner_id =25704147
4 and t.status >= 0
5 and t.test_list_id not in
6 ((select i.test_list_id
7 from test_list_item i
8 where i.test_list_id = t.test_list_id
9 and i.collect_item_id =
10 (select i1.collect_item_id
11 from collect_item i1
12 where i1.item_id =’123456′)))
13 /
—————————————————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————————————————
| 0 | SELECT STATEMENT | | 3 | 81 | 5 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| TEST_LIST | 3 | 81 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IND_TEST_LIST_OWNER_STA | 3 | | 1 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | IND_TEST_LIST_ITEM_ID_NUM_COL | 1 | 8 | 1 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | IDX_COLLECT_ITEM_ITEMID | 1 | 36 | 1 (0)| 00:00:01 |
—————————————————————————————————————
Predicate Information (identified by operation id):
—————————————————
1 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM “TEST_LIST_ITEM” “I” WHERE “I”.”TEST_LIST_ID”=:B1
AND “I”.”COLLECT_ITEM_ID”= (SELECT /*+ */ “I1″.”COLLECT_ITEM_ID” FROM “COLLECT_ITEM” “I1″ WHERE
“I1″.”ITEM_ID”=’123456′)))
3 - access(”T”.”OWNER_ID”=25704147 AND “T”.”STATUS”>=0 AND “T”.”STATUS” IS NOT NULL)
4 - access(”I”.”TEST_LIST_ID”=:B1 AND “I”.”COLLECT_ITEM_ID”= (SELECT /*+ */ “I1″.”COLLECT_ITEM_ID”
FROM “COLLECT_ITEM” “I1″ WHERE “I1″.”ITEM_ID”=’123456′))
filter(”I”.”COLLECT_ITEM_ID”= (SELECT /*+ */ “I1″.”COLLECT_ITEM_ID” FROM “COLLECT_ITEM” “I1″
WHERE “I1″.”ITEM_ID”=’123456′))
5 - access(”I1″.”ITEM_ID”=’123456′)