这篇博客主要探讨了两个SQL查询的执行计划。第一个查询使用了MINUS操作符来排除OBJECTS1表中的记录，通过SORTUNIQUE进行全表扫描。第二个查询则使用了HASHJOINRIGHTANTI来实现相同目的，通过INDEXFULLSCAN和TABLEACCESSFULL操作。两个查询都涉及到了对象表的全量数据处理，关注点在于如何高效地排除特定记录。
SQL> explain plan for
2 select * from objects
3 minus
4 select * from objects1
5 /
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1313218784
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Ti
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 67090 | 5178K| | 1334 (1)| 00
| 1 | MINUS | | | | | |
| 2 | SORT UNIQUE | | 67090 | 5175K| 11M| 1330 (1)| 00
| 3 | TABLE ACCESS FULL| OBJECTS | 67090 | 5175K| | 95 (2)| 00
| 4 | SORT UNIQUE | | 30 | 2370 | | 4 (25)| 00
| 5 | TABLE ACCESS FULL| OBJECTS1 | 30 | 2370 | | 3 (0)| 00
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
SQL> explain plan for
2 select *
3 from objects
4 where not exists
5 (select * from objects1 where objects1.object_id = objects.object_id)
6 /
Explained.
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 212929155
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 67060 | 6024K| 96 (2)| 00:00:
|* 1 | HASH JOIN RIGHT ANTI| | 67060 | 6024K| 96 (2)| 00:00:
| 2 | INDEX FULL SCAN | PK_OBJECTS1 | 30 | 390 | 1 (0)| 00:00:
| 3 | TABLE ACCESS FULL | OBJECTS | 67090 | 5175K| 95 (2)| 00:00:
--------------------------------------------------------------------------------
254
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
