Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 11.0.1 (Français) » SQL Anywhere Server - SQL Usage » Remote Data and Bulk Operations » Accessing remote data » Internal operations


Partial passthrough of the statement

If a statement contains references to multiple servers, or uses SQL features not supported by a remote server, the query is decomposed into simpler parts.


SELECT statements are broken down by removing portions that cannot be passed on and letting SQL Anywhere perform the work. For example, suppose a remote server can not process the ATAN2 function in the following statement:

SELECT a,b,c
WHERE ATAN2( b, 10 ) > 3
AND c = 10;

The statement sent to the remote server would be converted to:

SELECT a,b,c WHERE c = 10;

Then, SQL Anywhere locally applies WHERE ATAN2( b, 10 ) > 3 to the intermediate result set.


When two tables are joined, one table is selected to be the outer table. The outer table is scanned based on the WHERE conditions that apply to it. For every qualifying row found, the other table, known as the inner table is scanned to find a row that matches the join condition.

This same algorithm is used when remote tables are referenced. Since the cost of searching a remote table is usually much higher than a local table (due to network I/O), every effort is made to make the remote table the outermost table in the join.


When a qualifying row is found, if SQL Anywhere cannot pass off an UPDATE or DELETE statement entirely to a remote server, it must change the statement into a table scan containing as much of the original WHERE clause as possible, followed by a positioned UPDATE or DELETE statement that specifies WHERE CURRENT OF cursor-name.

For example, when the function ATAN2 is not supported by a remote server:

SET a = atan2( b, 10 )
WHERE b > 5;

Would be converted to the following:

WHERE  b > 5;

Each time a row is found, SQL Anywhere would calculate the new value of a and issue:

SET a = 'new value'

If a already has a value that equals the new value, a positioned UPDATE would not be necessary, and would not be sent remotely.

To process an UPDATE or DELETE statement that requires a table scan, the remote data source must support the ability to perform a positioned UPDATE or DELETE (WHERE CURRENT OF cursor-name). Some data sources do not support this capability.

Temporary tables cannot be updated

An UPDATE or DELETE cannot be performed if an intermediate temporary table is required. This occurs in queries with ORDER BY and some queries with subqueries.