USE_MERGE hint and the correct way to hint
- From: "Arul Ramachandran" <contactarul@xxxxxxxxx>
- To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 7 Feb 2007 11:51:51 -0800
Here is the query:
SELECT *
FROM acc, cus_acc
WHERE acc_id = id
AND
acc_id in (1,2,3....100 values)
GROUP BY acc_id
If I use the hint USE_MERGE(acc cus_acc) I get the desired execution plan
below.
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows |
Bytes | Cost |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50 |
1050 | 68 |
| 1 | SORT GROUP BY NOSORT | | 50 |
1050 | 68 |
| 2 | MERGE JOIN | | 50 |
1050 | 68 |
| 3 | INLIST ITERATOR | |
| | |
| 4 | TABLE ACCESS BY INDEX ROWID| ACC | 50
| 500 | 48 |
|* 5 | INDEX RANGE SCAN | PK_ACC | 50
| | 3 |
|* 6 | SORT JOIN | | 257 |
2827 | 20 |
| 7 | INLIST ITERATOR | |
| | |
|* 8 | INDEX RANGE SCAN | PK_CUS_ACC | 257 |
2827 | 4 |
-------------------------------------------------------------------------------------------
I've heard the correct way to hint is use two hints per table, so I am
wondering if I need to use additional hints to be more specific. Should I
hint /*+ ORDERED USE_MERGE(acc cus_acc) */?
I played around with changing the order of the tables in the FROM clause and
in the use_merge hint. It does change the order in the plan, but the logical
reads come to the same.
--
Arul
Other related posts:
