Writing efficient SQL Statements as part of SQL Tuning

Developing Efficient SQL Statements: Developing efficient SQL statement is one of the important parts of SQL tuning. You can optimize your SQL while developing something for production which is beyond the system capacilty.

1.       Verifying Optimizer Statistics

2.       Reviewing the Execution Plan

3.       Restructuring the SQL Statements

4.       Restructuring the Indexes

5.       Modifying or Disabling Triggers and Constraints

6.       Restructuring the Data

7.       Maintaining Execution Plans Over Time

8.       Visiting Data as Few Times as Possible

Verifying Optimizer Statistics

The query optimizer uses statistics gathered on tables and indexes when determining the optimal execution plan. If these statistics have not been gathered, or if the statistics are no longer representative of the data stored within the database, then the optimizer does not have sufficient information to generate the best plan.

Reviewing the Execution Plan

When tuning or writing a SQL statement in an OLTP environment, the goal is to drive from the table that has the most selective filter. This means that there are fewer rows passed to the next step. If the next step is a join, then this means that fewer rows are joined. Check to see whether the access paths are optimal. While examining the optimizer execution plan, keep in mind the followings:

1.       The plan is such that the driving table has the best filter.

2.       The join order in each step means that the fewest number of rows are being returned to the next step (that is, the join order should reflect, where possible, going to the best not-yet-used filters).

3.       The join method is appropriate for the number of rows being returned. For example, nested loop joins through indexes may not be optimal when many rows are being returned.

4.       Views are used efficiently. Look at the SELECT list to see whether access to the view is necessary.

5.       There are any unintentional Cartesian products (even with small tables).

6.       Each table is being accessed efficiently.

Restructuring the SQL Statements

1.       Compose Predicates Using AND and =: To improve SQL efficiency, use equijoins whenever possible. Statements that perform equijoins on untransformed column values are the easiest to tune.

2.       Avoid Transformed Columns in the WHERE Clause:

 Use untransformed column values. For example, use:  WHERE a.order_no = b.order_no

Rather than: WHERE TO_NUMBER (SUBSTR(a.order_no, INSTR(b.order_no, '.') - 1))

               = TO_NUMBER (SUBSTR(a.order_no, INSTR(b.order_no, '.') - 1))

Do not use SQL functions in predicate clauses or WHERE clauses. Any expression using a column, such as a function having the column as its argument, causes the optimizer to ignore the possibility of using an index on that column, even a unique index, unless there is a function-based index defined that the database can use.

3.       Write Separate SQL Statements for Specific Tasks: SQL is not a procedural language. Using one piece of SQL to do many different things usually results in a less-than-optimal result for each task. If you want SQL to accomplish different things, then write various statements, rather than writing one statement to do different things depending on the parameters you give it.

4.       Controlling the Access Path and Join Order with Hints: You can influence the optimizer's choices by setting the optimizer approach and goal, and by gathering representative statistics for the query optimizer. Sometimes, the application designer, who has more information about a particular application's data than is available to the optimizer, can choose a more effective way to execute a SQL statement. You can use hints in SQL statements to instruct the optimizer about how the statement should be executed.

Hints, such as /*+FULL */ control access paths. For example:

SELECT /*+ FULL(e) */ e.last_name

  FROM employees e

 WHERE e.job_id = 'CLERK';

5.       Use Caution When Managing Views  &  When Joining Complex Views

6.       Use Caution When Performing Outer Joins to Views

7.       Store Intermediate Results: Storing intermediate results in staging tables could improve application performance.

Restructuring the Indexes:

This can involve the following:

1.       Remove nonselective indexes to speed the DML.

2.       Index performance-critical access paths.

3.       Consider reordering columns in existing concatenated indexes.

4.       Add columns to the index to improve selectivity.

Modifying or Disabling Triggers and Constraints

Using triggers consumes system resources. If you use too many triggers, then you can find that performance is adversely affected and you might need to modify or disable them.

Restructuring the Data

After restructuring the indexes and the statement, consider restructuring the data:

1.       Introduce derived values. Avoid GROUP BY in response-critical code.

2.       Review your data design. Change the design of your system if it can improve performance.

3.       Consider partitioning, if appropriate

Maintaining Execution Plans Over Time

You can maintain the existing execution plan of SQL statements over time either using stored statistics or SQL plan baselines. Storing optimizer statistics for tables will apply to all SQL statements that refer to those tables. Storing an execution plan as a SQL plan baseline maintains the plan for set of SQL statements. If both statistics and a SQL plan baseline are available for a SQL statement, the optimizer will first use a cost-based search method to build a best-cost plan, then it will try to find a matching plan in the SQL plan baseline. If a match is found, the optimizer will proceed using this plan.

Visiting Data as Few Times as Possible

Applications should try to access each row only once. This reduces network traffic and reduces database load. Consider doing the following:

1.       Combine Multiples Scans with CASE Statements

2.       Use DML with RETURNING Clause

3.       Modify All the Data Needed in One Statement

The End