问题描述
Linux RH 4.0运行DB2 V8 FP 11.
我有一个包含~11M行的表，并且正在运行DELETE语句实际上是很慢的b $ b。删除1k行需要3分钟以上。如果我在同一个表上运行select
语句，我通常会在合理的
时间内获取行。
该表有以下描述：
MACHINE_ID VARCHAR（24）
COLLECT_TIME TIMESTAMP
PROCESS_NAME VARCHAR（64）
PROCESS_PID DECIMAL（10）
这些字段是PK表。 （MACHINE_ID，
COLLECT_TIME）还有索引。
我运行了db2exfmt，解释计划似乎表明价格低了
报表费用。表和索引的统计信息已更新。
我对实际非常慢的执行速度感到困惑。
任何人都可以指导我跟踪执行约束的原因和位置？
提前致谢，
原始声明：
-------------- ----
删除
来自RTM.TBL_COLLECT_PSSTAT_WIN_RTM
其中MACHINE_ID =''000423B0F8F61420F1B8111A''和COLLECT_TIME之间
''2006-04-17 00：00：55.0''和''2006-04-17 01：00：55.0''
优化声明：
-------------------
DELETE
来自RTM.TBL_COLLECT_PSSTAT_WIN_RTM AS Q1
在哪里$ RID $ IN
（SELECT $ RID $
来自RTM.TBL_COLLECT_PSSTAT_WIN_RTM AS Q2
WHERE（Q2.COLLECT_TIME< ='' 2006-04-17-01.00.55.000000''）和
（''2006-04-17-00.00.55.000000''< = Q2.COLLECT_TIME）和
（Q2.MACHINE_ID =''000423B0F8F61420F1B8111A''））
访问计划：
-----------
总费用：41.6416
查询等级：1
行
返回
（1）
成本
I / O
|
3.50129
DELETE
（2）
41.6416
6.50129
/ ---- + --- \
3.50129 1.33235e + 07
IXSCAN表：RTM
（3）TBL_COLLECT_PSST
19.2224
3
|
1.33235e + 07
INDEX：RTM
IPSSTAT_NEW_WIN
Linux RH 4.0 running DB2 V8 FP 11.
I have a table with ~ 11M rows and running DELETE statements is really
slow. Deleting 1k rows takes more than 3 minutes. If I run select
statements on the same table, I usually fetch rows in a reasonable
time.
The table has the following description:
MACHINE_ID VARCHAR (24)
COLLECT_TIME TIMESTAMP
PROCESS_NAME VARCHAR (64)
PROCESS_PID DECIMAL (10)
These fields are the table PK. There is also and index on (MACHINE_ID,
COLLECT_TIME).
I have run db2exfmt and the explain plan seems to indicate a low
statement cost. Statistics for table and indexes are updated.
I am puzzled by actual very slow execution speeds.
Can anyone guide me trace why and where are the execution constraints?
Thanks in advance,
Original Statement:
------------------
delete
from RTM.TBL_COLLECT_PSSTAT_WIN_RTM
where MACHINE_ID=''000423B0F8F61420F1B8111A'' and COLLECT_TIME between
''2006-04-17 00:00:55.0'' and ''2006-04-17 01:00:55.0''
Optimized Statement:
-------------------
DELETE
FROM RTM.TBL_COLLECT_PSSTAT_WIN_RTM AS Q1
WHERE $RID$ IN
(SELECT $RID$
FROM RTM.TBL_COLLECT_PSSTAT_WIN_RTM AS Q2
WHERE (Q2.COLLECT_TIME <= ''2006-04-17-01.00.55.000000'') AND
(''2006-04-17-00.00.55.000000'' <= Q2.COLLECT_TIME) AND
(Q2.MACHINE_ID = ''000423B0F8F61420F1B8111A''))
Access Plan:
-----------
Total Cost: 41.6416
Query Degree: 1
Rows
RETURN
( 1)
Cost
I/O
|
3.50129
DELETE
( 2)
41.6416
6.50129
/----+---\
3.50129 1.33235e+07
IXSCAN TABLE: RTM
( 3) TBL_COLLECT_PSST
19.2224
3
|
1.33235e+07
INDEX: RTM
IPSSTAT_NEW_WIN
推荐答案
这篇关于执行删除语句的速度很慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！