Partial passthrough of the statement

SELECT

SELECT statements are broken down by removing portions that cannot be passed on and letting SAP Sybase IQ perform the work. For example, suppose a remote server cannot 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, SAP Sybase IQ locally applies WHERE ATAN2( b, 10 ) > 3 to the intermediate result set.

Joins

When a statement contains joins between tables in multiple locations, IQ will attempt to push joins of collocated tables to the server on which they reside. The results of that join will then be joined by IQ with results from other remote tables or local tables. IQ will always prefer to push as much join work as is possible to remote servers. When IQ joins remote tables with local IQ tables, IQ may choose to use any join algorithm it supports.

The choice of algorithm is based on cost estimates. These algorithms can include nested loop, hash, or sort-merge joins.

When a nested loop join is chosen between an IQ and a remote table, every effort is made to make the remote table the outermost table in the join. This is due to the high cost of network I/O that makes look-ups against a remote table usually much higher than a local table.

UPDATE and DELETE

When a qualifying row is found, if SAP Sybase IQ 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, SAP Sybase IQ would calculate the new value of a and execute:

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.