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

"Stephen Reid" <stephen.reid@NOSPAMttabconnectors.com> wrote in message
news:447da4b4$0$2590$db0fefd9@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

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 --

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

If there is no overlap in the data between the 2 selects, try using
UNION ALL instead of UNION.

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 --
>
> 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

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.

-- Mark D Powell --

"Vince" <vinnyop@yahoo.com> wrote:

> If there is no overlap in the data between the 2 selects, try using
> UNION ALL instead of UNION.


What is the technical rationale for doing such a thing?


Paul...


--

plinehan __at__ yahoo __dot__ __com__

XP Pro, SP 2,

Oracle, 9.2.0.1.0 (Enterprise Ed.)
Interbase 6.0.1.0;

When asking database related questions, please give other posters
some clues, like operating system, version of db being used and DDL.
The exact text and/or number of error messages is useful (!= "it didn't work!").
Thanks.

Furthermore, as a courtesy to those who spend
time analysing and attempting to help, please
do not top post.

Paul (paul@see.my.sig.com) wrote:


: "Vince" <vinnyop@yahoo.com> wrote:

: > If there is no overlap in the data between the 2 selects, try using
: > UNION ALL instead of UNION.


: What is the technical rationale for doing such a thing?

UNION removes duplicates whereras UNION ALL does not remove duplicates.

Oracle cannot know whether there are any duplicates except by removing
them, so it always goes through the process of removing duplicates even if
the tables can never contain duplicates.

There are various ways to remove duplicates, but generally speaking Oracle
either has to sort the two tables or it has to do an indexed lookup of
every value from one table in the other table.

If you know the two tables do not have duplicates, or if you do not care
that the result might have duplicates, then you can avoid all the above
work by using UNION ALL.

Notice that (some what ironically) the UNION forces the removal of
duplicates that come from the _same_ table even though the two tables may
have nothing in common. It's all entirely logical, but may be unexpected
if you haven't had to think about it before.

Thanks for all the replies. This is what I had done and it the example
count(*) query no takes just a few seconds. I am yet to test how much of an
impact this has had on the actual queries.

Thanks again
Stephen
"Vince" <vinnyop@yahoo.com> wrote in message
news:1149088154.396500.13770@c74g2000cwc.googlegro ups.com...
> If there is no overlap in the data between the 2 selects, try using
> UNION ALL instead of UNION.
>

Hi

I seem to have come unstuck again. Even though the example query has become
much quicker using the UNION ALL the actual query seems to take forever.
The main reason behind my post was that we used to run a report that queried
a sales order header and detail tables amongst others. I recently replaced
theses tables (BILL_SO and BILL_HDR) with two views (C_BILL_DET and
C_BILL_HDR) that joined these tables to similar tables with a small amount
of data (at this point we are probably only talking of a 100 records). An
example of this was the view I posted yesterday. However, the report use to
take about a minute to run but now takes 20 minutes. Is there something I
can do to speed this up? I will post the SQL that is used to generate te
report data and the explanation for the query below. I am extremely
grateful to anyone who can offer any assistance with this.

Stephen



SELECT
AR_DOC.CUSTOMER, AR_DOC.DOC_TYPE, ITEM.PC, C_BILL_DET.ITEM,
C_BILL_DET.DOC, AR_DOC.CREATED_DATE, MEMO.TOT_ORD_QTY,
C_BILL_DET.SHIP_QTY, MEMO.MEMO_TYPE, C_BILL_HDR.RATE,
C_BILL_DET.SHIP_VALUE, MEMO.BOOK_RATE, MEMO.UNIT_PRICE,
C_BILL_DET.COST, CUS_LOC.NAME
FROM
AR_DOC, C_BILL_HDR, CUS_LOC, C_BILL_DET, MEMO, ITEM
WHERE
(((AR_DOC.BRANCH=C_BILL_HDR.BRANCH) AND
(AR_DOC.DOC_TYPE=C_BILL_HDR.DOC_TYPE)) AND
(AR_DOC.DOC=C_BILL_HDR.DOC)) AND
((AR_DOC.CUSTOMER=CUS_LOC.CUSTOMER) AND
(AR_DOC.CUS_AR_LOC=CUS_LOC.CUS_LOC)) AND
(((C_BILL_HDR.BRANCH=C_BILL_DET.BRANCH) AND
(C_BILL_HDR.DOC_TYPE=C_BILL_DET.DOC_TYPE)) AND
(C_BILL_HDR.DOC=C_BILL_DET.DOC)) AND
((((C_BILL_DET.BRANCH=MEMO.BRANCH (+)) AND
(C_BILL_DET.DOC_TYPE=MEMO.MEMO_TYPE (+))) AND
(C_BILL_DET.DOC_LINE=MEMO.MEMO_LINE (+))) AND
(C_BILL_DET.DOC=MEMO.DOC (+))) AND
((C_BILL_DET.ITEM=ITEM.ITEM (+)) AND
(C_BILL_DET.REVISION=ITEM.REVISION (+))) AND
(AR_DOC.CREATED_DATE>=TO_DATE ('30-04-2006', 'DD-MM-YYYY') AND
AR_DOC.CREATED_DATE<TO_DATE ('28-05-2006', 'DD-MM-YYYY'))
ORDER BY
AR_DOC.CUSTOMER, ITEM.PC




OPERATION OPTIONS OBJECT_NAME OBJECT_INSTANCE
OBJECT_TYPE
-------------------- -------------------- --------------- -----------------
------------
SELECT STATEMENT
SORT ORDER BY
NESTED LOOPS
NESTED LOOPS
MERGE JOIN
SORT JOIN
NESTED LOOPS OUTER
NESTED LOOPS OUTER
VIEW C_BILL_DET 4
UNION-ALL
TABLE ACCESS FULL BILL_SO 7
TABLE ACCESS FULL BILL_CR 8
TABLE ACCESS BY INDEX ROWID ITEM 6
INDEX UNIQUE SCAN PK_ITEM
UNIQUE
TABLE ACCESS BY INDEX ROWID MEMO 5
INDEX RANGE SCAN PK_MEMO
UNIQUE
SORT JOIN
VIEW C_BILL_HDR 2
UNION-ALL
TABLE ACCESS FULL BILL_HDR 9
TABLE ACCESS FULL BILL_HCR 10
TABLE ACCESS BY INDEX ROWID AR_DOC 1
INDEX UNIQUE SCAN PK_AR_DOC
UNIQUE
TABLE ACCESS BY INDEX ROWID CUS_LOC 3
INDEX UNIQUE SCAN PK_CUS_LOC
UNIQUE