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