Re: Help needed with slow union join

"Stephen Reid" <stephen.reid_at_NOSPAMttabconnectors.com> wrote in message news:447da4b4$0$2590$db0fefd9_at_news.zen.co.uk...
: Hi
:
: I am having a problem with a slow view that just union joins to similar
: tables. The view is called C_BILL_DET and joins two tables BILL_SO and
: BILL_CR. The problem is when querying the view it is extremely slow. For
: example I simple select count(*) query on each of the tables and join and
: the results were:
:
: BILL_SO took 1.234 seconds to return.
: BILL_CR took 0.016 seconds to return.
: C_BILL_DET took 79.282 seconds to return.
:
: Can anyone offer any advise on how to speed this up?
:
: The view is created by:
:
: CREATE OR REPLACE FORCE VIEW C_BILL_DET
: ("BRANCH", "DOC_TYPE", "DOC", "DOC_LINE", "ITEM",
: "REVISION", "SHIP_QTY", "SHIP_VALUE", "COST") AS
:
: SELECT
: BRANCH, DOC_TYPE, DOC, DOC_LINE,ITEM, REVISION,
: SO_SHIP_QTY, SO_SHIP_VALUE, COST
: FROM
: BILL_SO
:
: UNION
:
: SELECT
: BRANCH, DOC_TYPE, DOC, DOC_LINE,ITEM, REVISION,
: CR_SHIP_QTY, CR_SHIP_VALUE, COST
: FROM
: BILL_CR;
:
: Thanks
: Stephen
:
:

can you post the execution plans for the 3 selects?

i think you'll see the count from the UNION'd view is doing a lot more work, as it is eliminating duplicate rows, which requires sorting, where-as the counts from the tables do not require sorting, and may even be using a PK index

++ mcs Received on Wed May 31 2006 - 09:53:46 CDT