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

Answer 1

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.