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 = CHOOSE Cost=1289 Cardinality=1258 Bytes=38998
UPDATE Object owner=INTER Object name=BROAD_BAND
FILTER
TABLE ACCESS FULL Object owner=INTER Object name=BROAD_BAND Cost=31 Cardinality=1258 Bytes=38998
COUNT STOPKEY
TABLE ACCESS BY INDEX ROWID Object owner=INTER Object name=SERV_CUST Cost=1 Cardinality=433152 Bytes=3898368
INDEX RANGE SCAN Object owner=INTER Object name=IDX_SC_8 Cost=1 Cardinality=1
COUNT STOPKEY
TABLE ACCESS BY INDEX ROWID Object owner=INTER Object name=SERV_CUST Cost=1 Cardinality=433152 Bytes=7363584
INDEX RANGE SCAN Object owner=INTER Object name=IDX_SC_8 Cost=1 Cardinality=1 如何改写 或者建立索引什么的能快点?
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 = CHOOSE Cost=1289 Cardinality=1258 Bytes=38998
UPDATE Object owner=INTER Object name=BROAD_BAND
FILTER
TABLE ACCESS FULL Object owner=INTER Object name=BROAD_BAND Cost=31 Cardinality=1258 Bytes=38998
COUNT STOPKEY
TABLE ACCESS BY INDEX ROWID Object owner=INTER Object name=SERV_CUST Cost=1 Cardinality=433152 Bytes=3898368
INDEX RANGE SCAN Object owner=INTER Object name=IDX_SC_8 Cost=1 Cardinality=1
COUNT STOPKEY
TABLE ACCESS BY INDEX ROWID Object owner=INTER Object name=SERV_CUST Cost=1 Cardinality=433152 Bytes=7363584
INDEX RANGE SCAN Object owner=INTER Object name=IDX_SC_8 Cost=1 Cardinality=1 如何改写 或者建立索引什么的能快点?
2。TABLE ACCESS FULLObject owner=INTERObject name=BROAD_BAND 是因为缺索引还是整个语句有错引起的。
3。数据量太大的时候,我们应该将语句改成pl/sql段,先执行限定性语句,插入到临时表中,大表不要做连接。
serv_cust 的acct_id serv_state product_id 需要索引
语句应该只能这样了楼上的建议不错 建立临时表