Editor
Editor supports
- code assist
- code complete (Type Ahead and Name completion)
- code templates (Templates for creation of tables, procedures, etc)
- hyper link
- semantic validation
- object hovering
- code formatter
- code correction ( transformations),
- object indexing
- SQL Project Explorer
Examples of Transformations and SQL Query Rewrites:
- Cartesian Product Elimination -
- detect 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
- identify actions on predicates that might supress index usage such as "where empid + 1 = 1 ", should be "where empid=0"
- Invalid Outer Join
- before
- SELECT * FROM employee e , customer c
- WHERE e.employee_id = c.salesperson_id ( +)
- AND c.state = 'CA'
- after
- SELECT * FROM employee e,customer c
- WHERE e.employee_id = c.salesperson_id ( +)
- AND c.state( +) = 'CA'
- Transitivity
- Before
- SELECT * FROM item i, product p, price pr
- WHERE i.product_id = p.product_id AND p.product_id = pr.product_id
- After
- 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
- Before
- SELECT col_a, SUM(col_b) FROM table_a
- GROUP BY col_a HAVING col_a > 100
- After
- SELECT col_a, SUM(col_b) FROM table_a
- WHERE col_a > 100 GROUP BY col_a
- NULL Column
- Before
- SELECT * FROM employee
- WHERE manager_id != NULL
- After
- SELECT * FROM employee
- WHERE manager_id IS NUL
- Push Subquery
- Before
- SELECT *
- FROM employee
- WHERE employee_id = (SELECT MAX(salary) FROM employee)
- After
- 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
- Mismatched column types
Quick Fixes Demo File