Page History
Cartesian Product Elimination
: Detects Cartesian Joins and propose corrections based on analysis of statement, for example suggesting dept.deptno = emp.deptno if emp and dept had no join criteria.
Expression Transformation
: Identifies actions on predicates that might suppress index usage such as "where empid + 1 = 1 ", should be "where empid=0"
Invalid Outer Join
: Identifies invalid outer joins and suggests more efficient alternatives. BeforeAfter
| Before | After |
|---|---|
| SELECT * FROM employee e, customer c WHERE e.employee_id = c.salesperson_id ( + ) AND c.state = 'CA' | SELECT * FROM employee e, customer c WHERE e.employee_id = c.salesperson_id ( + ) AND c.state( + ) = 'CA' |
Transitivity
...
| Before |
|---|
...
| After |
|---|
| SELECT * FROM item i, product p, price pr WHERE i.product_id = p.product_id AND |
...
| p.product_id = pr.product_id | SELECT * FROM item i, product p, price pr |
...
p.product_id = pr.product_id |
...
id |
Move Expression to WHERE
...
Clause
| Before |
|---|
...
| After | |
|---|---|
| SELECT col_a, SUM(col_b) FROM table_a GROUP BY col_a HAVING col_a > 100 | SELECT col_a, SUM(col_b) FROM table_a WHERE col_a > 100 GROUP BY col_ |
...
| a |
NULL
...
Column
| Before |
|---|
...
| After | |
|---|---|
| SELECT * FROM employee WHERE manager_id != NULL | SELECT * FROM employee |
...
NULL |
Push
...
Subquery
| Before |
|---|
...
| After |
|---|
| SELECT * |
...
| FROM employee WHERE employee_id = (SELECT MAX(salary) FROM employee) | SELECT employee.* |
...
FROM employee, (SELECT DISTINCT MAX(salary) |
...
col1 FROM employee) t1 |
...
col1 |
Mismatched column types
Identify : identify joins type mismatch such as number = character which might suppress use of Index.