本帖最后由 showzeal 于 2013-12-3 20:19 编辑
最近利用ROWID,原先以为能很快,没想到最后却是很慢,20w行rowid去更新1000w行的表,却要执行快3个小时
目标表有 2个压缩索引,一个非复合和一个复合的。数据范围从2011-1-1到2013-11-1,还未分区
更新语句如下
UPDATE DM_BASE_OP_REVISIT_DET_DAY A SET (REVI_BEGIN_DATE,
REVI_BEGIN_ORG)=(SELECT REVISIT_BEGIN_DATE,
REVI_BEGIN_ORG
FROM DM_BASE_OP_REVISIT_TEMP1 B
WHERE A.ROWID=B.RE_ROWID
AND B.DIM_DATE>=DATE'2013-7-1'
AND B.DIM_DATE
WHERE EXISTS(SELECT 1 FROM DM_BASE_OP_REVISIT_TEMP1 c
WHERE A.ROWID=c.RE_ROWID
AND c.DIM_DATE>=DATE'2013-7-1'
AND c.DIM_DATE
执行计划:
Plan hash value: 32013224
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 23905 | 1307K| 12466 (1)| 00:02:30 |
| 1 | UPDATE | DM_BASE_OP_REVISIT_DET_DAY | | | | |
| 2 | NESTED LOOPS | | 23905 | 1307K| 12466 (1)| 00:02:30 |
| 3 | SORT UNIQUE | | 23905 | 630K| 320 (2)| 00:00:04 |
|* 4 | TABLE ACCESS FULL | DM_BASE_OP_REVISIT_TEMP1 | 23905 | 630K| 320 (2)| 00:00:04 |
|* 5 | TABLE ACCESS BY USER ROWID| DM_BASE_OP_REVISIT_DET_DAY | 1 | 29 | 1 (0)| 00:00:01 |
|* 6 | TABLE ACCESS FULL | DM_BASE_OP_REVISIT_TEMP1 | 1 | 48 | 321 (2)| 00:00:04 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("C"."DIM_DATE">=TO_DATE(' 2013-07-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"C"."DIM_DATE"
5 - access(CHARTOROWID("C"."RE_ROWID"))
6 - filter("B"."DIM_DATE">=TO_DATE(' 2013-07-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
CHARTOROWID("B"."RE_ROWID")=:B1 AND "B"."DIM_DATE"
hh24:mi:ss'));
不过当我用merge into时,执行120多秒就ok了,前面一个是为什么会那样??要执行那么久?我给DM_BASE_OP_REVISIT_TEMP1 的re_rowid加了索引,加上去了也还是那么久