As you add SQL source to the Overview tab of a tuning job, the supported DML statements are automatically parsed out and a numbered statement record for each statement is added to the Overview tab.

Cases generated from tuning candidates are alternative forms of the original statement that have been optimized or otherwise "fixed" by the tuning function. Once you have executed a tuning job, tuning automatically generates all SQL optimizer hint-based variations that can be applied to the statement: If you change the schema of a statement

Hint-based cases and the transformation-based cases are a special case of the statement records added to the Overview tab as you add candidates to a tuning job. With the exception of the Text, Source, and Index Analysis fields, cases are identical to the standard statement record. Similarly, execution, statistics collection, and other options available for basic statement records are available for individual cases.

Once cases have been generated, if you have the required permissions on the specified data source, you can apply the changes suggested by hint and transformation based cases in the Overview table.

DB2, Oracle, SQL Server, and Sybase query rewrites

The following query rewrites or transformations may be recommended during tuning. The examples below are for Oracle data sources. The implementations for DB2, SQL Server, and Sybase data sources are slightly different. These rewrites are available on all four platforms except for those noted for ORACLE and DB2 only.

BeforeAfter
select * from t1 where EXISTS
(select null from t2 where t2.key=t1.key);
select * from t1 where t1.key IN
(select t2.key from t2);
select * from t1 where NOT EXISTS
(select null from t2 where t2.key=t1.key);
select * from t1 where t1.key NOT IN
(select t2.key from t2 where t2.key is not null);

select * from t1 where t1.key IN
(select t2.key from t2);

select * from t1 where EXISTS
(select null from t2 where t2.key = t1.key);

select * from t1 where t1.key NOT IN
(select t2.key from t2 where t2.key is not null);

select * from t1 where NOT EXISTS
(select null from t2 where t2.key = t1.key);

select * from t1 where NOT EXISTS
(select null from t2 where t2.key = t1.key);

select t1.* from t1, t2 where t1.key = t2.key(+) and t2.key is null

select * from t1 where t1.key
NOT IN
(select t2.key from t2 where t2.key is not null);

select t1.* from t1, t2 where t1.key = t2.key(+) and t2.key is null;

select column BETWEEN X AND Yselect (column <= X AND column >= Y) 
select column NOT BETWEEN X AND Yselect (column < X AND column > Y) 
select (column<= X AND column >= Y)select column BETWEEN X AND Y
select (column < X AND column > Y)select column NOT BETWEEN X AND Y

select t1.* from t1, t2 where t1.key = t2.key and t2.col = 10;

select t1.* from t1,

(select * from t2 where t2.col = 10) inline_alias where t1.key= inline_alias.key;

For DB2 and Oracle only

select t2.* from t1, t2 where t1.key = t2.key and t1.col is null

select * from t2 where t2.key IN

(select t1.key from t1 where t1.col is null )

IDERA |  Products | Purchase | Support |  Community |  Resources |  About Us  | Legal