Techniques - Which can use to influence the SQL compiler's choice of execution plan

Below techniques which can influence the SQL compiler's choice of execution plan.

1. Performing Update Statistics
2. Creating or Modifying Indexes
3. Changing the Query Text
4. Using SQL CONTROL Statements

1.Performing Update Statistics

The SQL compiler uses table statistics to help estimate the cost of potential execution plan candidates. (By the way, don't confuse table statistics with the execution statistics we covered above. 

Table statistics tell us about the distribution of data values in the columns of a table.) Table statistics are not maintained automatically. You have to tell NonStop SQL to re-calculate them on an on-going basis. You use the UPDATE STATISTICS command for this. 

Not only is it important to keep table statistics up to date, you must also generate the right statistics. In general, it is a good idea to update statistics for all key columns. If you are tuning a poorly performing query, make sure there are statistics for the columns involved in the filter conditions. For MX, be sure to include multicolumn statistics when a table filter refers to more than one column.

2. Creating or Modifying Indexes

Adding an index is often the best way to improve query performance.

However, you need to consider the additional cost that will be incurred for INSERT, UPDATE, and DELETE (IUD) operations against the table. An insert or delete against the table will also cause an insert or delete against all of its indexes. An update will cause an index to be updated if the index includes any columns that are changed by the update.

For a table that is not too volatile, creating an index to support an important query is often worth the extra cost incurred by IUD operations.

You might consider creating an index to:
• Support a filter, avoiding a full table scan.
• Support a join, if you suspect a nested join supported by an index will perform better than a hash join.
• Support an aggregate, enabling the use of an index only scan instead of a table scan.

You might consider adding columns to an index to avoid a table scan. If an index contains all of the columns required by a query an index-only scan can be used, this is almost always more efficient than a table scan (since index rows tend to be much smaller than
table rows).

3. Changing the Query Text

It is often possible to write a query in more than one way, yet still 

get the same rows in the result. Changing the way that a query is 

written can allow the SQL compiler to choose a better execution plan.

Here is a trivial example to illustrate the point:

Consider this query:

SELECT * FROM DEPT D 

WHERE D.DEPTNUM IN (1000, 3000, 4000)

The DEPT table has a primary (cluster) key of DEPTNUM.

The compiler generates a plan that performs a full table scan on

DEPT, and applies the filter predicate to each row.

If we re-write the query like this:

SELECT * FROM DEPT D

WHERE D.DEPTNUM IN (1000, 3000, 4000) AND

D.DEPTNUM >= 1000 AND

D.DEPTNUM <= 4000

This returns the same results, but the compiler generates a 

plan that does a subset scan that only reads the rows between 

DEPTNUM 1000 and 4000.

4. Using SQL Control Statements

You can use SQL CONTROL statements to influence the behavior of the SQL compiler. You can use them to directly control join order, join methods, access paths, and parallelism.

There are also less direct options like specifying selectivity estimates for example.

The MX CONTROL QUERY DEFAULT (CQD) statement supports a large number of options, including a number that are useful in query tuning.

The JOIN_ORDER_BY_USER attribute allows you to control the join order used in the execution plan. The tables are joined in the order in which they appear in the FROM

clause.

The HASH_JOINS, NESTED_JOINS, and MERGE_JOINS attributes can be used to enable or disable the corresponding join methods. Unfortunately, they apply to the query as a

whole, and not to individual tables.

The INTERACTIVE_ACCESS and INDEX_ELIMINATION attributes can be used to encourage or discourage the use of indexes.

The ATTEMPT_ESP_PARALLELISM attribute controls the generation of parallel plans.

The MX CONTROL QUERY SHAPE (CQS) statement provides a means to force the use of a particular execution plan. However, its use is generally discouraged. A change to

the database or the SQL compiler may make the forced plan inoperable. CQS statements are hard to write, especially from scratch. The best approach is to use CQD statements (including

the JON_ORDER_BY_USER option) to get a plan that is as close as possible to the desired plan. Then use the SHOWSHAPE statement to generate the CQS statement that reflects the current plan. Finally, make adjustments to the generated CQS statement.

The MP CONTROL TABLE statement provides direct control over the join order, join methods, and access paths used in the execution plan. The CONTROL EXECUTOR

statement allows for the selection of parallel or serial plans.

Using CONTROL statements is a two-edged sword. They give you a way to influence the execution plan produced by the compiler, but at the same time they may prevent the compiler

from finding a better plan.