The database server can process the data stored on remote tables on a given remote server in parallel. However, most commonly the data is communicated serially because the database server allocates a single thread to receive the data from that remote server. The SQL operator REMOTE is used for remote scans. A query plan for a distributed query uses only a single instance for a REMOTE operator. However, different REMOTE operators are used to fetch data from different remote servers and in some cases from multiple tables on a single server. In such cases the REMOTE operators scanning different servers work in parallel. Local operations, such as scan, inserts, updates that are carried out as part of the distributed query, also occur in parallel.
The optimizer does not rewrite nested queries involving remote objects. You can work around this limitation by rewriting the nested subquery in an unnested fashion before submitting it to the database server. Alternatively, you can move the remote data into a temporary table and use that temporary table in the query. If the query plan is still sub-optimal, you might need to run UPDATE STATISTICS on the temporary table before using it in the query. This way, the optimizer can use the distributions to determine the optimal query plan. Additionally, when a NESTED LOOP JOIN operator has a remote path scanned on its inner side, the database server fetches the tuples from the remote scan to a temporary table which is then used for the join. In such cases, temporary space availability must be ensured for the query to be successful.
Home | [ Top of Page | Previous Page | Next Page | Contents | Index ]