1. 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 

Quick Fixes Demo File 

Comments