Optimization (1) (Optimization Tab)
The SQL Rewrite (1) page under the SQL Rewrite tab on the Options window allows you to enable or disable certain specific SQL optimization techniques.
Temp Table
|
Temp Table Options |
Description |
|---|---|
|
Temp table generation |
Specify whether to allow temp table generation as SQL is being rewritten. |
|
L |
Specify whether to apply the selected SQL options to the generated temp table SQL. |
|
User temporary table space |
Specify the temporary table space to use when declaring the temp table. If no user temporary table space is specified then the database default user temporary table space will be used. |
|
On commit |
Specify whether to Delete rows or Preserve rows in the temp table after commit. |
Note: To create or modify temporary tables in SQL Rewrite, the logon user needs the following privileges:
-
Connection to DB2 LUW 7 or above
-
USE privilege on the USER TEMPORARY table space or SYSADM or DBADM authority.
View to Nested Table Transformation
This transformation is only applicable if the SQL statement is using a view to access information from the database. When a SQL statement is using a view, the SQL Rewrite optimizes the view's SQL statement along with the original SQL statement. SQL Rewrite inserts the view's SQL into the original SQL statement in every place the view is referenced. Therefore the view's SQL is going to be rewritten along with the original SQL. This is very useful when you want to optimize a SQL statement that is using a poor performing view but you cannot change the view's SQL.
Original SQL
SELECT *
FROM V_DEPT
WHERE DPT_MANAGER = 'SMITH'
Alternative SQL
SELECT *
FROM (SELECT DPT_ID,
DPT_MANAGER
FROM DEPARTMENT)
WHERE DPT_MANAGER = 'SMITH'
|
View to Nested Table Transformation Options |
Description |
|---|---|
|
Transform view to nested table |
Specify whether to transform view to nested table - a subquery using as a table in the FROM clause. |
|
Transformation level |
Specify the recursive level to transform views inside the subquery of a nested table. You can control whether the view's SQL is rewritten with the original SQL statement with the Transform view to nested table option. SQL Rewrite can also transform a view that is being used by another view. You control how many views will be included when the original SQL is rewritten by specifying the transformation levels that you would like to perform during the optimization. |
Query to Derived Table Transformation
This transformation takes a original SQL statement with an IN or EXISTS clause and rewrites it as a derived table.
Original SQL
SELECT *
FROM DEPARTMENT
WHERE DPT_ID IN (SELECT EMP_DEPT
FROM EMPLOYEE)
Alternative SQL
SELECT DEPARTMENT.*
FROM (SELECT DISTINCT EMP_DEPT AS COL1
FROM EMPLOYEE) DERIVEDTABLE0,
DEPARTMENT
WHERE DPT_ID = DERIVEDTABLE0.COL1
|
Query to Derived Table Transformation Option |
Description |
|---|---|
|
Transform query to derived table |
Specify whether to transform the query to a derived table – a subquery used as a table in a FROM clause. |