[:Help:] 查询优化求助：如何加快汇总查询的执行速度?

Question

我要按出货月份转换汇率统计销售额,用下面的统计查询竟然用了616.797秒(合10分钟),晕死了-_-#请大侠帮忙看看：要如何优化才能加快查询的执行速度：
SELECT ROUND(SUM(D.QTY*D.UNIT_PRICE* GG_PO.GF_CURR_RATE(M.ORG_ID,M.MONEY_UNIT,'USD',TO_CHAR(D.SALES_DATE,'YYYYMM'))),2)
FROM SALES_M M,SALES_D D
WHERE  M.ORG_ID=100
AND M.PERIOD>='200804'
AND M.PERIOD<='200804'
AND  M.SALES_ID = D.SALES_ID 
AND DECODE(GG_R1.GF_SEID_TYPE(D.ORG_ID,D.SE_ID),1,'P',2,'S',4,'O')='P'
AND DECODE(GG_SH.GF_SALESNO_SHIPTYPE(D.ORG_ID,D.SALES_NO),1,'a',2,'a',3,'b')='a'
AND  M.SALES_TYPE = 1
AND  M.PAY_NO <> '022'

Answer 1

贴执行计划出来看看
不会用执行计划的话
在sql plus 环境下
set autotrace on
然后执行语句，最后几行就是执行计划
说一下你表里一个月有多少数据，索引列的情况

Answer 2

SELECT ROUND (SUM (  d.qty
                   * d.unit_price
                   * gg_po.gf_curr_rate (m.org_id,
                                         m.money_unit,
                                         'USD',
                                         TO_CHAR (d.sales_date, 'YYYYMM')
                                        )
                  ),
              2
             )
  FROM sales_d d, sales_m m
 WHERE DECODE (gg_r1.gf_seid_type (d.org_id, d.se_id),
               1, 'P',
               2, 'S',
               4, 'O'
              ) = 'P'
   AND DECODE (gg_sh.gf_salesno_shiptype (d.org_id, d.sales_no),
               1, 'a',
               2, 'a',
               3, 'b'
              ) = 'a'
   AND m.sales_id = d.sales_id
   AND m.sales_type = 1
   AND m.pay_no <> '022'
   AND m.org_id = 100
   AND m.period >= '200804'
   AND m.period <= '200804'语句顺序换成这样试试看
不过以前我也有张报表，月销售统计明细报表，原来执行一次大概1分钟左右
后来财务要求改计算方式，结果也只能改成像你这样在where里调用函数来判断，执行时间延长到8分钟
不过你这是汇总应该要快点

Answer 3

謝謝樓上朋友的指點。。一個月大概2K條數據，數据量其實不算大。。
索引列:RG_ID, SALES_ID上面的數据查詢匯總的目的：其實是為了導銷售匯總報表
我的報表里有好几條那樣的查詢匯總語句，執行速度太慢了，一個多小時都導不出，暈死了-_-#

Answer 4

你的索引列基本上都没用到，就是说多数是全表扫描
贴一下你的表结构，以及你的函数内容
2K条不算多，我们现在大概一个万把条
我原先 个报表，一个月大概5万条纪录，全是拉明细，你只是汇总的不过你应该在m.period建索引，一般来说这种销售表，日期查询的是最多

Answer 5

给出执行计划，
全表扫描是肯定的了，不过看样子，你的gg_sh.gf_salesno_shiptype 和
gg_r1.gf_seid_type效率不怎么高。。

Answer 6

估计是索引不足，另外尽量不要在 where 子句的列名称上使用函数，这样会阻止生成使用索引的执行计划。

Answer 7

where里条件在前,连接在后可能快一些

Answer 8

下面是我調用的函數：--AND DECODE(GG_SH.GF_SALESNO_SHIPTYPE(D.ORG_ID,D.SALES_NO),1,'a',2,'a',3,'b')='a'
--==>FUNCTION GF_SALESNO_SHIPTYPE (P_ORGID              NUMBER,                      
                              P_SALESNO            VARCHAR2)                     
RETURN VARCHAR2 IS
V_SHIPTYPE VARCHAR2(1);
V_SHIPID SP_SHIP_M.SHIP_ID%TYPE;
BEGIN

BEGIN   
   
     SELECT COLUMN4 INTO V_SHIPID
     FROM SH_SALES_M 
     WHERE ORG_ID = P_ORGID
     AND SALES_NO = P_SALESNO;                   
   
   BEGIN
   SELECT SHIP_TYPE INTO V_SHIPTYPE
     FROM   SP_SHIP_M
     WHERE  ORG_ID = P_ORGID
     AND    SHIP_ID = V_SHIPID;
     EXCEPTION WHEN NO_DATA_FOUND THEN
      NULL;
   END;
EXCEPTION WHEN NO_DATA_FOUND THEN
   NULL;
END;

RETURN V_SHIPTYPE;
END;

Answer 9

下面是我的執行計划：
原來我的明細表TABLE ACCESS (FULL) OF 'SALES_D' (TABLE) 是全表掃描。。
給這個表加上索引應該能解決問題，謝謝樓上各位朋友:)
set autotrace onExecute Plan
------------------------------
0  SELECT STATEMENT Optimizer=ALL_ROWS (Cost=219 Card=1 Bytes=77)
1  0 SORT (AGGREGATE)   
2  1 NESTED LOOPS (Cost=219 Card=4 Bytes=308)
3  2 TABLE ACCESS (FULL) OF 'SALES_D' (TABLE) (Cost=215 Card=4 Bytes=204)   
4  2 TABLE ACCESS (BY INDEX ROWID) OF 'SALES_M' (TABLE) (Cost=1 Card=1 Bytes=26)   
5  4 INDEX (UNIQUE SCAN) OF 'PK_AR_SALES_M' (INDEX (UNIQUE)) (Cost=0 Card=1)

Answer 10

事實上我的明細表SALES_D是有索引的。。
-- Create/Recreate primary, unique and foreign key constraints 
alter table SALES_D
  add constraint PK_SALES_D primary key (ORG_ID,SALES_ID,ITEM_ID)
  using index 
  tablespace SADB_INDEX02
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );

Answer 11

其它的就不说了。
但是楼主有一些陋习还是改改较好。
明明可以等于的就不要用一个>=和一个<=来处理。
明明可以直接比较的，就不要加上多于的decode处理了。
光这些函数处理也能耗费不少时间。
此外如果你有权限， 最好还是把函数转换为表格处理（多对多记录rate），这些关联比函数来的更快些，因为你的数据并不大，不是几百几千万。
最后，decode中函数不应该使用，也应该直接在表格中加列，预先计算结果。

Answer 12

結貼了。。俺是菜鳥。。第一次學會看執行計划。 
不会用执行计划的话 
在sql plus 环境下 
SET autotrace trace explain
然后执行语句，最后几行就是执行计划 
收獲很大，謝謝各位參與指教，謝謝~