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.
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
SELECT /*+ index(cust_table_last_name_indx) */
distinct author_names FROM devx_author_names WHERE
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;
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.