| 4 | INDEX FAST FULL SCAN| IDX_TAB2_A | 1001 | 3003 | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL |TABLE_1 | 500K| 11M| 502 (1)| 00:00:07 |
--------------------------------------------------------------------------------------
call count cpu elapsed disk query current rows
------- ------ -------- -------------------- ---------- ---------- ----------
Parse 1 0.00 0.01 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 18 0 100
------- ------ -------- -------------------- ---------- ---------- ----------
total 3 0.00 0.01 0 18 0 100
EXPLAIN PLAN FOR
select
a.a,a.b
from table_1 a
where a.a in (select a from table_2);
SELECT * FROM TABLE(DBMS_XPLAN.display());
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 20000 | 508 (2)| 00:00:07 |
|* 1 | HASH JOIN RIGHT SEMI | | 1000 | 20000 | 508 (2)| 00:00:07 |
| 2 | INDEX FAST FULL SCAN| IDX_TAB2_A | 1001 | 3003 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL |TABLE_1 | 500K| 8300K| 502 (1)| 00:00:07 |
------------------------------------------------------------------------------------
call count cpu elapsed disk query current rows
------- ------ -------- -------------------- ---------- ---------- ----------
Parse 1 0.00 0.12 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 10 0 100
------- ------ -------- -------------------- ---------- ---------- ----------
total 3 0.00 0.12 0 10 0 100
EXPLAIN PLAN FOR
select
a.a,a.b
from table_1 a
where exists (select null from table_2 b where a.a=b.a);
SELECT * FROM TABLE(DBMS_XPLAN.display());
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 20000 | 508 (2)| 00:00:07 |
|* 1 | HASH JOIN RIGHT SEMI | | 1000 | 20000 | 508 (2)| 00:00:07 |
| 2 | INDEX FAST FULL SCAN| IDX_TAB2_A | 1001 | 3003 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL |TABLE_1 | 500K| 8300K| 502 (1)| 00:00:07 |
------------------------------------------------------------------------------------
call count cpu elapsed disk query current rows
------- ------ -------- -------------------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execu