Re: Help needed with slow union join

Stephen Reid wrote:
> 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

Steve, there is no where clause so the CBO has only one option to obtain the data: full table scan. A full table scan takes however long it takes to read all the blocks in the table. Period.

Now a view like this is normally written to perform the join (or union in this case) of the data. It is generally expected that the query against the view will provide filter conditions hopefully on indexed columns. The when the CBO gets the query it will rewrite the view to include the filter condition in each query that makes up the view. Again if the referenced columns are indexed then the result may well be obtained very quickly.

select * from view where branch = 'value1' and item = 'item1' results in

select * from table1 where branch = value1 and item = item1 union
select * from table2 where branch = value1 and item = item1

The resulting plan will hopfully show indexed access to the tables in the view. The plan will depend on the filter condition provided, the nature of the view itself, the statistics, how much data is in each table, etc...

Look at an explan plan for the view without a filter condition then explain a queries against the view with various where clause conditions that can be expected.

If no where clause is going to be provided then there is not a lot of statement level tuning you can do where no filter condition on the data exists. You need it all.

HTH -- Mark D Powell -- Received on Wed May 31 2006 - 10:04:20 CDT