Re: Help needed with slow union join

Stephen Reid wrote:
> 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
Stephen, a select count(*) and selecting data are not the same query and may have different plans. Why waste time tuning the wrong query?

If the old SQL works fast why are you trying to replace it or parts of it with views?

You should compare the explain plan for the original query to the explain plan for the new version. This should help you determine where the extra time cost is located. By unioning two tables together in a view you may have cut the CBO off from a more efficient access path to some of the data preventing these rows being used as filters in a join operation early in the stream and greatly increasing the work that has to be done to complete the plan.

HTH -- Mark D Powell -- Received on Thu Jun 01 2006 - 11:07:45 CDT