Help with slow union all

From: Stephen Reid <stephen.reid_at_NOSPAMttabconnectors.com>
Date: Tue, 13 Jun 2006 11:15:12 +0100
Message-ID: <448e902c$0$30726$fa0fcedb@news.zen.co.uk>

A few weeks back I posted reagrding a slow union all join. I have since revisited the issue and taking on board the previous advice have carried out explain plans for each query.

The problem I had was I had a query on sales order tables that used to take a couple of minutes to run. The user wanted contract orders to be added to the query and these were stored in different tables to the sales orders. I carried out union all joins in views and then applied the query to the views. Unfortunately it now takes 30 minutes to run.

I apologise for the large post, I am just trying to provide enough information for someone to help me out.

Any help will be greatly appreciated.

Thanks
Stephen

The original query was:

SELECT
C_STD_COST.MAT, C_STD_COST.LAB, C_STD_COST.OHD, ITEM.PC, BILL_HDR.RATE, BILL_SO.SO_SHIP_QTY, BILL_SO.SO_SHIP_EXT_AMT, AR_DOC.CUSTOMER, BILL_SO.AR_DOC,
CUS_LOC.NAME, AR_DOC.CREATED_DATE, MEMO.TOT_ORD_QTY, MEMO.UNIT_PRICE, MEMO.BOOK_RATE, AR_DOC.AR_DOC_TYPE, MEMO.MEMO_TYPE, BILL_SO.ITEM FROM
AR_DOC, BILL_HDR, CUS_LOC, BILL_SO, ITEM, MEMO, C_STD_COST WHERE

 AR_DOC.AR_CCN=BILL_HDR.AR_CCN AND
 AR_DOC.AR_DOC_TYPE=BILL_HDR.AR_DOC_TYPE AND
 AR_DOC.AR_DOC=BILL_HDR.AR_DOC AND
 AR_DOC.CUSTOMER=CUS_LOC.CUSTOMER AND
 AR_DOC.CUS_AR_LOC=CUS_LOC.CUS_LOC AND
 BILL_HDR.AR_CCN=BILL_SO.AR_CCN AND
 BILL_HDR.AR_DOC_TYPE=BILL_SO.AR_DOC_TYPE AND
 BILL_HDR.AR_DOC=BILL_SO.AR_DOC AND
 BILL_SO.ITEM=ITEM.ITEM (+) AND
 BILL_SO.REVISION=ITEM.REVISION (+) AND
 BILL_SO.AR_DOC=MEMO.AR_DOC (+) AND
 BILL_SO.AR_DOC_LINE=MEMO.MEMO_LINE (+) AND

BILL_SO.AR_CCN=MEMO.SALES_CCN (+) AND
BILL_SO.AR_DOC_TYPE=MEMO.MEMO_TYPE (+) AND ITEM.ITEM=C_STD_COST.ITEM (+) AND
ITEM.REVISION=C_STD_COST.REVISION (+) AND AR_DOC.CREATED_DATE>=TO_DATE ('30-05-2006 00:00:00', 'DD-MM-YYYY HH24:MI:SS') AND
AR_DOC.CREATED_DATE<TO_DATE ('14-06-2006 00:00:00', 'DD-MM-YYYY HH24:MI:SS')
ORDER BY
AR_DOC.CUSTOMER, ITEM.PC The explain plan showed:

SELECT STATEMENT

SORT                ORDER BY
MERGE JOIN          OUTER
SORT                JOIN
NESTED LOOPS        OUTER

NESTED LOOPS
NESTED LOOPS
NESTED LOOPS

NESTED LOOPS        OUTER
TABLE ACCESS        FULL              BILL_SO       4
TABLE ACCESS        BY INDEX ROWID    ITEM          5
INDEX               UNIQUE SCAN       PK_ITEM           UNIQUE
TABLE ACCESS        BY INDEX ROWID    BILL_HDR      2
INDEX               UNIQUE SCAN       PK_BILL_HDR       UNIQUE
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
TABLE ACCESS        BY INDEX ROWID    MEMO          6
INDEX               RANGE SCAN        PK_MEMO           UNIQUE
SORT                JOIN
TABLE ACCESS        FULL              COST_ITM      8

The views I created were:

