使用哈希连接 (Hash Join) 适合大表，返回大结果集
SQL> select * from test1, test2 where test1.object_id = test2.object_id and rown
um < 2;
执行计划
----------------------------------------------------------
Plan hash value: 3552420324
--------------------------------------------------------------------------------
-----
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 480 | | 415 (2)| 00:00:05 |
|* 1 | COUNT STOPKEY | | | | | | |
|* 2 | HASH JOIN | | 20281 | 9506K| 3408K| 415 (2)| 00:00:05 |
| 3 | TABLE ACCESS FULL| TEST2 | 20280 | 3168K| | 69 (3)| 00:00:01 |
| 4 | TABLE ACCESS FULL| TEST1 | 21604 | 3375K| | 3 (34)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<2)
2 - access("TEST1"."OBJECT_ID"="TEST2"."OBJECT_ID")
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
3 recursive calls
0 db block gets
307 consistent gets
259 physical reads
0 redo size
1923 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
使用嵌套循环连接 ， 适合小表返回小结果集
SQL> select /*+ use_nl( test1, test2) */ * from test1, test2 where test1.object_
id = test2.object_id and rownum < 2;
执行计划
----------------------------------------------------------
Plan hash value: 2978587790
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 320 | 781K (4)| 02:36:19 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | NESTED LOOPS | | 20281 | 6337K| 781K (4)| 02:36:19 |
| 3 | TABLE ACCESS FULL| TEST1 | 21604 | 3375K| 3 (34)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| TEST2 | 1 | 160 | 36 (3)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<2)
4 - filter("TEST1"."OBJECT_ID"="TEST2"."OBJECT_ID")
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
1923 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/98157/viewspace-662220/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
讲解Oracle中DUMP的转储方法
下一篇：
查看Oracle数据库阻塞
请登录后发表评论
登录
全部评论
