|
ct_id = t2.object_id
and t1.object_id = 99999
Plan hash value: 1970191094
-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 4 | | | |
| 2 | MERGE JOIN | | 1 | 1 | 0 |00:00:00.01 | 4 | | | |
|* 3 | TABLE ACCESS FULL | TEST1 | 1 | 1 | 0 |00:00:00.01 | 4 | | | |
|* 4 | SORT JOIN | | 0 | 1 | 0 |00:00:00.01 | 0 | 73728 | 73728 | |
|* 5 | TABLE ACCESS FULL| TEST2 | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
-------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T1"."OBJECT_ID"=99999)
4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
filter("T1"."OBJECT_ID"="T2"."OBJECT_ID")
5 - filter("T2"."OBJECT_ID"=99999)
SQL> select /*+ ordered use_merge(t2)*/count(*)
from test1 t1, test2 t2
where t1.object_id = t2.object_id
and 1=2;
COUNT(*)
----------
0
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID 3duvzmx0wm2hv, child number 0
-------------------------------------
select /*+ ordered use_merge(t2)*/count(*) from test1 t1, test2 t2 where t1.object_id =
t2.object_id and 1=2
Plan hash value: 593691543
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | | | |
|* 2 | FILTER | | 1 | | 0 |00:00:00.01 | | | |
| 3 | MERGE JOIN | | 0 | 100 | 0 |00:00:00.01 | | | |
| 4 | SORT JOIN | | 0 | 100 | 0 |00:00:00.01 | 73728 | 73728 | |
| 5 | TABLE ACCESS FULL| TEST1 | 0 | 100 | 0 |00:00:00.01 | | | |
|* 6 | SORT JOIN | | 0 | 1000 | 0 |00:00:00.01 | 73728 | 73728 | |
| 7 | TABLE ACCESS FULL| TEST2 | 0 | 1000 | 0 |00:00:00.01 | | | |
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(NULL IS NOT NULL)
6 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
filter("T1"."OBJECT_ID"="T2"."OBJECT_ID")
|