INTERPRETATION of the result of an EXPLAIN (order of execution ?)

Anne Van den Bergh

INTERPRETATION of the result of an EXPLAIN (order of execution ?)
Is there anyone who can confirm my doubts about the ORDER of execution of
the DB2-statements in the plan_table ?

Environment : DB2 OS/390 Version 5.

Given : a query with several joins for which we obtained the accespath via
EXPLAIN


***QUERY :

select
NBB.KCPT_PSKR.C_PSKR,
NBB.KCPT_PSKR.D_PSKR_BEG,
NBB.KCPT_PSKR.D_PSKR_BETW,
NBB.KCPT_PSKR.A_PSKR_TERM,
NBB.KCPT_PSKR.A_PSKR,
NBB.KCPT_PSKR.Q_PSKR_TERM,
NBB.KCPT_PSKR.C_PSKR_PER,
NBB.KCPT_PSKR.D_PSKR_AFSL,
NBB.KCPT_PSKR.D_PSKR_EIND,
NBB.KCPT_ADRP.C_ADRP,
case when NBB.KCPT_ADRP.C_ADRP_LAND = 'BE'
then NBB.KCPT_ADRP.N_ADRP_STRAAT
end as N_ADRP_STRAAT,
case when NBB.KCPT_ADRP.C_ADRP_LAND = 'BE'
then NBB.KCPT_ADRP.C_ADRP_POST
end as C_ADRP_POST,
case when NBB.KCPT_ADRP.C_ADRP_LAND = 'BE'
then NBB.KCPT_POST.N_POST_FR
end as N_POST_FR,
case when NBB.KCPT_ADRP.C_ADRP_LAND = 'BE'
then NBB.KCPT_POST.N_POST_NL
end as N_POST_NL,
case when NBB.KCPT_ADRP.C_ADRP_LAND <> 'BE'
then NBB.KCPT_ADRP.N_ADRP_PLAATS
end as N_ADRP_PLAATS,
PERS.Z_PERS_RR,
PERS.Z_PERS,
PERS.N_PERS,
PERS.N_PERS_VOORN,
PERS.D_PERS_GEB,
PERS.C_PERS_GESL,
NBB.KCPT_WANB.D_WANB,
NBB.KCPT_WANB.N_WANB,
NBB.KCPT_BFAC.C_BFAC_POST,
NBB.KCPT_BFAC.D_BFAC,
NBB.KCPT_NGKR.C_NGKR,
NBB.KCPT_NGKR.A_NGKR,
NBB.KCPT_NGKR.D_NGKR,
NBB.KCPT_NGKR.D_NGKR_BEG,
NBB.KCPT_NGKR.D_NGKR_REG,
-- if C_PSKR_DELN_OVR = &PARTICIPANT
NBB.KCPT_PSKR.C_PSKR_DELN,
NBB.KCPT_DELN.N_DELN_NL,
NBB.KCPT_DELN.N_DELN_FR,
NBB.KCPT_SBMD.N_SBMD,
NBB.KCPT_COLL.Z_COLL,
NBB.KCPT_COLL.D_COLL_TOEL,
NBB.KCPT_COLL.D_COLL_HER,
NBB.KCPT_COLL.D_COLL_VERV,
NBB.KCPT_COLL.D_COLL_MINBS,
NBB.KCPT_COLL.D_COLL_MINPV,
NBB.KCPT_COLL.D_COLL_MINEIND,
NBB.KCPT_COLL.D_COLL_MINHER,
NBB.KCPT_COLL.D_COLL_JURBS,
NBB.KCPT_COLL.D_COLL_JURVERW,
NBB.KCPT_COLL.D_COLL_JUREIND,
NBB.KCPT_COLL.D_COLL_JURHER
from
(select
NBB.KCPT_PERS.Z_PERS_RR,
NBB.KCPT_PERS.Z_PERS,
NBB.KCPT_PERS.N_PERS,
NBB.KCPT_PERS.N_PERS_VOORN,
NBB.KCPT_PERS.D_PERS_GEB,
NBB.KCPT_PERS.C_PERS_GESL
from
NBB.KCPT_PERS
where
I_PERS_EIND = 'N'
and z_pers_rr = '12345678912') as PERS
left outer join nbb.kcpt_coll
on z_pers = z_coll_pers
left outer join NBB.KCPT_WANB
on z_pers = z_wanb_pers,
NBB.KCPT_PRKR,
NBB.KCPT_PSKR left OUTER JOIN NBB.KCPT_NGKR
on Z_PSKR_RED = Z_NGKR_RED and
H_PSKR = H_NGKR_PSKR
left outer join NBB.KCPT_BFAC
on Z_PSKR_RED = Z_BFAC_RED and
H_PSKR = H_BFAC_PSKR,
NBB.KCPT_DELN,
NBB.KCPT_SBMD,
NBB.KCPT_ADRP left OUTER JOIN NBB.KCPT_POST
on C_ADRP_POST = C_POST_CODE
where
-- join key to PRKR
Z_PRKR_PERS = PERS.Z_PERS
-- join key to PSKR
and (Z_PSKR_RED = Z_PRKR_RED and H_PSKR = H_PRKR_PSKR)
-- join key to ADRP
and PERS.Z_PERS = Z_ADRP_PERS
-- join key to SBMD
and H_COLL_SBMD = H_SBMD
-- join key to DELN
and C_PSKR_DELN = C_DELN



