stj11
asked on
db2 z/os V10 access path
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
T2.C2=T1.C2 AND
T2.C5=T1.C5 AND
T1.C6='Z' AND
T2.C6='Y' AND
T2.C7='X' AND
T2.C8<>T1.C9
FOR FETCH ONLY
T1 CARDINALITY =400052
T2 CARDINALITY=1300006
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,
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.
SELECT T1.C1, T1,C2, T1.C3,T2.C4,T2.C10
FROM T1, T2
WHERE T2.C1=T1.C1 AND
T2.C2=T1.C2 AND
T2.C5=T1.C5 AND
T1.C6='Z' AND
T2.C6='Y' AND
T2.C7='X' AND
T2.C8<>T1.C9
FOR FETCH ONLY
T1 CARDINALITY =400052
T2 CARDINALITY=1300006
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,
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for the explanation.
a) Can you please advise on how to calculate the selectivity (cost) for Table( T1 and T2 ) based on the FF/
available STATS ?
b) Because of the rebind it did change the access path due to Version upgrade. if you advise me how to calculate
the cost for the above access path then I can work out the cost for the new one.
a) Can you please advise on how to calculate the selectivity (cost) for Table( T1 and T2 ) based on the FF/
available STATS ?
b) Because of the rebind it did change the access path due to Version upgrade. if you advise me how to calculate
the cost for the above access path then I can work out the cost for the new one.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
a) How does it do the join please, when it finds the first candidate row will it start the join process or else wait till the TS scan on the entire Tablespace before starting the join ?
b) Sorry for my terminology. What I meant was how to work out the selectivity estimation. I guess for that one needs to know the filter factors for the predicates ?
There is no stats(-1) on the columns but those have HIGH2KEY and LOW2KEY valuse as below. How to find out the distribution is uniform or not pl ?
H2KEY L2KEY
C6 x'D5' x'C2'
C7 x'C6' x'C6'
Primary keys are as below :
T2 (C1,C2,C5,C6,C8)
T1(C1,C2,C5)
Yes , T2IX2 is not unique.
I am not going to improve the query but try to compare with the new access path after the rebind. First I'm trying to make sure that I understand correctly how to work out the selectivity estimation ,which I believe optimiser uses to decide on access path selection. Could you please help me to calculate how many rows optimiser estimates qualify from Tables T1 and T2 under the available STATs ?