批量修改表记录,根据要修改的表的列来作为子查询(求和语句）的条件，结果速度相当慢。。不符合客户的要求。。请各位帮忙。。

Question

PRODUCT_ID=RP_HQ_IO_DAY.PRODUCT_ID),                                     
          AGENT01_SALE_PREMONTH_NUM=(SELECT SUM(NVL(SALE_OUT_NUM,0))              
                                   FROM   WM_WH_REMAIN_DAY
                                   WHERE CUSTOMER_TYPE='01'
                                   AND    DAYS>=VPREPERIOD_FIRSTDAY
                                   AND    DAYS<=VPREPERIOD_DATE
                                   AND    PRODUCT_ID=RP_HQ_IO_DAY.PRODUCT_ID),
     AGENT02_SALE_DAY_NUM=(SELECT SUM(NVL(SALE_OUT_NUM,0))              
                                   FROM   WM_WH_REMAIN_DAY
                                   WHERE  CUSTOMER_TYPE='02'
                                   AND    DAYS=VDATE
                                   AND    PRODUCT_ID=RP_HQ_IO_DAY.PRODUCT_ID), 
          AGENT02_SALE_MONTH_NUM=(SELECT SUM(NVL(SALE_OUT_NUM,0))              
                                   FROM   WM_WH_REMAIN_DAY
                                   WHERE  CUSTOMER_TYPE='02'
                                   AND    DAYS>=VPERIOD_FIRSTDAY
                                   AND    DAYS<=VDATE
                                   AND    PRODUCT_ID=RP_HQ_IO_DAY.PRODUCT_ID),                                     
          AGENT02_SALE_PREMONTH_NUM=(SELECT SUM(NVL(SALE_OUT_NUM,0))              
                                   FROM   WM_WH_REMAIN_DAY
                                   WHERE CUSTOMER_TYPE='02'
                                   AND    DAYS>=VPREPERIOD_FIRSTDAY
                                   AND    DAYS<=VPREPERIOD_DATE
                                   AND    PRODUCT_ID=RP_HQ_IO_DAY.PRODUCT_ID),
        RETAIL_SALE_DAY_NUM=(SELECT SUM(NVL(SALE_OUT_NUM,0))              
                                   FROM   CH_RETAIL_REMAIN_DAY
                                   WHERE  CUSTOMER_TYPE='03'
                                   AND    DAYS=VDATE
                                   AND    PRODUCT_ID=RP_HQ_IO_DAY.PRODUCT_ID), 
          RETAIL_SALE_MONTH_NUM=(SELECT SUM(NVL(SALE_OUT_NUM,0))              
                                   FROM   CH_RETAIL_REMAIN_DAY
                                   WHERE  CUSTOMER_TYPE='03'
                                   AND    DAYS>=VPERIOD_FIRSTDAY
                                   AND    DAYS<=VDATE
                                   AND    PRODUCT_ID=RP_HQ_IO_DAY.PRODUCT_ID),                                     
          RETAIL_SALE_PREMONTH_NUM=(SELECT SUM(NVL(SALE_OUT_NUM,0))              
                                   FROM   CH_RETAIL_REMAIN_DAY
                                   WHERE CUSTOMER_TYPE='03'
                                   AND    DAYS>=VPREPERIOD_FIRSTDAY
                                   AND    DAYS<=VPREPERIOD_DATE
                                   AND    PRODUCT_ID=RP_HQ_IO_DAY.PRODUCT_ID),
     HQ_REMAIN_NUM= (SELECT SUM(NVL(REMAIN_NUM,0))              
                                   FROM   WM_WH_REMAIN_DAY
                                   WHERE  CUSTOMER_NO IS NULL
                                   AND    DAYS=VDATE
                                   AND    PRODUCT_ID=RP_HQ_IO_DAY.PRODUCT_ID), 
       AGENT01_REMAIN_NUM= (SELECT SUM(NVL(REMAIN_NUM,0))              
                                   FROM   WM_WH_REMAIN_DAY
                                   WHERE  CUSTOMER_TYPE='01'
                                   AND    DAYS=VDATE
                                   AND    PRODUCT_ID=RP_HQ_IO_DAY.PRODUCT_ID), 
        AGENT02_REMAIN_NUM= (SELECT SUM(NVL(REMAIN_NUM,0))              
                                   FROM   WM_WH_REMAIN_DAY
                                   WHERE  CUSTOMER_TYPE='02'
                                   AND    DAYS=VDATE
                                   AND    PRODUCT_ID=RP_HQ_IO_DAY.PRODUCT_ID), 
             RETAIL_REMAIN_NUM=(SELECT SUM(NVL(REMAIN_NUM,0))              
                                   FROM   CH_RETAIL_REMAIN_DAY
                                   WHERE  CUSTOMER_TYPE='03'
                                   AND    DAYS=VDATE
                                   AND    PRODUCT_ID=RP_HQ_IO_DAY.PRODUCT_ID)
     WHERE  RECORD_TYPE=VRECORD_LEVEL01
AND    CREATE_USER=VUSER_ID;其中,1.RP_HQ_IO_DAY是要更新的表..根据条件要更新的数据大约有900条, WM_WH_REMAIN,CH_RETIAL_REMAIN 为仓库、零售商的月结表，总数据分别为：6000，9000条，
WM_WH_REMAIN_DAY,CH_RETIAL_REMAIN_DAY 为仓库、零售商的月结表，总数据分别为：140000，160000条.. 2.日月结表的索引为days,months（日期与月份）在查询条件中都用到了，需要更新的列要22列。。3。结果完成这个更新花了将近半个小时
 我以为设计思路还是比较有效的。。但结果不理想请位：数据与数据库本身的配置哪一些方面有关。。可以提高查询的速度。。小弟在此感谢了。。

Answer 1

首先是对于你sql的优化.看看你的索引使用情况.
set autotrace on;

Answer 2

set autotrace on;
是什么意思?如何看索引使用情况？

Answer 3

在sqlplus里面执行以上命令.或者在pl/sql developer里面按F5都可以查看.

Answer 4

在sqlplus里面执行以上命令.或者在pl/sql developer里面按F5都可以查看.