Browse DevX
Sign up for e-mail newsletters from DevX


Some "Hints" for Mastering SQL Tuning : Page 3

Help your database optimize SQL queries by including hints in your SQL statements. Used correctly, hints can reduce the running time of your queries significantly.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

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.

Raghu Donepudi, an independent contractor, currently is a technical manager for a federal government agency. He has a master's degree in Computer Science from Lamar University in Texas. He is a Sun-certified Java developer and the author of many software design techniques.
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date