You can use the REWRITE function without the ANSI argument to help understand how the optimizer generated the access plan
for a given query. In particular, you can find how SQL Anywhere has rewritten the conditions in the statement's WHERE, ON,
and HAVING clauses, and then determine if applicable indexes exist that can be exploited to improve the request's execution
The statement that is returned by REWRITE may not match the semantics of the original statement. This is because several rewrite
optimizations introduce internal mechanisms that cannot be translated directly into SQL. For example, the server's use of
row identifiers to perform duplicate elimination cannot be translated into SQL.
The rewritten query from the REWRITE function is not intended to be executable. It is a tool for analyzing performance issues
by showing what gets passed to the optimizer after the rewrite phase.
There are some rewrite optimizations that are not reflected in the output of REWRITE. They include LIKE optimization, optimization
for minimum or maximum functions, upper/lower elimination, and predicate subsumption.
If ANSI is specified, REWRITE returns the ANSI equivalent to the statement. In this case, only the following rewrite optimizations
Transact-SQL outer joins are rewritten as ANSI SQL outer joins.
Duplicate correlation names are eliminated.
KEY and NATURAL joins are rewritten as ANSI SQL joins.
In the following example, two rewrite optimizations are performed on a query. The first is the un-nesting of the subquery
into a join between the Employees and SalesOrders tables. The second optimization simplifies the query by eliminating the
primary key - foreign key join between Employees and SalesOrders. Part of this rewrite optimization is to replace the join
predicate e.EmployeeID=s.SalesRepresentative with the predicate s.SalesRepresentative IS NOT NULL.
SELECT REWRITE( 'SELECT s.ID, s.OrderDate
FROM SalesOrders s
WHERE EXISTS ( SELECT *
FROM Employees e
WHERE e.EmployeeID = s.SalesRepresentative)' ) FROM dummy;
The query returns a single column result set containing the rewritten query:
'SELECT s.ID, s.OrderDate FROM SalesOrders s WHERE s.SalesRepresentative IS NOT NULL'
The next example of REWRITE uses the ANSI argument.
SELECT REWRITE( 'SELECT DISTINCT s.ID, s.OrderDate, e.GivenName, e.EmployeeID
FROM SalesOrders s, Employees e
WHERE e.EmployeeID *= s.SalesRepresentative', 'ANSI' ) FROM dummy;
The result is the ANSI equivalent of the statement. In this case, the Transact-SQL outer join is converted to an ANSI outer
join. The query returns a single column result set (broken into separate lines for readability):
'SELECT DISTINCT s.ID, s.OrderDate, e.GivenName, e.EmployeeID
FROM Employees as e
LEFT OUTER JOIN SalesOrders as s
ON e.EmployeeID = s.SalesRepresentative';