CREATE VIEW C_BILL_DET AS
SELECT
AR_CCN, AR_DOC_TYPE, AR_DOC, AR_DOC_LINE, ITEM, REVISION, SO_SHIP_QTY, SO_SHIP_EXT_AMT, COST1 FROM
BILL_SO
UNION ALL
SELECT
AR_CCN, AR_DOC_TYPE, AR_DOC, AR_DOC_LINE, ITEM, REVISION, SHIP_QTY, SHIP_EXT_AMT, COST1 FROM
CR_BLSH; CREATE VIEW C_BILL_HDR AS
SELECT
AR_CCN, AR_DOC_TYPE, AR_DOC, RATE
FROM
BILL_HDR
UNION ALL
SELECT
AR_CCN, AR_DOC_TYPE, AR_DOC, RATE
FROM
CR_BLHD; The new query is:

SELECT
C_STD_COST.MAT, C_STD_COST.LAB, C_STD_COST.OHD, ITEM.PC, C_BILL_HDR.RATE, C_BILL_DET.SO_SHIP_QTY, C_BILL_DET.SO_SHIP_EXT_AMT, AR_DOC.CUSTOMER, C_BILL_DET.AR_DOC,
CUS_LOC.NAME, AR_DOC.CREATED_DATE, MEMO.TOT_ORD_QTY, MEMO.UNIT_PRICE, MEMO.BOOK_RATE, AR_DOC.AR_DOC_TYPE, MEMO.MEMO_TYPE, C_BILL_DET.ITEM FROM
AR_DOC, C_BILL_HDR, CUS_LOC, C_BILL_DET, ITEM, MEMO, C_STD_COST WHERE

 AR_DOC.AR_CCN=C_BILL_HDR.AR_CCN AND
 AR_DOC.AR_DOC_TYPE=C_BILL_HDR.AR_DOC_TYPE AND
 AR_DOC.AR_DOC=C_BILL_HDR.AR_DOC AND
 AR_DOC.CUSTOMER=CUS_LOC.CUSTOMER AND
 AR_DOC.CUS_AR_LOC=CUS_LOC.CUS_LOC AND
 C_BILL_HDR.AR_CCN=C_BILL_DET.AR_CCN AND
 C_BILL_HDR.AR_DOC_TYPE=C_BILL_DET.AR_DOC_TYPE AND
 C_BILL_HDR.AR_DOC=C_BILL_DET.AR_DOC AND
 C_BILL_DET.ITEM=ITEM.ITEM (+) AND
 C_BILL_DET.REVISION=ITEM.REVISION (+) AND
 C_BILL_DET.AR_DOC=MEMO.AR_DOC (+) AND
 C_BILL_DET.AR_DOC_LINE=MEMO.MEMO_LINE (+) AND

C_BILL_DET.AR_CCN=MEMO.SALES_CCN (+) AND C_BILL_DET.AR_DOC_TYPE=MEMO.MEMO_TYPE (+) AND ITEM.ITEM=C_STD_COST.ITEM (+) AND
ITEM.REVISION=C_STD_COST.REVISION (+) AND AR_DOC.CREATED_DATE>=TO_DATE ('30-05-2006 00:00:00', 'DD-MM-YYYY HH24:MI:SS') AND
AR_DOC.CREATED_DATE<TO_DATE ('14-06-2006 00:00:00', 'DD-MM-YYYY HH24:MI:SS')
ORDER BY
AR_DOC.CUSTOMER, ITEM.PC The explain plan is:

SELECT STATEMENT

SORT                ORDER BY
MERGE JOIN          OUTER
SORT                JOIN

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      9
TABLE ACCESS        FULL              CR_BLSH      10
TABLE ACCESS        BY INDEX ROWID    ITEM         5
INDEX               UNIQUE SCAN       PK_ITEM           UNIQUE
TABLE ACCESS        BY INDEX ROWID    MEMO         6
INDEX               RANGE SCAN        PK_MEMO           UNIQUE
SORT                JOIN
VIEW                                  C_BILL_HDR   2
UNION-ALL
TABLE ACCESS        FULL              BILL_HDR     11
TABLE ACCESS        FULL              CR_BLHD      12
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
SORT                JOIN
TABLE ACCESS        FULL              COST_ITM     8

Received on Tue Jun 13 2006 - 05:15:12 CDT