急，急，UPDATE执行时间过长。

[zhangcyu]

UPDATE BILL.STAT_SERV_CONSUME_0406 A
SET CUST_ID = (SELECT CUST_ID FROM SUBSCRIBER B WHERE A.SERV_ID=B.SERV_ID)
WHERE A.SERV_ID is not null

这条语句执行10多个小时，还未执行完，
而同样的语句：
UPDATE BILL.STAT_SERV_CONSUME_0407 A
SET CUST_ID = (SELECT CUST_ID FROM SUBSCRIBER B WHERE A.SERV_ID=B.SERV_ID)
WHERE A.SERV_ID is not null
只要一分钟就可以搞定
STAT_SERV_CONSUME_0406和STAT_SERV_CONSUME_0407都是100多万条记录，表结构一样，索引一样：
-- Create table
create table STAT_SERV_CONSUME_0406
(
SERV_ID NUMBER,
CUST_ID VARCHAR2(12),
TICKET_TYPE VARCHAR2(4),
PAGE NUMBER,
DURATION NUMBER,
CHARGE NUMBER,
COUNTS NUMBER
)
tablespace TBS_BILL
pctfree 10
pctused 40
initrans 1
maxtrans 255
storage
(
initial 50M
minextents 1
maxextents unlimited
);
-- Create/Recreate indexes
create index IND_SERV_CMS_CST_ID_0406 on STAT_SERV_CONSUME_0406 (CUST_ID)
tablespace TBS_BILL
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 20M
minextents 1
maxextents unlimited
);
create index IND_SERV_CMS_SRV_ID_0406 on STAT_SERV_CONSUME_0406 (SERV_ID)
tablespace TBS_BILL
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 20M
minextents 1
maxextents unlimited
);
请问高手是何原因？

[zhangcyu]

SUBSCRIBER.SERV_ID上已经有索引了

[jxc]

这两个表都没有主键吗?

[zhangcyu]

SUBSCRIBER表结构和主键见下，stat_serv_consume_0406没有主键：
- Create table
create table BASEDBA.SUBSCRIBER
(
SUBS_ID VARCHAR2(11) not null,
SEQ NUMBER(3) not null,
SUBS_CODE VARCHAR2(16),
CUST_ID VARCHAR2(11) not null,
SERV_ID VARCHAR2(11) not null,
CREDIT_GRADE_ID VARCHAR2(5),
AREA_ID VARCHAR2(8),
CREA_DATE DATE not null,
STATE CHAR(1) not null,
MODI_DATE DATE not null
)
tablespace TBS_SERV
pctfree 10
initrans 3
maxtrans 255
storage
(
initial 128K
minextents 1
maxextents unlimited
);
-- Create/Recreate primary, unique and foreign key constraints
alter table BASEDBA.SUBSCRIBER
add constraint PK_SUBSCRIBER primary key (SUBS_ID,SEQ)
using index
tablespace TBS_SERV_IDX
pctfree 10
initrans 3
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
alter table BASEDBA.SUBSCRIBER
add constraint AK_AK_SUBSCRIBER_SUBSCRIB unique (CUST_ID,SERV_ID)
disable;
alter table BASEDBA.SUBSCRIBER
add constraint FK_SUBSCRIB_REFERENCE_SERV foreign key (SERV_ID)
references BASEDBA.SERV (SERV_ID);
-- Create/Recreate indexes
create index BASEDBA.IDX_SUBSCRIBER_CUST_ID on BASEDBA.SUBSCRIBER (CUST_ID)
tablespace TBS_SERV_IDX
pctfree 10
initrans 3
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
create index BASEDBA.IDX_SUBSCRIBER_SERV_ID on BASEDBA.SUBSCRIBER (SERV_ID)
tablespace TBS_SERV_IDX
pctfree 10
initrans 3
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
-- Grant/Revoke object privileges
grant select, insert, update, delete on BASEDBA.SUBSCRIBER to BILL;
grant select, insert, update, delete, references, alter, index on BASEDBA.SUBSCRIBER to PUBLIC;

[baojianjun]

樓主嘗試一下以下語句：
SELECT COUNT(*) FROM BILL.STAT_SERV_CONSUME_0406
SELECT COUNT(*) FROM BILL.STAT_SERV_CONSUME_0407
上面兩個查詢語句的結果是什麼，各花多少時間？

還有就是主鍵的問題，我看0406中沒有主鍵，0407中有嗎?

[wylwyl1130]

你创建一个主健或者一个唯一索引吧
update操作比较占用回滚段

