Am little surprised why the optimizer brings in a Sequential and List Prefetch on
an SQL that has not been changed and has seen below average growth in data.
I think that the runstats somehow precipitated this change but am not sure why
there was a change! Any pointers will be appreciated.
Explain / Before :
Uses Primary Clustering indices on both tables without Prefetch
Explain / Now:
Uses Primary Clustering indices on both tables but brings
a Sequential Prefetch on T2 and and List Prefetch on T1
FROM "VCUSR_REINST" T1,
WHERE T1."FK_CUST_ID" = T2."CUST_ID"
AND T1."APROVED_DT" <= :PROCESS-DATE
AND T1."ROW_VER_NB" = 1
AND T2."CUST_ID" >= :IDENTIFIER
ORDER BY 3 ASC
Details about the Table:
Primary/Clustering index CUST_ID
Cardinality : 600K
Cluster Ratio : 0.99
Child to T2. Has only index which is the Primary/Clustering index
( FK_CUST_ID + REINSTATED_DT)
Cardinality : 5100K
Cluster Ratio : 56
* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
http://www.IDUG.org membership is now free.
Do you have people in your office who are not an IDUG member?
Show them how to access the information and help train the next generation of DB2 Users!
If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L