Page History
...
- 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 SQL Query Tuner 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 Oracle and SQL Server query rewrites. For information on other transformations, see Examples of Transformations and SQL Query 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 Oracle and SQL Server query rewrites.
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.
| Anchor | ||||
|---|---|---|---|---|
|
...
query rewrites
The following query rewrites or transformations may be recommended during tuning. The examples below are for Oracle data sources. The implementations for SQL Server data sources are slightly different. These rewrites are available on all platforms except for those noted for Oracle only.
| 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 * from t1 where EXISTS |
select * from t1 where t1.key NOT IN | select * from t1 where NOT EXISTS |
select * from t1 where NOT EXISTS | 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 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 ) |