Db Sequential Read

From: walid alkaakati <walid_alkaakati_at_yahoo.com>
Date: Fri, 19 Jan 2007 23:03:16 -0800 (PST)
Message-ID: <20070120070316.56597.qmail@web61122.mail.yahoo.com>

Hi ,    

  I need your help on tunning this query :    

  SELECT a.ARTICLE_CODE, b.BRAND, DECODE(a.INVOICE_DATE,NULL,TO_DATE(NULL,'MMDDYYYY'),TO_DATE(TO_CHAR(TRUNC(a.INVOICE_DATE,'YY'),'RR')||'01','RRMM')), SUM(FG_SALES_VD.PC_AMT) FROM FG_ARTICLE_VD b, FG_SALES_VD a
WHERE ( ( b.ARTICLE_CODE = a.ARTICLE_CODE AND b.COMP_CODE = a.COMP_CODE ) ) GROUP BY a.ARTICLE_CODE,a.BRAND, DECODE(a.INVOICE_DATE,NULL,TO_DATE(NULL,'MMDDYYYY'),TO_DATE(TO_CHAR(TRUNC(a.INVOICE_DATE,'YY'),'RR')||'01','RRMM'));    

  Explain plan:    

  SELECT STATEMENT
  SORT GROUP BY
    HASH JOIN

      VIEW  FG_SALES_VD
        SORT GROUP BY 
          HASH JOIN  
            TABLE ACCESS FULL FGINV
            TABLE ACCESS FULL FGLIINV
      INDEX FAST FULL SCAN FGART_IDX_SEC
   

  The query is taking about 3 minutes when I join article view to the sales view, i did a trace and found that the following lines :   WAIT #1: nam='db file sequential read' ela= 8228 p1=5 p2=81898 p3=1 WAIT #1: nam='db file sequential read' ela= 7187 p1=5 p2=82226 p3=1 WAIT #1: nam='db file sequential read' ela= 4803 p1=5 p2=82288 p3=1   ........
  My question how i minimize the time needed for sequential read so that blocks are read quickly ? or how to increase efficieny of hash join ?  

  Wating for your help .  

---

8:00? 8:25? 8:40? Find a flick in no time  with theYahoo! Search movie showtime shortcut.
--

http://www.freelists.org/webpage/oracle-l Received on Sat Jan 20 2007 - 01:03:16 CST