Page History

Versions Compared

changes.mady.by.user Nadja Pollard

Saved on May 09, 2018

compared with

changes.mady.by.user Nadja Pollard

Saved on Nov 03, 2021

...

All SQL Optimizer hint-based variations that can be applied to a statement.
A transformation-based case, if any of the eight common quick fixes can be applied to an SQL statement. This feature leverages the DB Optimizer Code Quality Check functionality. See Understanding Code Quality Checks for more information on the eight quick fixes. A transformation case, in turn, has its own set of SQL Optimizer hint cases. For information on query rewrites, see DB2, Oracle, SQL Server, Sybase Query Rewrites. For information on other transformations, see Examples of Transformations transformations and SQL Query Rewritesquery rewrites.
SQL Query Rewrites may be suggested when tuning. For example, a recommended rewrite for EXISTS may be IN. For information on query rewrites, see DB2, Oracle, SQL Server, Sybase Query Rewrites.

...

Before	After
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 Y	select (column <= X AND column >= Y)
select column NOT BETWEEN X AND Y	select (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 )