***Result of the EXPLAIN (plan_table):
please, look attachment


***In Bookmanager, I found following statement :

"All rows with the same non-zero value for QBLOCKNO and
the same value for QUERYNO relate to a step within the query.
QBLOCKNOs are not necessarily executed in the order shown in PLAN_TABLE.
But within a QBLOCKNO, the PLANNO column gives the substeps in the order
they execute."


***So , my question :
If qblocknos are not necessarily executed in the order shown in the
plan_table,
are there other ways to know the REAL ORDER OF EXECUTION by DB2 ???

Thanks in advance.

[[ EXPLAIN.doc : 1536 in winmail.dat ]]

Visit our website! http://www.nbb.be

"DISCLAIMER: The content of this e-mail message does not constitute a commitment of the National Bank of Belgium (NBB) except where provided for in a written agreement between you and the NBB or where confirmed with a written form approved according to the internal regulations of the NBB.
Besides, the statements and opinions expressed in this e-mail message are those of the author of the message and do not necessarily represent those of the NBB.
The e-mail message contains proprietary information intended for the intended recipient only. If an addressing or transmission error has misdirected this e-mail, please notify the author. If you are not the intended recipient you must not use, disclose, distribute, copy, print or rely on any part of this e-mail message."



Terry Purcell

Re: INTERPRETATION of the result of an EXPLAIN (order of execution ?)
(in response to Anne Van den Bergh)
Anne,

Firstly, we did not receive your attachment, as these are not allowed by the
list.

As you've found, you cannot rely on the qblockno's, as these relate more to
the sequence of the separate SQL blocks as coded in the query, rather than
the execution sequence.

Subqueries and materialized nested table expressions are the main culprits.
Non-correlated subqueries and materialized NTEs are executed first, and
often appear last in the explain output. Correlated subqueries are executed
as regular stage 2 predicates on the table that supplies the last of the
available correlation predicates.

It gets better when the planno's within the same qblockno are not executed
in the sequence of the explain output. The inner table of a merge scan join,
appearing second in the planno sequence can actually be accessed before the
outer table since it is always materialized.

