The ordered hint instructs the optimizer to join the tables in the order in which they appear in the FROM clause. Even though the optimizer picks a different join order based on the computed statistics, this hint forces the optimizer to overwrite its choice of join order. The syntax is
/*+ ORDERED */
Use nested loop joins when the subset of data between the two joining tables is small. Because nested loop joins fetch the data as soon as possible, they are the preferred joins when either the data doesn't need to be sorted or you need the queries to return quickly.
For the previous tables A and B example, the inner table (second table) would be table A:
SELECT /*+ ORDERED USE_NL(A) */
FROM B, A
Where A.column1 = B.column2
This hint is best suited for joining tables that have a large subset of data. A hash join offers better throughput and is best suited for sorting and ordering queries. When the size of the tables is large, the hash table size becomes pretty large and it requires more CPU and memory. The syntax for the hint is
/*+ USE_HASH (table_name) */.
The merge hint requires that both inputs be sorted on the merge columns, which are defined by the equality (WHERE) clauses of the join predicate. Because each input is sorted, the merge join operator gets a row from each input and compares them. For example, for inner join operations, the rows are returned if they are equal. If they are not equal, whichever row has the lower value is discarded and another row is obtained from that input. This process repeats until all rows have been processed. The syntax for this hint is
/*+ USE_MERGE(table_name) */.
Use the SQL Tuning that Fits
Before you start tuning the query, understand the tables and their data and apply the hints. The hints you use in the query must be tailored to each query's requirement. No one solution fits all in SQL tuning. The best bet would be to try different hints and time the queries. Select the best hint and do a cost analysis on the hint to make sure that you don't overuse the server resources.
With this knowledge of SQL tuning, you are ready to build your time-critical applications.