Zigzag join access plan examples
The following examples show the db2exfmt command output for different access plans possible with a zigzag join.
These examples use a star-shaped query with DAILY_SALES
as the fact table, and CUSTOMER and PERIOD as the dimension tables.
The query asks for the total quantity of the products sold in the
month of March 1996 to the customers in age-level 7, such that the
results are shown aggregated by the income level description of the
customer.
select income_level_desc, sum(quantity_sold) "Quantity"
from daily_sales s, customer c, period p
where calendar_date between '1996-03-01' and '1996-03-31'
and p.perkey = s.perkey
and s.custkey = c.custkey
and age_level = 7
group by income_level_desc;Three
types of fact table access plans are possible with a zigzag join.
- An index scan-fetch plan: In this plan, the index scan accesses the index over the fact table to retrieve RIDs from the fact table matching the input probe values. These fact table RIDs are then used to fetch the necessary fact table data from the fact table. Any dimension table payload columns are then retrieved from the dimension table and the result row is output by the zigzag join operator.
- A single probe list-prefetch plan: In this plan, a list prefetch plan is executed for every probe row from the combination of dimension tables and snowflakes. The index scan over the fact table finds fact table RIDs matching the input probe values. The SORT, RIDSCAN, and FETCH operators sort RIDs according to data page identifiers and list prefetchers start to get the fact table data. Any dimension table payload columns are then retrieved from the dimension tables and the result row is output by the zigzag join operator.
- An all-probes list-prefetch plan: In this plan, the index scan accesses the fact table index for all the probes from the combination of dimension tables and snowflakes. All such matching RIDs are sorted together in the order of fact table data pages and the list prefetchers start to retrieve the necessary data from the fact table. This method of sorting all RIDs helps achieve better prefetching. These queries will include two separate ZZJOIN operators, one of which represents a back-join between the fact table and the dimension tables.
Example: Index scan-fetch access of the fact table
2.6623e+06
ZZJOIN
( 5)
7620.42
5.37556
+------------------+------------------+
292.2 40000 0.227781
TBSCAN TBSCAN FETCH
( 6) ( 9) ( 13)
56.2251 7596.78 11.8222
1 2.92 1.22778
| | /---+----\
292.2 40000 0.227781 6.65576e+08
TEMP TEMP IXSCAN TABLE: POPS
( 7) ( 10) ( 14) DAILY_SALES
30.4233 4235.52 9.93701 Q3
1 2.92 1
| | |
292.2 40000 6.65576e+08
IXSCAN FETCH INDEX: POPS
( 8) ( 11) PER_CUST_ST_PROMO
29.9655 4235.07 Q3
1 2.92
| /---+----\
2922 40000 1e+06
INDEX: POPS IXSCAN TABLE: POPS
PERX1 ( 12) CUSTOMER
Q1 2763.52 Q2
1
|
1e+06
INDEX: POPS
CUSTX1
Q2
The TBSCAN(6) and TBSCAN(9) operators show the following information:
IS_TEMP_INDEX : True/False
The scan builds an index over the temp for random access of the temp.
(If the flag is 'true')
The scan builds a fast integer sort structure for random access of the temp.
(If the flag is 'false') The TBSCAN(6) and
TBSCAN(9) operators show the information regarding the feedback predicates
applied to the operators, in the form of start-stop key conditions.
Predicates:
----------
5) Start Key Predicate,
Comparison Operator: Equal (=)
Subquery Input Required: No
Filter Factor: 0.000342231
Predicate Text:
--------------
(Q1.PERKEY = Q3.PERKEY)
5) Stop Key Predicate,
Comparison Operator: Equal (=)
Subquery Input Required: No
Filter Factor: 0.000342231
Predicate Text:
--------------
(Q1.PERKEY = Q3.PERKEY)
The ZZJOIN(5) operator shows the collection of
all the feedback predicates used in the processing of zigzag join.
Predicates:
----------
4) Feedback Predicate used in Join,
Comparison Operator: Equal (=)
Subquery Input Required: No
Filter Factor: 1e-06
Predicate Text:
--------------
(Q3.CUSTKEY = Q2.CUSTKEY)
5) Feedback Predicate used in Join,
Comparison Operator: Equal (=)
Subquery Input Required: No
Filter Factor: 0.000342231
Predicate Text:
--------------
(Q1.PERKEY = Q3.PERKEY)Example: Single probe list-prefetch access of the fact table
2.6623e+06
ZZJOIN
( 5)
1.10517e+06
5.37556
+------------------+---+-----------------------+
292.2 40000 0.227781
TBSCAN TBSCAN FETCH
( 6) ( 9) ( 13)
56.2251 7596.78 548787
1 2.92 1.22778
| | /----+----\
292.2 40000 0.227781 6.65576e+08
TEMP TEMP RIDSCN TABLE: POPS
( 7) ( 10) ( 14) DAILY_SALES
30.4233 4235.52 319827 Q3
1 2.92 1
| | |
292.2 40000 0.227781
IXSCAN FETCH SORT
( 8) ( 11) ( 15)
29.9655 4235.07 319827
1 2.92 1
| /---+----\ |
2922 40000 1e+06 0.227781
INDEX: POPS IXSCAN TABLE: POPS IXSCAN
PERX1 ( 12) CUSTOMER ( 16)
Q1 2763.52 Q2 10.0149
1 1
| |
1e+06 6.65576e+08
INDEX: POPS INDEX: POPS
CUSTX1 PER_CUST_ST_PROMO
Q2 Q3
This shows that the difference between the index-scan plan and the single-probe plan is the way in which the fact table is accessed.
All other operators show the same information as the operators in the previous example.
Example: All probes list-prefetch access of the fact table
2.6623e+06
ZZJOIN
( 2)
78132.52
27.81
|
2.6623e+06
FETCH
( 3)
65524.23
27.81
|
2.6623e+06
RIDSCN
( 4)
56514.23
4.92
|
2.6623e+06
SORT
( 5)
56514.23
4.92
|
2.6623e+06
ZZJOIN
( 6)
7616.65
4.92
+---------------+--+------------+
292.2 40000 0.227781
TBSCAN TBSCAN IXSCAN
( 7) ( 10) ( 14)
56.2251 7596.78 9.93701
1 2.92 1
| | |
292.2 40000 6.65576e+08
TEMP TEMP INDEX: POPS
( 8) ( 11) PER_CUST_ST_PROMO
30.4233 4235.52 Q3
1 2.92
| |
292.2 40000
IXSCAN FETCH
( 9) ( 12)
29.9655 4235.07
1 2.92
| /---+----\
2922 40000 1e+06
INDEX: POPS IXSCAN TABLE: POPS
PERX1 ( 13) CUSTOMER
Q1 2763.52 Q2
1
|
1e+06
INDEX: POPS
CUSTX1
Q2Compared to the other access
plans, the all probes list-prefetch plan shows an additional operator,
ZZJOIN (2). This operator is being used to perform back-joins of the
fact table with the dimension tables. It shows the following information:
Backjoin = True