db2 z/os V10 access path
Posted on 2014-08-16
Hi , I would like to calculate the Filter Factor for the below query to compare with the access path after the rebind.
SELECT T1.C1, T1,C2, T1.C3,T2.C4,T2.C10
FROM T1, T2
WHERE T2.C1=T1.C1 AND
FOR FETCH ONLY
T1 CARDINALITY =400052
T1 INDEX -> T1IX1 ON C1,C2 FULLKEYCARD = 400052
T2 INDEX -> T2IX2 ON C1,C2,C5 FULL KEY CARDF =400052
Otimiser selects the following access path before rebind:
1.1 Tablespace scan on T2
1.2 Nested loop join with T1 on index T1X1 MC=2
My questions are :
a) When it does the Table space scan on T2 will it access all data pages and apply T2 predicates(C1,C2,C5,C6,C7,C8) and filtered and generate the result set and use that to do the Nested loop join ?
b) How to workout the Filter Factor for the Query for the above access Path given the above Indexes/STATS are available ?
I am new to application performance tuning /access paths selection and thanks in advance for your advice/direction. Would like to understand the basics on access path cost calculation.