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

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.

Joins

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.

UPDATE and DELETE

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:

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

Would be converted to the following:

SELECT a,b
FROM t1
WHERE  b > 5;

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

UPDATE t1
SET a = 'new value'
WHERE CURRENT OF CURSOR;

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.