By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,225 Members | 1,051 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,225 IT Pros & Developers. It's quick & easy.

DGTT Changes access plan (extra SORT + RIDSCAN)

P: n/a
All:

We are running UDB V8.2 FP8. We have sql query which uses DGTT. The
access plan for the query changes based on rows selected into DGTT.

1. Secnario I: DGTT has atlest 1 row.
I see INDEX ORing (IDXSCAN, SORT and RIDSCAN) when DGTT has atlest 1
row.

2. Scenario II: DGTT is empty.
If the DGTT is empty, the query plan has IDXSCAN only.

Question:
1. Why does INDEX ORing choosen when DGTT has rows but not when it's
empty?
2. How can we make DGTT provide same access plan (which is to avoid
INDEX ORing)?
Section of plan when DGTT returns atlest 1 matching row:

3.70898e-06
FETCH
( 24)
166.517
50.7799
/---+---\
60.4011 1.91409e+07
RIDSCN TABLE: TYES
( 25) EQ_EVENT_H
75.1384
3
|
60.4011
SORT
( 26)
75.1323
3
|
60.4011
IXSCAN
( 27)
75.0901
3
|
1.91409e+07
INDEX: TYES
EQ_EVENT_H_I1
Section of plan when DGTT returns ZERO matching row:

3.27106e-06
FETCH
( 22)
884.801
35.3819
/---+---\
59.7192 1.86085e+07
IXSCAN TABLE: TYES
( 23) EQ_EVENT_H
75.0258
3
|
1.86085e+07
INDEX: TYES
EQ_EVENT_H_I1
Thank you much!

Vijay

Nov 12 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
UDBDBA wrote:
All:

We are running UDB V8.2 FP8. We have sql query which uses DGTT. The
access plan for the query changes based on rows selected into DGTT.

1. Secnario I: DGTT has atlest 1 row.
I see INDEX ORing (IDXSCAN, SORT and RIDSCAN) when DGTT has atlest 1
row.

2. Scenario II: DGTT is empty.
If the DGTT is empty, the query plan has IDXSCAN only.

Question:
1. Why does INDEX ORing choosen when DGTT has rows but not when it's
empty?
2. How can we make DGTT provide same access plan (which is to avoid
INDEX ORing)?
Section of plan when DGTT returns atlest 1 matching row:

3.70898e-06
FETCH
( 24)
166.517
50.7799
/---+---\
60.4011 1.91409e+07
RIDSCN TABLE: TYES
( 25) EQ_EVENT_H
75.1384
3
|
60.4011
SORT
( 26)
75.1323
3
|
60.4011
IXSCAN
( 27)
75.0901
3
|
1.91409e+07
INDEX: TYES
EQ_EVENT_H_I1
Section of plan when DGTT returns ZERO matching row:

3.27106e-06
FETCH
( 22)
884.801
35.3819
/---+---\
59.7192 1.86085e+07
IXSCAN TABLE: TYES
( 23) EQ_EVENT_H
75.0258
3
|
1.86085e+07
INDEX: TYES
EQ_EVENT_H_I1
Thank you much!

Vijay

This looks more like list prefetching to me than index-oring.
Prefetching is disabled with optlevel 0 and 1.
Obviously prefetching makes little sense for a table that's known to be
empty.....
Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #2

P: n/a
Hi Serge:
I made a mistake while posting and apologize for any confusion.

It's NOT the DGTT result set that changes the plan, it's the result set
of the query using the DGTT that changes the plan.

When the query (using DGTT) returns rows, then i see the
IDXSCAN+SORT+RIDSCAN and then FETCH. If the query returned ZERO rows,
then it does a IDXSCAN and then FETCH.

So, now can you tell me what's going on?

Once again, sorry for the confusion.

Vijay
So,

Nov 12 '05 #3

P: n/a
UDBDBA wrote:
Hi Serge:
I made a mistake while posting and apologize for any confusion.

It's NOT the DGTT result set that changes the plan, it's the result set
of the query using the DGTT that changes the plan.

When the query (using DGTT) returns rows, then i see the
IDXSCAN+SORT+RIDSCAN and then FETCH. If the query returned ZERO rows,
then it does a IDXSCAN and then FETCH.

So, now can you tell me what's going on?

Once again, sorry for the confusion.

Vijay

Couple of questiosn for clarification:
1. Am I correct that the rsult set is CORRECT?
(I.e. you are not getting different results depending on the plan chosen
given the same data in DB2)
2. Care to share more information about the predicates?
It may well be that DB2 can prove that the number of rows will be 0 (or
close to 0) and this prove gets reflected in the optimizers strategy...

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #4

P: n/a
1. Yes. Result set is correct. It all depends on the predicate value.
2. Sure, here is the query with the DGTT definition given below.
(Thanks much for the help!)

