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

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