Browse DevX
Sign up for e-mail newsletters from DevX


Some "Hints" for Mastering SQL Tuning : Page 2

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

Optimizer Modes
The optimizer goal determines the overall approach the optimizer takes in determining an execution plan. The following statement returns the rows as soon as it finds a few:

SELECT /*+ FIRST_ROWS */ distinct customer_name FROM

However, the following query waits until all the rows are retrieved and sorted before returning them to the client:

SELECT /*+ ALL_ROWS */ distinct customer_name FROM
customer ORDER BY customer_name

You can set the optimizer modes at the session level or at the query level. PL/SQL procedures that run multiple queries would need the session-level setting.

Index Hint
Indexes play a very important role in SQL tuning. Indexes allow the table data to be indexed and organized, which in turn enables faster retrieval. Merely creating an index does not speed up the query execution. You must make sure that the query's execution plan uses the hinted index. In the following query, when the optimizer uses the index hint, it will be forced to use the specified index for the search on last_name:

SELECT /*+ index(cust_table_last_name_indx) */
distinct author_names FROM devx_author_names WHERE
author_last_name ='DON%'

When you do a explain plan on this query, you will see the optimizer using this index. You can also instruct the optimizer to choose between a subset of indexes using /*+ index( indx1, indx2) */.

Note: Creating the index does not speed up the query execution. The index needs to be analyzed. The syntax for analyzing the index is:

analyze index <index_name> compute statistics;

Join Queries
If the query involves joining two or more tables, the database server provides various hints to speed up the queries. A typical join query involves performing a search of the inner table for each row found in the outer table.

For example, suppose table A has 100 rows and table B has 1,000 rows. Logically, the query would run faster if for each row from table B it did a lookup for a matching row in table A. The opposite join could take as much as 10 times longer to execute.

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