The query plan of a distributed query

You can display the chosen query plan of a distributed query. The information displayed for a distributed join differs from information displayed for a local join.

The following figure shows the chosen query plan for the distributed query.
Figure 1. Selected Output of SET EXPLAIN ALL for Distributed Query, Part 3
select l.customer_num, l.lname,,, r.call_dtime, r.call_descr
       from customer l,  vjp_stores@gilroy:cust_calls r 
        where l.customer_num = r.customer_num

 Estimated Cost: 9 
Estimated # of Rows Returned: 7

  1) informix.r:  REMOTE PATH 

  2) informix.l: INDEX PATH

    (1) Index Keys: customer_num   (Serial, fragments: ALL)
        Lower Index Filter: informix.l.customer_num = informix.r.customer_num

The following table shows the main differences between the chosen query plans for the distributed join and the local join.

Output Line in Figure 1 for Distributed Query Output Line in Figure 1 for Local-Only Query Description of Difference
vjp_stores@gilroy: virginia.cust_calls informix.cust_calls The remote table name is prefaced with the database and server names.
Estimated Cost: 9 Estimated Cost: 7 The optimizer estimates a higher cost for the distributed query.
informix.r: REMOTE PATH informix.r: SEQUENTIAL SCAN The optimizer chose to keep the outer, remote cust_calls table at the remote site.
select x0.call_dtime,x0.call_descr,x0. customer_num from vjp_stores:”virginia”.cust_ calls x0   The SQL statement that the local database server sends to the remote site. The remote site reoptimizes this statement to choose the actual plan.

Copyright© 2018 HCL Technologies Limited