Page History
...
Identifies invalid outer joins and suggests more efficient alternatives.
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 |
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 |
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 |
Mismatched column types
Identify joins type mismatch such as number = character which might suppress use of Index.