If you don't wish to care this much about interpreting the plan table output
yourself, then you may wish to consider using visual explain. I don't use
it, but that's because I can read the plan table. If you can't (and most
people don't want to...understandably), then go straight to using visual
explain.

Regards
Terry Purcell
Yevich Lawson & Assoc Inc (YL&A)
IBM Gold Consultant - DB2
IBM Certified Solutions Expert
DB2 V7 Database Administration OS/390
http://www.ylassoc.com


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf Of
Anne Van den Bergh
Sent: Wednesday, January 02, 2002 12:56 AM
To: [login to unmask email]
Subject: INTERPRETATION of the result of an EXPLAIN (order of execution?)

Is there anyone who can confirm my doubts about the ORDER of execution of
the DB2-statements in the plan_table ?

Environment : DB2 OS/390 Version 5.

Given : a query with several joins for which we obtained the accespath via
EXPLAIN

***QUERY :

select
NBB.KCPT_PSKR.C_PSKR,
NBB.KCPT_PSKR.D_PSKR_BEG,
NBB.KCPT_PSKR.D_PSKR_BETW,
NBB.KCPT_PSKR.A_PSKR_TERM,
NBB.KCPT_PSKR.A_PSKR,
NBB.KCPT_PSKR.Q_PSKR_TERM,
NBB.KCPT_PSKR.C_PSKR_PER,
NBB.KCPT_PSKR.D_PSKR_AFSL,
NBB.KCPT_PSKR.D_PSKR_EIND,
NBB.KCPT_ADRP.C_ADRP,
case when NBB.KCPT_ADRP.C_ADRP_LAND = 'BE'
then NBB.KCPT_ADRP.N_ADRP_STRAAT
end as N_ADRP_STRAAT,
case when NBB.KCPT_ADRP.C_ADRP_LAND = 'BE'
then NBB.KCPT_ADRP.C_ADRP_POST
end as C_ADRP_POST,
case when NBB.KCPT_ADRP.C_ADRP_LAND = 'BE'
then NBB.KCPT_POST.N_POST_FR
end as N_POST_FR,
case when NBB.KCPT_ADRP.C_ADRP_LAND = 'BE'
then NBB.KCPT_POST.N_POST_NL
end as N_POST_NL,
case when NBB.KCPT_ADRP.C_ADRP_LAND <> 'BE'
then NBB.KCPT_ADRP.N_ADRP_PLAATS
end as N_ADRP_PLAATS,
PERS.Z_PERS_RR,
PERS.Z_PERS,
PERS.N_PERS,
PERS.N_PERS_VOORN,
PERS.D_PERS_GEB,
PERS.C_PERS_GESL,
NBB.KCPT_WANB.D_WANB,
NBB.KCPT_WANB.N_WANB,
NBB.KCPT_BFAC.C_BFAC_POST,
NBB.KCPT_BFAC.D_BFAC,
NBB.KCPT_NGKR.C_NGKR,
NBB.KCPT_NGKR.A_NGKR,
NBB.KCPT_NGKR.D_NGKR,
NBB.KCPT_NGKR.D_NGKR_BEG,
NBB.KCPT_NGKR.D_NGKR_REG,
-- if C_PSKR_DELN_OVR = &PARTICIPANT
NBB.KCPT_PSKR.C_PSKR_DELN,
NBB.KCPT_DELN.N_DELN_NL,
NBB.KCPT_DELN.N_DELN_FR,
NBB.KCPT_SBMD.N_SBMD,
NBB.KCPT_COLL.Z_COLL,
NBB.KCPT_COLL.D_COLL_TOEL,
NBB.KCPT_COLL.D_COLL_HER,
NBB.KCPT_COLL.D_COLL_VERV,
NBB.KCPT_COLL.D_COLL_MINBS,
NBB.KCPT_COLL.D_COLL_MINPV,
NBB.KCPT_COLL.D_COLL_MINEIND,
NBB.KCPT_COLL.D_COLL_MINHER,
NBB.KCPT_COLL.D_COLL_JURBS,
NBB.KCPT_COLL.D_COLL_JURVERW,
NBB.KCPT_COLL.D_COLL_JUREIND,
NBB.KCPT_COLL.D_COLL_JURHER
from
(select
NBB.KCPT_PERS.Z_PERS_RR,
NBB.KCPT_PERS.Z_PERS,
NBB.KCPT_PERS.N_PERS,
NBB.KCPT_PERS.N_PERS_VOORN,
NBB.KCPT_PERS.D_PERS_GEB,
NBB.KCPT_PERS.C_PERS_GESL
from
NBB.KCPT_PERS
where
I_PERS_EIND = 'N'
and z_pers_rr = '12345678912') as PERS
left outer join nbb.kcpt_coll
on z_pers = z_coll_pers
left outer join NBB.KCPT_WANB
on z_pers = z_wanb_pers,
NBB.KCPT_PRKR,
NBB.KCPT_PSKR left OUTER JOIN NBB.KCPT_NGKR
on Z_PSKR_RED = Z_NGKR_RED and
H_PSKR = H_NGKR_PSKR
left outer join NBB.KCPT_BFAC
on Z_PSKR_RED = Z_BFAC_RED and
H_PSKR = H_BFAC_PSKR,
NBB.KCPT_DELN,
NBB.KCPT_SBMD,
NBB.KCPT_ADRP left OUTER JOIN NBB.KCPT_POST
on C_ADRP_POST = C_POST_CODE
where
-- join key to PRKR
Z_PRKR_PERS = PERS.Z_PERS
-- join key to PSKR
and (Z_PSKR_RED = Z_PRKR_RED and H_PSKR = H_PRKR_PSKR)
-- join key to ADRP
and PERS.Z_PERS = Z_ADRP_PERS
-- join key to SBMD
and H_COLL_SBMD = H_SBMD
-- join key to DELN
and C_PSKR_DELN = C_DELN



***Result of the EXPLAIN (plan_table):
please, look attachment

***In Bookmanager, I found following statement :

"All rows with the same non-zero value for QBLOCKNO and
the same value for QUERYNO relate to a step within the query.
QBLOCKNOs are not necessarily executed in the order shown in PLAN_TABLE.
But within a QBLOCKNO, the PLANNO column gives the substeps in the order
they execute."

***So , my question :
If qblocknos are not necessarily executed in the order shown in the
plan_table,
are there other ways to know the REAL ORDER OF EXECUTION by DB2 ???

Thanks in advance.