| ALL_ROWS | FIRST_ROWS\(N) | ||
|---|---|---|---|
| {code:sql | titleBGColor=#FFFFFF | title=!http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/img/all_rows_hint.gif! | borderStyle=solid} SELECT /*+ ALL_ROWS */ * FROM T1 A, T2 B WHERE A.N4 = B.N4 AND A.N4 = 0; |
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
| 0 | SELECT STATEMENT | 1000K | 26M | 13 (77) | 00:00:01 | |
| HASH JOIN | 1000K | 26M | 13 (77) | 00:00:01 | |
| 2 | TABLE ACCESS BY INDEX ROWID | T1 | 1000 | 14000 | 1 (0) | 00:00:01 |
| INDEX RANGE SCAN | T1N4 | 1000 | 1 (0) | 00:00:01 | |
| 4 | TABLE ACCESS BY INDEX ROWID | T2 | 1000 | 14000 | 1 (0) | 00:00:01 |
| INDEX RANGE SCAN | T2N4 | 1000 | 1 (0) | 00:00:01 |
Statistics
– HASH 조인, 읽은 블록수 작다
– http://ukja.tistory.com/172
– http://scidb.tistory.com/entry/NO-Costing-in-CBO
|{code:sql|titleBGColor=#FFFFFF|title=!http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/img/first_rows_hint.gif!|borderStyle=solid}
SELECT /*+ FIRST_ROWS(20) */ * FROM T1 A, T2 B WHERE A.N4 = B.N4 AND A.N4 = 0;
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 29 | 812 | 2 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 29 | 812 | 2 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| T1 | 20 | 280 | 1 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | T1N4 | 1000 | | 1 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | T2N4 | 20 | | 1 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID | T2 | 20 | 280 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
140341 consistent gets
0 physical reads
0 redo size
0 sorts (memory)
0 sorts (disk)
1000000 rows processed
-- NL 조인, 읽은 블록수 크다
|