DGTT definition:
DECLARE GLOBAL TEMPORARY TABLE
SESSION."#ts930a_scan_eq"
( scnr_rpt_ln_nr smallint not null
, cnsst_seq_nr smallint not null
, eq_init char(4) not null
, eq_nr char (10) not null
, eq_display_tp char(1) not null
, equipid char(15) not null
, scnr_vehicle_tp char(1) not null
, orientation char(1) not null
, tag_stat char(1) not null
, eq_id numeric(14)
, empty_ind char(1)
, curr_gals smallint
, kwh int
, wgt int
, pos_on_tk smallint
, indus_nr char(4)
, sub_tk_or_spot char(2)
, spot smallint
, eq_loc_id numeric(14)
, tk_id char(6)
, actv_trn_id numeric(14,0)
, plnned_ic_rcpt_id numeric(14)
, lst_rpt_dtm char(26)
, lst_rpt_loc_id numeric(14)
, lst_rpt_evt_tp char(4)
) WITH REPLACE ON COMMIT PRESERVE ROWS NOT LOGGED !

INSERT INTO SESSION."#ts930a_scan_eq"
( scnr_rpt_ln_nr
, cnsst_seq_nr
, eq_init
, eq_nr
, eq_display_tp
, equipid
, scnr_vehicle_tp
, orientation
, tag_stat
, eq_id
, empty_ind
, curr_gals
, kwh
, wgt
, pos_on_tk
, indus_nr
, sub_tk_or_spot
, spot
, sub_tk_or_spot
, spot
, eq_loc_id
, tk_id
, actv_trn_id
, plnned_ic_rcpt_id
, lst_rpt_dtm
, lst_rpt_loc_id
, lst_rpt_evt_tp)
SELECT a.scnr_rpt_ln_nr
, a.cnsst_seq_nr
, a.eq_init
, a.eq_nr
, CASE WHEN a.scnr_vehicle_tp = 'D' THEN 'E'
WHEN a.scnr_vehicle_tp = 'E' THEN 'X'
ELSE ' '
END
, a.eq_init ||
CASE WHEN a.scnr_vehicle_tp = 'D' THEN 'E'
WHEN a.scnr_vehicle_tp = 'E' THEN 'X'
ELSE ' '
END ||
a.eq_nr
, a.scnr_vehicle_tp
, a.orientation
, a.tag_stat
, b.eq_id
, b.empty_ind
, a.curr_gals
, a.kwh
, a.wgt
, b.pos_on_tk
, b.indus_nr
, b.sub_tk_or_spot
, b.spot
, b.eq_loc_id
, b.tk_id
, b.actv_trn_id
, 0
, b.lst_rpt_dtm
, b.lst_rpt_loc_id
, b.lst_rpt_evt_tp
FROM
tyes.scnr_rpt_eq AS a left outer join
tyes.actv_eq AS b on
a.eq_init = b.eq_init and a.eq_nr = b.eq_nr
and (CASE WHEN a.scnr_vehicle_tp = 'D' THEN 'E'
WHEN a.scnr_vehicle_tp = 'E' THEN 'X'
ELSE ' '
WHEN a.scnr_vehicle_tp = 'E' THEN 'X'
ELSE ' '
END) = b.eq_display_tp
WHERE a.stor_dtm = '2005-06-03-16.37.09.260000'
!

The actual Query is:
SELECT distinct cast(rtrim(c.trn_nr)
|| rtrim(COALESCE(c.crew_distr, ''))
|| right('00'
|| cast(c.origin_day as char(2)),2)
as char(10))
, b.cnsst_h_id
FROM SESSION."#ts930a_scan_eq" as a
, tyes.trn_event_h as b
, tyes.eq_event_h as c
WHERE a.actv_trn_id = b.actv_trn_id
AND b.evt_tp = 'DFLC'
AND b.cnsst_h_id = c.cnsst_h_id
AND c.source_cd in (4, 5)
AND c.evt_dtm =
(SELECT MAX (r.evt_dtm)
FROM tyes.trn_event_h r
, tyes.eq_event_h s
WHERE r.actv_trn_id = b.actv_trn_id
AND r.evt_tp = 'DFLC'
AND r.cnsst_h_id = s.cnsst_h_id
AND s.source_cd in (4, 5) )
AND ( 2 * (SELECT count(1)
FROM SESSION."#ts930a_scan_eq" x
WHERE x.actv_trn_id = b.actv_trn_id)

(SELECT count(1)
FROM tyes.eq_event_h y
WHERE y.cnsst_h_id = c.cnsst_h_id
FROM tyes.eq_event_h y
WHERE y.cnsst_h_id = c.cnsst_h_id
AND y.eq_tp NOT IN ('T', 'C', 'Z'))
)
!
Thanks!

Vijay

Nov 12 '05 #5

P: n/a
Vijay,

Do you mean this predicate (of the INSERT statement)?
WHERE a.stor_dtm = '2005-06-03-16.37.09.260000'
If the temp table is empty DB2 will know about it.
The SELECT does not get compiled before first execution because DB2 must
be sure about the signature of the global temp.

"TYES"... in case you operate this cross continental fantasy of every
modeltrain enthusiast, bounce me an email with your # and I'll see to it
we get you over the hump. :-)

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #6

P: n/a
Serge:

Did drop you an email as requested...

Meantime....

As you mentioned, the RIDSCN is a list prefetch operation. I tried
using DB2_MINIMIZE_LISTPREFETCH registry variable, but no difference in
plan. (Note: Run statistics on all columns with detailed indexes all.)
Also the query has IN list, so decided to use DB2_INLIST_NLJN registry
variable, no differnce in plan / query rewrite. The IN operation were
shown in the SQL rewriten by the optimizer.

Anything i can try while Support is working on this problem?

Thanks Much!

Vijay

Nov 12 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.