ASA SQL Reference
  SQL Functions
    Alphabetical list of functions

REWRITE function [Miscellaneous]

Function 

Returns a rewritten SELECT, UPDATE, or DELETE statement.

Syntax 

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

Usage 

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 Adaptive Server Anywhere has rewritten the conditions in the statement's WHERE, ON, and HAVING clauses, and then determine whether or not 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:

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

Standards and compatibility 

• SQL/92    Vendor extension.

• SQL/99    Vendor extension.

• Sybase    Not supported by Adaptive Server Enterprise.

See also 

Semantic query transformations

EXTENDED_JOIN_SYNTAX option [database]

Transact-SQL outer joins (*= or =*)

Key joins

Natural joins

Duplicate correlation names in joins (star joins)

Example 

In the following example, two rewrite optimizations are performed on a query. The first is the unnesting of the subquery into a join between the employee and sales_order tables. The second optimization simplifies the query by eliminating the primary key - foreign key join between employee and sales_order. Part of this rewrite optimization is to replace the join predicate e.emp_id=s.sales_rep with the predicate s.sales_rep IS NOT NULL.

SELECT REWRITE( 'SELECT s.id, s.order_date
    FROM sales_order s
    WHERE  EXISTS(SELECT *
       FROM employee e
          WHERE e.emp_id = s.sales_rep)' ) FROM dummy

The query returns a single column result set containing the rewritten query:

'SELECT s.id, s.order_date FROM sales_order s WHERE s.sales_rep IS NOT NULL'

The next example of REWRITE uses the ANSI argument.

SELECT REWRITE( 'SELECT DISTINCT s.id, s.order_date, e.emp_fname, e.emp_id
    FROM sales_order s, employee e
          WHERE e.emp_id *= s.sales_rep', '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.order_date, e.emp_id, e.emp_fname
 FROM employee as e
 LEFT OUTER JOIN  sales_order as s
 ON e.emp_id = s.sales_rep'