Zigzag joins with index gaps
Even if there is a gap in the composite indexes, due to a missing join predicate or a missing dimension in the query, the query optimizer recognizes the star shape of a query and can select an appropriate access plan with a zigzag join. However, the performance of access plan may not be optimal. Review the db2exfmt command output to find out whether there are any index gaps in your query and consider adding new indexes or modifying existing indexes to improve performance.
Example: Zigzag join with a missing dimension
select count(*)
from d1, d3, d4, d5, f1
where d1.pk = f1.fk1 and d3.pk = f1.fk3 and d4.pk = f1.fk4 and d5.pk = f1.fk5create index i11 on f1(fk1, fk2, fk3, fk4, fk5, fk6);The query joins dimensions d1, d3, d4, d5 with fact table f1. Because the dimension d2 is not included in the query, there is no join predicate with the dimension d2 on the column fk2. The query optimizer recognizes fact column fk2 as a gap in the index and is able to use the index for a zigzag join.
The db2exfmt command output shows that the index scan is a jump scan, by indicating the JUMPSCAN=TRUE option. The output also shows the index gap information, specifically that the second index column has a positioning gap and the other columns do not.
Rows
RETURN
( 1)
Cost
I/O
|
1
GRPBY
( 2)
1539.45
33
|
1000
ZZJOIN
( 3)
1529.44
33
+----------------+----------------++---------------+------------------+
1000 1000 1000 1000 1000
TBSCAN TBSCAN TBSCAN TBSCAN FETCH
( 4) ( 9) ( 14) ( 19) ( 24)
184.085 184.085 184.085 184.085 205.222
8 8 8 8 1
| | | | /---+----\
1000 1000 1000 1000 1000 1000
TEMP TEMP TEMP TEMP RIDSCN TABLE: STAR
( 5) ( 10) ( 15) ( 20) ( 25) F1
184.003 184.003 184.003 184.003 55.5857 Q1
8 8 8 8 1
| | | | |
1000 1000 1000 1000 1000
TBSCAN TBSCAN TBSCAN TBSCAN SORT
( 6) ( 11) ( 16) ( 21) ( 26)
178.62 178.62 178.62 178.62 55.5342
8 8 8 8 1
| | | | |
1000 1000 1000 1000 1e-09
SORT SORT SORT SORT IXSCAN
( 7) ( 12) ( 17) ( 22) ( 27)
178.569 178.569 178.569 178.569 12.0497
8 8 8 8 1
| | | | |
1000 1000 1000 1000 1000
TBSCAN TBSCAN TBSCAN TBSCAN INDEX: STAR
( 8) ( 13) ( 18) ( 23) I11
135.093 135.093 135.093 135.093 Q1
8 8 8 8
| | | |
1000 1000 1000 1000
TABLE: STAR TABLE: STAR TABLE: STAR TABLE: STAR
D1 D3 D4 D5
Q5 Q4 Q3 Q2
JUMPSCAN: (Jump Scan Plan)
TRUE
Gap Info: Status
--------- ------
Index Column 0: No Gap
Index Column 1: Positioning Gap
Index Column 2: No Gap
Index Column 3: No Gap
Index Column 4: No Gap Example: Zigzag join with missing join predicate on fact column
select count(*)
from d2, d3, d4, f1
where d2.pk = f1.fk2 and d3.pk = f1.fk3 and d4.pk = f1.fk4 and fk1=10In this query, dimensions d2, d3 and d4 join with the fact table f1. There is no join predicate on fact column fk1, there is only a local predicate fk1=10.
The query optimizer recognizes the fact column fk1 as a gap, because there is no join predicate on it. The query optimizer is still able to use the index for zigzag join.
The db2exfmt command output shows that the index scan is a jump scan, by indicating the JUMPSCAN=TRUE option. The output also shows the index gap information, specifically that the first index column has a positioning gap and the other columns do not.
Rows
RETURN
( 1)
Cost
I/O
|
1
GRPBY
( 2)
893.899
25.12
|
40
HSJOIN
( 3)
893.489
25.12
/------------+-------------\
1000 40
TBSCAN ZZJOIN
( 4) ( 5)
135.093 750.88
8 17.12
| +----------------++-----------------+
1000 1000 1000 40
TABLE: STAR TBSCAN TBSCAN FETCH
D4 ( 6) ( 11) ( 16)
Q2 184.085 184.085 18.1845
8 8 1.12004
| | /---+----\
1000 1000 40 1000
TEMP TEMP RIDSCN TABLE: STAR
( 7) ( 12) ( 17) F1
184.003 184.003 13.4358 Q1
8 8 1.12
| | |
1000 1000 40
TBSCAN TBSCAN SORT
( 8) ( 13) ( 18)
178.62 178.62 13.3843
8 8 1.12
| | |
1000 1000 4e-05
SORT SORT IXSCAN
( 9) ( 14) ( 19)
178.569 178.569 12.5738
8 8 1.12
| | |
1000 1000 1000
TBSCAN TBSCAN INDEX: STAR
( 10) ( 15) I11
135.093 135.093 Q1
8 8
| |
1000 1000
TABLE: STAR TABLE: STAR
D2 D3
Q4 Q3
JUMPSCAN: (Jump Scan Plan)
TRUE
Gap Info: Status
--------- ------
Index Column 0: Positioning Gap
Index Column 1: No Gap
Index Column 2: No Gap