Re: Union All Problem

From: Michel Cadot <micadot_at_netcourrier.com>
Date: 2000/05/15
Message-ID: <8fp9ab$2nm2$1@news4.isdnet.net>

First you haven't created your view
with the statement you give us.
Actually, in your create view statement
view keyword is missing and there is 4
columns for the view and only 3 in the
select statements.

If you correct that, it's important to
know your Oracle version.

That works fine in Oracle 7.3.4, with
union or union all:

v734> create table tablea (col1 number,colx number);
v734> create table tableb (col2 number, col3 number);
v734> create or replace view my_view (col1,col2,col3) as

  2 select a.col1,b.col2,b.col3
  3 from tablea a
  4 , (select col2

  5        ,      col3
  6        from tableb) b

  7 where b.col3 = a.colx
  8 union all
  9 select a.col1,b.col2,b.col3
 10 from tablea a
 11 , (select col2

 12        ,      col3
 13        from tableb) b

 14 where b.col3 = a.colx
 15 /
v734> select 1, 2 from my_view;

no rows selected

v734> select * from my_view;

no rows selected

v734> create or replace view my_view (col1,col2,col3) as   2 select a.col1,b.col2,b.col3
  3 from tablea a
  4 , (select col2

  5        ,      col3
  6        from tableb) b

  7 where b.col3 = a.colx
  8 union
  9 select a.col1,b.col2,b.col3
 10 from tablea a
 11 , (select col2

 12        ,      col3
 13        from tableb) b

 14 where b.col3 = a.colx
 15 /

v734> select 1, 2 from my_view;

no rows selected

v734> select * from my_view;

no rows selected

But you're right in 8.1.5:

v815> create table tablea (col1 number,colx number);
v815> create table tableb (col2 number, col3 number);
v815> create or replace view my_view (col1,col2,col3) as

  2 select a.col1,b.col2,b.col3
  3 from tablea a
  4 , (select col2

  5        ,      col3
  6        from tableb) b

  7 where b.col3 = a.colx
  8 union all
  9 select a.col1,b.col2,b.col3
 10 from tablea a
 11 , (select col2

 12        ,      col3
 13        from tableb) b

 14 where b.col3 = a.colx
 15 /
v815> select 1, 2 from my_view;
select 1, 2 from my_view

                 *

ERROR at line 1:
ORA-01789: query block has incorrect number of result columns

v815> select * from my_view;

no rows selected

v815> create or replace view my_view (col1,col2,col3) as   2 select a.col1,b.col2,b.col3
  3 from tablea a
  4 , (select col2

  5        ,      col3
  6        from tableb) b

  7 where b.col3 = a.colx
  8 union
  9 select a.col1,b.col2,b.col3
 10 from tablea a
 11 , (select col2

 12        ,      col3
 13        from tableb) b

 14 where b.col3 = a.colx
 15 /

v815> select 1, 2 from my_view;

no rows selected

v815> select * from my_view;

no rows selected

The difference between union and union all is the deletion of the duplicated rows with the union.

Here's the two explain plans for
"select * from my_view;"
first with union all and then with union.

SELECT STATEMENT
  VIEW MY_VIEW
    UNION-ALL

      MERGE JOIN
        SORT JOIN
          TABLE ACCESS FULL TABLEB
        SORT JOIN
          TABLE ACCESS FULL TABLEA
      MERGE JOIN
        SORT JOIN
          TABLE ACCESS FULL TABLEB
        SORT JOIN
          TABLE ACCESS FULL TABLEA

SELECT STATEMENT
  VIEW MY_VIEW
    SORT UNIQUE

      UNION-ALL
        MERGE JOIN
          SORT JOIN
            TABLE ACCESS FULL TABLEB
          SORT JOIN
            TABLE ACCESS FULL TABLEA
        MERGE JOIN
          SORT JOIN
            TABLE ACCESS FULL TABLEB
          SORT JOIN
            TABLE ACCESS FULL TABLEA

You see the SORT UNIQUE step used for the deletion of the duplicates.

It is interesting to notice that the use of an inline view does not generate an error:

v815> select 1, 2 from
  2 (select a.col1,b.col2,b.col3
  3 from tablea a
  4 , (select col2

  5        ,      col3
  6        from tableb) b

  7 where b.col3 = a.colx
  8 union all
  9 select a.col1,b.col2,b.col3
 10 from tablea a
 11 , (select col2

 12        ,      col3
 13        from tableb) b

 14 where b.col3 = a.colx) ;

no rows selected

So, it seems to be a bug.

--
Regards
Michel


Gareth Leachman <grleachman_at_my-deja.com> a écrit dans le message : 8fp5sa$dq9$1_at_nnrp1.deja.com...


> I would like to know if anyone has a documented reason for the

> following:

>

> select 1, 2 from my_view;

> select * from my_view;

>

> are 2 select queries on my_view.

> my_view defined as:

>

> create my_view (col1,col2,col3,col4) as

> select a.col1,b.col2,b.col3

> from tablea a

> ,    (select col2

>       ,      col3

>       from tableb) b

> where b.col3 = a.colx

> union all

> select a.col1,b.col2,b.col3

> from tablea a

> ,    (select col2

>       ,      col3

>       from tableb) b

> where b.col3 = a.colx

>

> Now when the first query is run it gives error 1789: Query block has

> incorrect number of result columns.

> The second query returns a correct result.

>

> After doing a little bit of fiddling, the inline view was taken out, and

> this produced a proper result for query 1.

>

> It was interesting to note that replacing union all with union worked

> for query 1 and 2 in the original version.

>

> So the question is: why would an inline view affect the result of a

> union all query.

>

> Thanks

> Gareth

>

>

> Sent via Deja.com http://www.deja.com/

> Before you buy.

Received on Mon May 15 2000 - 00:00:00 CDT