我这个提示为什么没有用？

显示全部楼层 · 2010-10-8 09:31:21

select */*+ ordered use_nl(t1) */ from t1,t4 where t1.object_id = t4.object_id
我想用NL，但是系统总是出现HASH，WHY？

千问 · 2010-10-8 09:31:21

版本是10.2.0.1

千问 · 2010-10-8 09:31:21

原帖由 bigacer 于 2010-3-30 15:00 发表
select */*+ ordered use_nl(t1) */ from t1,t4 where t1.object_id = t4.object_id
我想用NL，但是系统总是出现HASH，WHY？
select */*+ ordered use_nl(t1) */ from t1,t4 where t1.object_id = t4.object_id
==>
select /*+ ordered use_nl(t1)*/* from t1,t4 where t1.object_id = t4.object_id
掉个顺序看看

千问 · 2010-10-8 09:31:21

select /*+ ordered use_nl(t1 t4)*/* from t1,t4 where t1.object_id = t4.object_id

千问 · 2010-10-8 09:31:21

原帖由 Toms_zhang 于 2010-3-30 15:13 发表

select */*+ ordered use_nl(t1) */ from t1,t4 where t1.object_id = t4.object_id
==>
select /*+ ordered use_nl(t1)*/* from t1,t4 where t1.object_id = t4.object_id
掉个顺序看看

同意版主大人的。

千问 · 2010-10-8 09:31:21

原帖由 myttsd 于 2010-3-30 15:33 发表
select /*+ ordered use_nl(t1 t4)*/* from t1,t4 where t1.object_id = t4.object_id
顶一下，myttsd的正确。
SQL> explain plan for
2select * from t t1,t t2
3 where t1.object_id=t2.object_id;
已解释。
| Id| Operation
| Name | Rows| Bytes |TempSpc| Cost (%CPU)| Time|
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT ||1900K| 362M| | 35566 (2)| 00:07:07 |
|*1 |HASH JOIN ||1900K| 362M| 203M| 35566 (2)| 00:07:07 |
| 2 | TABLE ACCESS FULL| T|1900K| 181M| |7658 (2)| 00:01:32 |
| 3 | TABLE ACCESS FULL| T|1900K| 181M| |7658 (2)| 00:01:32 |
-----------------------------------------------------------------------------------
SQL> explain plan for
2select /*+ ordered use_nl(t1) */ * from t t1,t t2
3 where t1.object_id=t2.object_id;
已解释。
| Id| Operation
| Name | Rows| Bytes |TempSpc| Cost (%CPU)| Time|
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT ||1900K| 362M| | 35566 (2)| 00:07:07 |
|*1 |HASH JOIN ||1900K| 362M| 203M| 35566 (2)| 00:07:07 |
| 2 | TABLE ACCESS FULL| T|1900K| 181M| |7658 (2)| 00:01:32 |
| 3 | TABLE ACCESS FULL| T|1900K| 181M| |7658 (2)| 00:01:32 |
-----------------------------------------------------------------------------------
SQL> explain plan for
2select /*+ ordered use_nl(t2,t1) */ * from t t1,t t2
3 where t1.object_id=t2.object_id;
已解释。
| Id| Operation
| Name | Rows| Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT ||1900K| 362M|14G(2)|999:59:59 |
| 1 |NESTED LOOPS||1900K| 362M|14G(2)|999:59:59 |
| 2 | TABLE ACCESS FULL| T|1900K| 181M|7658 (2)| 00:01:32 |
|*3 | TABLE ACCESS FULL| T| 1 | 100 |7656 (2)| 00:01:32 |
---------------------------------------------------------------------------
SQL> explain plan for
2select /*+ leading(t2) use_nl(t1) */ * from t t1,t t2
3 where t1.object_id=t2.object_id;
已解释。
| Id| Operation
| Name | Rows| Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT ||1900K| 362M|14G(2)|999:59:59 |
| 1 |NESTED LOOPS||1900K| 362M|14G(2)|999:59:59 |
| 2 | TABLE ACCESS FULL| T|1900K| 181M|7658 (2)| 00:01:32 |
|*3 | TABLE ACCESS FULL| T| 1 | 100 |7656 (2)| 00:01:32 |
---------------------------------------------------------------------------

千问 · 2010-10-8 09:31:21

试试看下面的

千问 · 2010-10-8 09:31:21

留下脚印一串串