Re: Help needed with slow union join

From: Stephen Reid <stephen.reid_at_NOSPAMttabconnectors.com>
Date: Thu, 1 Jun 2006 09:54:59 +0100
Message-ID: <447eab50$0$2595$db0fefd9@news.zen.co.uk>

Hi

I seem to have come unstuck again. Even though the example query has become much quicker using the UNION ALL the actual query seems to take forever. The main reason behind my post was that we used to run a report that queried a sales order header and detail tables amongst others. I recently replaced theses tables (BILL_SO and BILL_HDR) with two views (C_BILL_DET and C_BILL_HDR) that joined these tables to similar tables with a small amount of data (at this point we are probably only talking of a 100 records). An example of this was the view I posted yesterday. However, the report use to take about a minute to run but now takes 20 minutes. Is there something I can do to speed this up? I will post the SQL that is used to generate te report data and the explanation for the query below. I am extremely grateful to anyone who can offer any assistance with this.

Stephen

SELECT

     AR_DOC.CUSTOMER, AR_DOC.DOC_TYPE, ITEM.PC, C_BILL_DET.ITEM,
     C_BILL_DET.DOC, AR_DOC.CREATED_DATE, MEMO.TOT_ORD_QTY,
     C_BILL_DET.SHIP_QTY, MEMO.MEMO_TYPE, C_BILL_HDR.RATE,
     C_BILL_DET.SHIP_VALUE, MEMO.BOOK_RATE, MEMO.UNIT_PRICE,
     C_BILL_DET.COST, CUS_LOC.NAME
FROM
     AR_DOC, C_BILL_HDR, CUS_LOC, C_BILL_DET, MEMO, ITEM
WHERE


     (((AR_DOC.BRANCH=C_BILL_HDR.BRANCH) AND

     (AR_DOC.DOC_TYPE=C_BILL_HDR.DOC_TYPE)) AND

     (AR_DOC.DOC=C_BILL_HDR.DOC)) AND

     ((AR_DOC.CUSTOMER=CUS_LOC.CUSTOMER) AND

     (AR_DOC.CUS_AR_LOC=CUS_LOC.CUS_LOC)) AND

     (((C_BILL_HDR.BRANCH=C_BILL_DET.BRANCH) AND

     (C_BILL_HDR.DOC_TYPE=C_BILL_DET.DOC_TYPE)) AND

     (C_BILL_HDR.DOC=C_BILL_DET.DOC)) AND

     ((((C_BILL_DET.BRANCH=MEMO.BRANCH (+)) AND

     (C_BILL_DET.DOC_TYPE=MEMO.MEMO_TYPE (+))) AND

     (C_BILL_DET.DOC_LINE=MEMO.MEMO_LINE (+))) AND

     (C_BILL_DET.DOC=MEMO.DOC (+))) AND

     ((C_BILL_DET.ITEM=ITEM.ITEM (+)) AND

     (C_BILL_DET.REVISION=ITEM.REVISION (+))) AND

     (AR_DOC.CREATED_DATE>=TO_DATE ('30-04-2006', 'DD-MM-YYYY') AND

     AR_DOC.CREATED_DATE<TO_DATE ('28-05-2006', 'DD-MM-YYYY'))
ORDER BY
     AR_DOC.CUSTOMER, ITEM.PC




OPERATION            OPTIONS              OBJECT_NAME     OBJECT_INSTANCE 

OBJECT_TYPE

-------------------- -------------------- --------------- -----------------  

---

SELECT STATEMENT

SORT                 ORDER BY

NESTED LOOPS
NESTED LOOPS
MERGE JOIN

SORT                 JOIN
NESTED LOOPS         OUTER
NESTED LOOPS         OUTER
VIEW                                      C_BILL_DET      4
UNION-ALL
TABLE ACCESS         FULL                 BILL_SO         7
TABLE ACCESS         FULL                 BILL_CR         8
TABLE ACCESS         BY INDEX ROWID       ITEM            6
INDEX                UNIQUE SCAN          PK_ITEM 
UNIQUE
TABLE ACCESS         BY INDEX ROWID       MEMO            5
INDEX                RANGE SCAN           PK_MEMO 
UNIQUE
SORT                 JOIN
VIEW                                      C_BILL_HDR      2
UNION-ALL
TABLE ACCESS         FULL                 BILL_HDR        9
TABLE ACCESS         FULL                 BILL_HCR        10
TABLE ACCESS         BY INDEX ROWID       AR_DOC          1
INDEX                UNIQUE SCAN          PK_AR_DOC 
UNIQUE
TABLE ACCESS         BY INDEX ROWID       CUS_LOC         3
INDEX                UNIQUE SCAN          PK_CUS_LOC 

UNIQUE Received on Thu Jun 01 2006 - 03:54:59 CDT