Intrapartition parallelism with queries that reference nicknames - examples of access plans

You can use the Db2® Explain facility to view the access plan that the optimizer uses during query processing. The following examples show how the optimizer accesses nickname data in an intrapartition parallelism environment.

Example 1: Without parallelism support

In this example, the federated server processes the join of the local table, ORDERS, and nickname, ITEMS, serially. No intrapartition parallelism is used.

SELECT * 
FROM ORDERS A, ITEMS B 
WHERE A.ID1 = B.ID1 AND B.ITEM = 3

	
             RETURN 
            (   1) 
               |
            HSJOIN 
            (   2) 
          /----+---\
     TBSCAN         SHIP   
     (   3)        (   4) 
       |             |
   TABLE: NEWTON  NICKNM: NEWTON   
     ORDERS         ITEMS

Example 2: With parallelism support

In this example of a join, the query can run faster by having the local table read in parallel before the serial join with the nickname. The LTQ operator indicates where parallelism is introduced into the plan.

SELECT * 
FROM ORDERS A, ITEMS B 
WHERE A.ID1 = B.ID1 AND B.ITEM = 3

	
            RETURN 
            (   1) 
               |
            HSJOIN 
            (   2) 
          /----+---\
      LTQ           SHIP   
     (   3)        (   5) 
        |             |
     TBSCAN   NICKNM: NEWTON   
     (   4)        ITEMS 
        |
   TABLE: NEWTON   
    ORDERS

Example 3: Intrapartition parallelism with aggregation

In this example, the database aggregates the local table data in parallel in the partition, improving the execution of the aggregation. The join of the local table and the nickname occurs serially.

SELECT * 
FROM ITEMS A 
WHERE ID = 
   (SELECT MAX(ID) 
   FROM ORDERS 
   WHERE NUMBER = 10) 

	
            RETURN 
            (   1) 
               |
            NLJOIN 
            (   2) 
           /----+---\
      GRPBY         SHIP   
     (   3)        (   7) 
        |             |
      LTQ      NICKNM: NEWTON   
     (   4)        ITEMS    
        |
        1 
     GRPBY  
     (   5) 
        |
      TBSCAN 
     (   6) 
        |
  TABLE: NEWTON   
     ORDERS