SQL Optimizer for SAP ASE 3.9 - User Guide

SQL Scanner Functions

Below is a list of available functions within the SQL Scanner window.

Related Topics

SQL Scanner Overview

SQL Scanner Window

SQL Conversion

SQL Conversion Overview

In order to render the SQL statement as a valid standalone SQL a number of conversions maybe applied to the SQL statement.

Note: If a conversion is applied, it may be necessary to reverse the changes after optimization when an alternative SQL is pasted back to the original source code.

Related Topic

SQL Scanner Overview

Trigger Conversion

During a trigger operation, two logical tables store deleted and inserted records. The deleted and inserted logical tables cannot be referenced outside the trigger body. Therefore to be able to optimize a SQL statement used in a trigger two temporary tables are used to simulate the inserted and deleted tables.

For example:

Original SQL statement

INSERT INTO EMP_SMALL (EMP_ID,

EMP_NAME,                        

EMP_SALARY)                        

SELECT A.EMP_ID,

A.EMP_NAME,        

B.EMP_SALARY        

FROM EMPLOYEE A,   

Inserted B       

WHERE A.EMP_ID = B.EMP_ID 

After conversion

SELECT *

INTO #inserted_simulation_table   

FROM dbo.EMPLOYEE  

WHERE 1 = 2 

INSERT INTO EMP_SMALL (EMP_ID,

EMP_NAME,                        

EMP_SALARY)                        

SELECT A.EMP_ID,

A.EMP_NAME,        

B.EMP_SALARY        

FROM EMPLOYEE A,   

#inserted_simulation_table B       

WHERE A.EMP_ID = B.EMP_ID 

Related Topic

SQL Conversion Overview