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
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:
BeforeAfter
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
WHERE i.product_id = p.product_id AND
p.product_id = pr.product_id
AND i.product_id = pr.product_id
Move Expression to WHERE Clause
BeforeAfter
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
BeforeAfter
SELECT * FROM employee
WHERE manager_id != NULL
SELECT * FROM employee
WHERE manager_id IS NUL
Push Subquery
BeforeAfter
SELECT *
FROM employee
WHERE employee_id = (SELECT MAX(salary) FROM employee)
SELECT employee.*
FROM employee, (SELECT DISTINCT MAX(salary)
col1 FROM employee) t1
WHERE employee_id = t1.col1
Mismatched column types: identify joins type mismatch such as number = character which might suppress use of Index.