关于hint问题的咨询

[putaofeng]

我的一个sql单从执行计划来看很好，但是运行起来效率很差。后来看数据发现IDX_PROBPRN_REGION_CODE的结果集很大，NESTED LOOPS用在这里就会很慢。听说过这种情况下用MERGE JOIN会很高效，但是我不清楚hint怎么指定使用MERGE JOIN。

请高人指点一下，谢谢啦！

SELECT STATEMENT, GOAL = CHOOSE
SORT UNIQUE
NESTED LOOPS
NESTED LOOPS
TABLE ACCESS BY INDEX ROWID Object owner=LIFEREPT Object name=PROBPRN
INDEX RANGE SCAN Object owner=LIFEREPT Object name=IDX_PROBPRN_REGION_CODE
TABLE ACCESS BY INDEX ROWID Object owner=LIFEREPT Object name=PROBPRN_TRACE
INDEX RANGE SCAN Object owner=LIFEREPT Object name=IDX_PROBPRN_TRACE
TABLE ACCESS BY INDEX ROWID Object owner=LIFEREPT Object name=PROBPRN_DESC
INDEX RANGE SCAN Object owner=LIFEREPT Object name=IDX_PROBPRN_DESC

[cenlmmx]

select /*+ USE_MERGE(PROBPRN, PROBPRN_TRACE, PROBPRN_DESC) */ ... from ... where ...

最好少用merge join,它会消耗大量的IO,仅适合于连接表上无索引而且返回数据量较大.

建议你使用hash join试试. select /*+ USE_HASH(PROBPRN, PROBPRN_TRACE, PROBPRN_DESC) */