USE_MERGE hint and the correct way to hint

From: Arul Ramachandran <contactarul_at_gmail.com>
Date: Wed, 7 Feb 2007 11:51:51 -0800
Message-ID: <1c1a62990702071151y575a47f1vce8e019b272d3ca4@mail.gmail.com>

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.

| 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

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Feb 07 2007 - 13:51:51 CST