very different costs for select and update - why?

[bughunter]

simple query

select * from "Result" res where (res."QID" = 51541 or res."QID" =
51542)

works fine ("SRV-BL"."Result" ~ 900000 rows) and returns 36 rows

but update - no!

update "SRV-BL"."Result" res set "Status"=0 where (res."QID" = 51541
or res."QID" = 51542)

Exec Time: 2.394387 seconds
Number of Agents created: 1
User CPU: 2.343750 seconds
System CPU: 0.031250 seconds
Fetch Count: 0
Sorts: 0
Total sort time: 0
Sort overflows: 0
Rows read: 2684799
Rows written: 38
Internal rows deleted: 0
Internal rows updated: 0
Internal rows inserted: 0
Bufferpool data logical reads: 25671
Bufferpool data physical reads: 1
Bufferpool temporary data logical reads: 894970

Explains show me - update has tablescan.

Access Plan:
-----------
Total Cost: 100916
Query Degree: 0

Rows
RETURN
( 1)
Cost
I/O
|
4242.54
UPDATE
( 2)
100916
29357.5
/---+---\
4242.54 875673
NLJOIN TABLE: SRV-BL
( 3) Result
46388.8
25115
/----+---\
875673 0.00484489
TBSCAN TBSCAN
( 4) ( 5)
44701.6 0.012825
25115 0
| |
875673 2
TABLE: SRV-BL TEMP
Result ( 6)
0.0037412
0
|
2
TBSCAN
( 7)
3.66067e-005
0
|
2
TABFNC: SYSIBM
GENROW
But for select

Access Plan:
-----------
Total Cost: 1366.05
Query Degree: 0

Rows
RETURN
( 1)
Cost
I/O
|
4242.54
NLJOIN
( 2)
1366.05
608.865
/------+------\
2 2121.27
TBSCAN FETCH
( 3) ( 4)
3.66067e-005 683.037
0 304.432
| /---+---\
2 2121.27 875673
TABFNC: SYSIBM IXSCAN TABLE: SRV-BL
GENROW ( 5) Result
58.0589
4.44714
|
875673
INDEX: RUAPNA
RESULT_QID_SHORT


CREATE TABLE SRV-BL.Result
(BLID INTEGER,
QID INTEGER,
PatternID INTEGER,
TimeStamp TIMESTAMP,
SecurityGroupName SRV-BL.VA,
GroupName SRV-BL.VA,
PatternName SRV-BL.VA,
Matched INTEGER,
BLAddressID INTEGER,
QAddressID INTEGER,
BLPhoneID INTEGER,
QPhoneID INTEGER,
BLDocID INTEGER,
QDocID INTEGER,
MatchDate TIMESTAMP
);

CREATE INDEX RUAPNA.RESULT_QID_SHORT
ON SRV-BL.Result
(QID ASC
);

Andy

[jefftyzzer]

Hmmm...I've seen something like this on an UPDATE to a base table of an
MQT. Just for kicks, change your OR to an IN, i.e.,

update "SRV-BL"."Result" res set "Status"=0 where res."QID" IN (51541,
51542);

I did this, and it made a HUGE difference in my situation. It's all
about when and how the optimizer chooses to use that little TEMP table
it seems to like to create in certain circumstances :-).

Otherwise, you might want to try using MERGE to do the UPDATE--I've had
amazing luck with Serge's advice to convert some DELETEs to MERGEs; you
may have similar luck with an UPDATE.

Regards,

--Jeff

bughunter@ru wrote:

simple query

select * from "Result" res where (res."QID" = 51541 or res."QID" =
51542)

works fine ("SRV-BL"."Result" ~ 900000 rows) and returns 36 rows

but update - no!

update "SRV-BL"."Result" res set "Status"=0 where (res."QID" = 51541
or res."QID" = 51542)

Exec Time: 2.394387 seconds
Number of Agents created: 1
User CPU: 2.343750 seconds
System CPU: 0.031250 seconds
Fetch Count: 0
Sorts: 0
Total sort time: 0
Sort overflows: 0
Rows read: 2684799
Rows written: 38
Internal rows deleted: 0
Internal rows updated: 0
Internal rows inserted: 0
Bufferpool data logical reads: 25671
Bufferpool data physical reads: 1
Bufferpool temporary data logical reads: 894970

Explains show me - update has tablescan.

Access Plan:
-----------
Total Cost: 100916
Query Degree: 0

Rows
RETURN
( 1)
Cost
I/O
|
4242.54
UPDATE
( 2)
100916
29357.5
/---+---\
4242.54 875673
NLJOIN TABLE: SRV-BL
( 3) Result
46388.8
25115
/----+---\
875673 0.00484489
TBSCAN TBSCAN
( 4) ( 5)
44701.6 0.012825
25115 0
| |
875673 2
TABLE: SRV-BL TEMP
Result ( 6)
0.0037412
0
|
2
TBSCAN
( 7)
3.66067e-005
0
|
2
TABFNC: SYSIBM
GENROW
But for select

Access Plan:
-----------
Total Cost: 1366.05
Query Degree: 0

Rows
RETURN
( 1)
Cost
I/O
|
4242.54
NLJOIN
( 2)
1366.05
608.865
/------+------\
2 2121.27
TBSCAN FETCH
( 3) ( 4)
3.66067e-005 683.037
0 304.432
| /---+---\
2 2121.27 875673
TABFNC: SYSIBM IXSCAN TABLE: SRV-BL
GENROW ( 5) Result
58.0589
4.44714
|
875673
INDEX: RUAPNA
RESULT_QID_SHORT


CREATE TABLE SRV-BL.Result
(BLID INTEGER,
QID INTEGER,
PatternID INTEGER,
TimeStamp TIMESTAMP,
SecurityGroupName SRV-BL.VA,
GroupName SRV-BL.VA,
PatternName SRV-BL.VA,
Matched INTEGER,
BLAddressID INTEGER,
QAddressID INTEGER,
BLPhoneID INTEGER,
QPhoneID INTEGER,
BLDocID INTEGER,
QDocID INTEGER,
MatchDate TIMESTAMP
);

CREATE INDEX RUAPNA.RESULT_QID_SHORT
ON SRV-BL.Result
(QID ASC
);

Andy