Re: Help needed with slow union join

Mark D Powell wrote:
> 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 --

Actually, if there is a PK on the table, in the absence of a where clause, CBO will perform an INDEX_FFS instead of a FULL.

-- 
Sybrand Bakker
Senior Oracle DBA