Help needed with slow union join

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 Received on Wed May 31 2006 - 09:14:31 CDT