Improving Efficiency of Multitable Queries

Each RDBMS has its own algorithms to translate an SQL query into a set of binary instructions that could be further interpreted by the RDBMS engine to create the execution plan for the query. This plan indicates the order in which tables are joined, the WHERE clause conditions that are applied, whether the indexes are used, and so on.

The order of joins and WHERE clause conditions can seriously affect query performance. For example, assume a goal to join tables CUSTOMER and ORDER_HEADER and then get all orders for customer FAIR PARK GARDENS only. It is obvious that the operation will take less time if we limit the row set from the CUSTOMER table first (WHERE customer.cust_name_s = 'FAIR PARK GARDENS') and then perform the join of the resulting set with the ORDER_HEADER table, because in that case you only perform the join for one row. Also, indexes on both CUST_NAME_S and the columns participating in the join operation could be useful.

Cross-References 

See Chapter 4 for index creation recommendations.

All modern RDBMS have special mechanisms called optimizers that create execution plans for queries based on certain information accumulated in the information schema. That — in theory — makes complex query writing rules (that are different for all vendors and are beyond the scope of this book) obsolete. SQL developers no longer have to specify tables and columns in a certain order in FROM and WHERE clauses of the SELECT statement to achieve acceptable query performance — the optimizer will do the job.

The words "in theory" were not used here by accident. Sometimes (usually for complex queries that join large number of tables) optimizers simply cannot find the "optimal" way to parse a query. In situations like that, you can give the query a hint, using a predefined vendor-specific set of keywords that overrides the optimizer's algorithm and perform actions in the order specified by the programmer. In general, hints can tell RDBMS which tables to join first, whether or not to use an index or to perform full table scan, and so on.

Note 

Hints are instructions in the SQL code that direct the RDBMS optimizer to use specific methods when creating the execution plan.

You know from Chapter 4 that you would not always benefit from using indexes. For example, the STATUS table is very small, so the full table scan does a better job. The following syntax overrides the index on the STATUS_ID_N column and forces the optimizer to perform the full table scan (using Oracle syntax):

SELECT /*+ FULL(status)*/ status_id_n,                                                                                status_code_s,                            status_desc_s FROM                      status