SQL调优，帮忙 谢谢
update broad_band a set RELATION_ACC_NBR=(
select b.acc_nbr from serv_cust b where a.acct_id=b.acct_id
and b.serv_state not IN ( '2HB ', '2HF ', '2HX ', '2IX ', '2PX ', '2SX ')
AND product_id IN(101070000,21500005, 101061200,101060200,102040000,
101060000,21600021,21200012,103020000,101030000,102020000, 600000027,
600000028,600000035,21600002,21600016,101060900,101061100,20400019,20400020,21300009,600000011) and rownum <2)
where exists(
select 1 FROM serv_cust b where a.acct_id=b.acct_id
and b.serv_state not IN ( '2HB ', '2HF ', '2HX ', '2IX ', '2PX ', '2SX ')
and product_id IN(101070000,21500005, 101061200,101060200,102040000,
101060000,21600021,21200012,103020000,101030000,102020000, 600000027,
600000028,600000035,21600002,21600016,101060900,101061100,20400019,20400020,
21300009,600000011) and rownum <2)
serv_cust 190w broad_band 10w 的数据
执行计划
UPDATE STATEMENT, GOAL = CHOOSECost=1289Cardinality=1258Bytes=38998
UPDATEObject owner=INTERObject name=BROAD_BAND
FILTER
TABLE ACCESS FULLObject owner=INTERObject name=BROAD_BANDCost=31Cardinality=1258Bytes=38998
COUNT STOPKEY
TABLE ACCESS BY INDEX ROWIDObject owner=INTERObject name=SERV_CUSTCost=1Cardinality=433152Bytes=3898368
INDEX RANGE SCANObject owner=INTERObject name=IDX_SC_8Cost=1Cardinality=1
COUNT STOPKEY
TABLE ACCESS BY INDEX ROWIDObject owner=INTERObject name=SERV_CUSTCost=1Cardinality=433152Bytes=7363584
INDEX RANGE SCANObject owner=INTERObject name=IDX_SC_8Cost=1Cardinality=1
如何改写 或者建立索引什么的能快点？
[解决办法]
1。update语句是否错误？where中应该有set值表中连接关系。
2。TABLE ACCESS FULLObject owner=INTERObject name=BROAD_BAND 是因为缺索引还是整个语句有错引起的。
3。数据量太大的时候，我们应该将语句改成pl/sql段，先执行限定性语句，插入到临时表中，大表不要做连接。
[解决办法]
broad_band的 acct_id 需要索引
serv_cust 的acct_id serv_state product_id 需要索引
语句应该只能这样了
楼上的建议不错 建立临时表
[解决办法]
不懂。。。接分~~~
