SQL Optimizer for SAP ASE 3.8 - Installation Guide

SQL Classification

Based on the query plan, a SQL statement is classified according to the characteristics of the query plan. If the query plan is not retrieved successfully, then these SQL statements are classified as Invalid SQL. A SQL statement can be invalid if the database object it references does not exist, the database user does not have privileges to access it, or the incorrect database and user are used to scan the Job.

All valid SQL statements are further classified as Simple, Complex, or Problematic SQL statements.

Problematic SQL Statements

Problematic SQL statements are potentially under performing SQL statements that should be optimized. Problematic SQL satisfy one of the following criteria:

• The number of tables referenced in the query plan of a SQL statement exceeds the upper limit of the Complex table scan operations range.

• Single full table scan with table size larger than the threshold size.

• Single full table scan in a nested loop with table size larger than the threshold size.

• The number of worktables is greater than or equal to the defined value if the With Worktable checkbox is selected in the Preferences window.

• The number of reformatting.

Complex SQL Statement

Complex SQL statements are complicated SQL statements where there is room for improvement. Complex SQL satisfy one of the following criteria:

• If the number of tables referenced in the query plan of an SQL statement falls into the Complex table scan operations range defined in the Preferences window.

• SQL statements with full index scan.

Simple SQL Statement

Simple SQL statements are direct and straightforward SQL statements with minimal probability of improvement. SQL statements are defined as Simple SQL statements when number of tables referenced in the query plan is less than the lower limit of the Complex table scan operations range; the default value is 1 table.