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