z/OS DB2 V8. What triggerred the change in Access path

Ray

z/OS DB2 V8. What triggerred the change in Access path


Dear listers,
 
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
 
SQL:
SELECT    T1."FK_CUST_ID"                      
               , T1."REINSTATED_DT"                          
               , T2."CUST_ID"                         
FROM "VCUSR_REINST"   T1,
           "VCUST_ORG_UNIT" T2                
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:
 
T2:
    Primary/Clustering index CUST_ID
    Cardinality    : 600K
    Cluster Ratio  : 0.99
T1:
   Child to T2.  Has only index which is the Primary/Clustering index
                      ( FK_CUST_ID + REINSTATED_DT)
   Cardinality     : 5100K
   Cluster Ratio   : 56
 
Cheers, Ray




_____________________________________________________________________

* 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

Terry Purcell

Re: z/OS DB2 V8. What triggerred the change in Access path
(in response to Ray)
Ray,

RUNSTATS is a likely reason for the access path change. No question.
Maintenance upgrade can also result in a change.

My thought is that the sequential prefetch is likely a reasonable choice, given
the range predicate on T2 and it's index-only. I am guessing you are
concerned about the list prefetch on T1.

Before you go trying to figure out exactly why.........a clusterratio of 56%
sounds like a good reason for list prefetch. And given this is the clustering
index, I think the tablespace is in dire need of a REORG.

Regards
Terry

On Wed, 30 Dec 2009 13:15:49 -0800, Ray <[login to unmask email]>
wrote:

>
>
>Dear listers,
>?
>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
>?
>SQL:
>SELECT??? T1."FK_CUST_ID"??????????????????????
>?????????????? ,
T1."REINSTATED_DT"??????????????????????????
>?????????????? , T2."CUST_ID"?????????????????????????
>FROM "VCUSR_REINST"?? T1,
>????????? ?"VCUST_ORG_UNIT" T2????????????????
>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:
>?
>T2:
>??? Primary/Clustering index CUST_ID
>??? Cardinality??? : 600K
>??? Cluster Ratio? : 0.99
>T1:
>?? Child to T2.? Has only index which is the Primary/Clustering index
>????????????????????? ( FK_CUST_ID +?REINSTATED_DT)
>?? Cardinality???? : 5100K
>?? Cluster Ratio?? : 56
>?
>Cheers, Ray



>
>__________________________________________________________
___________
>
>* 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

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-videos.html has hundreds of video presentations!
Did you miss out on attending an IDUG conference?
Many of the presentations were recorded and are available on our website!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L