REWRITE( select-statement [, 'ANSI' ] )

LONG VARCHAR

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

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 are applied:

• Optimizations performed during query processing
• extended_join_syntax option
• Transact-SQL outer joins (*= or =*)
• Key joins
• Natural joins
• Duplicate correlation names in joins (star 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';