急,急,UPDATE执行时间过长。
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
);
请问高手是何原因?