Oracle join using Hint USE_NL USE_HASH

Question

What is the best way to Force execution plan to do only nested loop joins for all tables using Hint USE_NL in once case,
And in other case to do only Hash Join using USE_HASH hint for all tables I want to run both query and see which has low cost in execution plan and use, please suggest

My doubt is in which sequence i should put for all 4 tables inside HINT like below USE_NL(bl1_gain_adj,customers,bl1_gain,bl1_reply_code)

SELECT bl1_gain_adj.adj_seq_no,
bl1_gain_adj.amount_currency ,
bl1_gain_adj.gain_seq_no, 
customers.loan_key,
customers.customer_key,
FROM
bl1_gain_adj,
customers,
bl1_gain,
bl1_reply_code
WHERE
bl1_gain.loan_key = customers.loan_key
AND bl1_gain.customer_key = customers.customer_key
AND bl1_gain.receiver_customer = customers.customer_no
AND bl1_gain.cycle_seq_no = customers.cycle_seq_no
AND bl1_reply_code.gain_code = bl1_gain.gain_code
AND bl1_reply_code.revenue_code = 'RC'
AND bl1_gain_adj.gain_seq_no = bl1_gain.gain_seq_no
AND bl1_gain_adj.customer_key = bl1_gain.customer_key;

Records in tables
---------------
 bl1_gain_adj = 100 records    
 customers = 10 Million records   
 bl1_gain = 1 Million records   
 bl1_reply_code = 100 million records

It's not that clear to me. Can't you simply insert the hint you want in your query and check the resulting plan? Also, consider switching to ANSI SQL, instead of the old Oracle way — Aleksej, Jan 04 '17 at 09:20
My doubt is in which sequence i should put for all 4 tables inside HINT like below USE_NL(bl1_gain_adj,customers,bl1_gain,bl1_reply_code) — User332772, Jan 04 '17 at 09:25
Simply check the execution plan. Oracle evaluates several plans and always takes the plan with the least costs - unless you overwrite with a hint. — Wernfried Domscheit, Jan 04 '17 at 09:25
why do you want to use nested loop? it is the most costly way with millions of rows — Thomas, Jan 04 '17 at 09:38
If you're aiming to improve performance, and if bl1_gain_adj really does have only 100 records, then I would look to move that from a join to a scalar subquery in order to take advantage of the scalar subquery caching. (You'd only need to query the table twice in the select list, because you can get away with switching `bl1_gain_adj.gain_seq_no` to `bl1_gain.gain_seq_no`, since that column is part of the join condition between `bl1_gain_adj` and `bl1_gain`). — Boneist, Jan 04 '17 at 12:10
Past experience suggests that when you start wanting to add hints to a query something else is wrong. This could be table design, statistics, misunderstanding of data profiles, etc. Hints are a Band-Aid that can quickly cause more trouble. If the profile of data in those tables changes then your hints may force an unsuitable plan. — BriteSponge, Jan 04 '17 at 14:10

Aleksej · Answer 1 · 2017-01-04T11:04:57.800

Keeping aside the choice of the most appropriate hint for your query (if any), the order you write the table names/aliases in the USE_NL hint does not matter.

According to Oracle documentation:

Note that USE_NL(table1 table2) is not considered a multi-table hint because it is a shortcut for USE_NL(table1) and USE_NL(table2)

About USE_NL, Oracle says:

The USE_NL hint instructs the optimizer to join each specified table to another row source with a nested loops join, using the specified table as the inner table.

That is, if you write USE_NL(table1 table2 table3 table4) this means "use all these tables as inner tables in a nested loop join"; if your query only has these 4 tables, the hint will be ignored for at least one table: to use a table as inner, we need another table to use as outer, so it's impossible to use all the tables as inner.

LEADING does something different, regarding the order in which tables are scanned:

The LEADING hint instructs the optimizer to use the specified set of tables as the prefix in the execution plan.

So in complex query having 4 tables and if I use hint like `USE_NL(table1 table2 table3 table4)` , which table will be driving table and which one will be inner — User332772, Jan 04 '17 at 09:56
In this case Oracle will try to use all these tables as inner, assuming that there's a table to use as outer — Aleksej, Jan 04 '17 at 10:02
Great one more clarification please, Did it make sense using `/*+ leading (table1 table2 table3 table4 */ with USE_NL(table1 table2 table3 table4)` , How it works when we put multiple tables in Leading hints — User332772, Jan 04 '17 at 10:48

Oracle join using Hint USE_NL USE_HASH

1 Answers1