第二个表执行快的原因是在第一个表的基础上
SELECT CUST_ID FROM SUBSCRIBER B WHERE A.SERV_ID=B.SERV_ID内容还在缓冲区

[zhangcyu]

0407也没有主键

[baojianjun]

SELECT COUNT(*) FROM BILL.STAT_SERV_CONSUME_0406
SELECT COUNT(*) FROM BILL.STAT_SERV_CONSUME_0407
這兩句的結果是什麼？

[zhangcyu]

SELECT COUNT(*) FROM BILL.STAT_SERV_CONSUME_0406
SELECT COUNT(*) FROM BILL.STAT_SERV_CONSUME_0407

两个表都是180万条记录，用时都是1秒左右

[baojianjun]

不好意思，是要看這個的結果
SELECT COUNT(*) FROM BILL.STAT_SERV_CONSUME_0406 WHERE SERV_ID is not null
SELECT COUNT(*) FROM BILL.STAT_SERV_CONSUME_0407 WHERE SERV_ID is not null

[zhangcyu]

运行结果和前者基本类似

[baojianjun]

那就是wylwyl1130(落雪山林)所說的原因了

[zhangcyu]

我测试过，和缓冲没有关系，不敢哪个语句先运行，0406都要执行很久
另外各位提出增加主键和唯一索引的办法也行不通，因为我们这个系统是针对全省电信的一个系统，各个地市版本要一样

[caven]

在运行INSERT, DELETE 和 UPDATE 语句前最好估算一下可能操作的记录范围,
应该把它限定在较小 (一万条记录) 范围内,. 否则ORACLE处理这个事物用到很大的回退段.
程序响应慢甚至失去响应. 如果记录数上十万以上这些操作, 可以把这些SQL语句分段分次完成, 其间加上COMMIT 确认事物处理

[jxc]

可以尝试两个语句多次,消除缓冲的影响后,看执行速度还有没有差别.

[tyrone98]

set autotrace on 看看两个更新的执行方式是否一样，可以先重新分析一下你的STAT_SERV_CONSUME_0407 表.
如果还是很慢
使用
update /*+ use_hash( a , b ) */指定为hash连接方式

[fieldsun]

SELECT COUNT(*) FROM BILL.STAT_SERV_CONSUME_0406 WHERE SERV_ID is not null
SELECT COUNT(*) FROM BILL.STAT_SERV_CONSUME_0407 WHERE SERV_ID is not null
这两个值是不是差别很大呀

[sunjet2000]

我的解决办法：
我觉得建临时表应该能起到一定的作用！
建立临时表：
Create global temporary table temp1
as
(
SELECT CUST_ID, SERV_ID
FROM SUBSCRIBER B, STAT_SERV_CONSUME_0406 A
WHERE A.SERV_ID=B.SERV_ID
);
UPDATE BILL.STAT_SERV_CONSUME_0406 A
SET CUST_ID =
(
select * from temp1 C where A.SERV_ID = C.SERV_ID
)
WHERE A.SERV_ID is not null;
commit;
Create global temporary table temp2
as
(
SELECT CUST_ID, SERV_ID
FROM SUBSCRIBER B, STAT_SERV_CONSUME_0407 A
WHERE A.SERV_ID=B.SERV_ID
);
UPDATE BILL.STAT_SERV_CONSUME_0406 A
SET CUST_ID =
(
select * from temp1 C where A.SERV_ID = C.SERV_ID
)
WHERE A.SERV_ID is not null;
commit;

[北极海hein]

试试在udate时开始事务指定一个大rollback segment看看.

[zhangcyu]

怎么指定一个大的回滚段

[zgh2003]

UPDATE BILL.STAT_SERV_CONSUME_0406 A
SET CUST_ID = (SELECT CUST_ID FROM SUBSCRIBER B WHERE A.SERV_ID=B.SERV_ID)
WHERE A.SERV_ID is not null
中的“WHERE A.SERV_ID is not null”肯定会导致全表扫描，所以速度会变慢。

测试一下下面语句，看看能不能得到改善。
UPDATE BILL.STAT_SERV_CONSUME_0406 A
SET CUST_ID = (SELECT CUST_ID FROM SUBSCRIBER B WHERE A.SERV_ID=to_number(B.SERV_ID,999999));
当然这必须是在表SUBSCRIBER记录较少的前提下。

句中用到to_number函数，是因为你的表STAT_SERV_CONSUME_0406中的字段SERV_ID是NUMBER型，
而表SUBSCRIBER 中是SERV_ID VARCHAR2(11) not null，即字符型，
这也是导致慢的原因之一。