ASA SQL User's Guide
  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 

SELECT statements are broken down by removing portions that cannot be passed on and letting Adaptive Server Anywhere perform the feature. For example, let's say 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

Locally, Adaptive Server Anywhere would apply "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 

If Adaptive Server 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 positioned UPDATE or DELETE "where current of cursor" when a qualifying row is found.

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, Adaptive Server 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.

In order to process an UPDATE or DELETE that requires a table scan, the remote data source must support the ability to perform a positioned UPDATE or DELETE ("where current of cursor"). Some data sources do not support this capability.