日期:2014-05-17  浏览次数:20985 次

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   =   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

如何改写     或者建立索引什么的能快点?




------解决方案--------------------
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 需要索引
语句应该只能这样了

楼上的建议不错 建立临时表
------解决方案--------------------
不懂。。。接分~~~