可能和数据有些关系,
1. 语句优化成:
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 > ' '2. 在SUBSCRIBER.SERV_ID 上建立索引
1. 语句优化成:
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 > ' '2. 在SUBSCRIBER.SERV_ID 上建立索引
- 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;
SELECT COUNT(*) FROM BILL.STAT_SERV_CONSUME_0406
SELECT COUNT(*) FROM BILL.STAT_SERV_CONSUME_0407
上面兩個查詢語句的結果是什麼,各花多少時間?還有就是主鍵的問題,我看0406中沒有主鍵,0407中有嗎?
update操作比较占用回滚段第二个表执行快的原因是在第一个表的基础上
SELECT CUST_ID FROM SUBSCRIBER B WHERE A.SERV_ID=B.SERV_ID内容还在缓冲区
SELECT COUNT(*) FROM BILL.STAT_SERV_CONSUME_0407
這兩句的結果是什麼?
SELECT COUNT(*) FROM BILL.STAT_SERV_CONSUME_0407两个表都是180万条记录,用时都是1秒左右
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
我觉得建临时表应该能起到一定的作用!
建立临时表:
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;
应该把它限定在较小 (一万条记录) 范围内,. 否则ORACLE处理这个事物用到很大的回退段.
程序响应慢甚至失去响应. 如果记录数上十万以上这些操作, 可以把这些SQL语句分段分次完成, 其间加上COMMIT 确认事物处理
如果还是很慢
使用
update /*+ use_hash( a , b ) */指定为hash连接方式
SELECT COUNT(*) FROM BILL.STAT_SERV_CONSUME_0407 WHERE SERV_ID is not null
这两个值是不是差别很大呀
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,即字符型,
这也是导致慢的原因之一。