| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Help needed with slow union join
sybrandb wrote:
> 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 --
>
>
For the count(*) that should be true; however, the OP said queries then used count as an example. I expect most queries would select some data and these I would expect to full scan the table so I think we are both correct as far as we can go with the data posted.
As others have posted "union all" might be an option for this view depending of if duplicates exist or if duplicates are desirable as part of the output.
 |
 |