Home » SQL & PL/SQL » SQL & PL/SQL » How to "union" values in two columns from the same table?
How to "union" values in two columns from the same table? [message #265874] Fri, 07 September 2007 13:18 Go to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Lets say I have a table with columns a and b. Each column has a bunch of (sometimes overlapping) numbers.

I want to get a result set that just has a single list of all of the numbers from both columns. None of the numbers should be duplicated in the ist. Like a union, except that it is a union of two columns in the same table, not a union of two different tables.

One approach would be using a union, but using two copies of the same table but with different aliasses, something like:

select a from mytable t1
union
select b from mytable t2

However, I want to avoid this because "mytable" really isn't a simple table, but it is an inline view that is a complex query that I want to only execute once if I can help it.

In theory, I could avoid the double execution problem by using the "with" clause, turning my inline view into a materialized with clause result, and then doing the union would be fine because it would only execute the complex query once.

But in my situation right now, I'm doing all of this on 8i, and there is no with clause / subquery_factoring_clause. Any ideas?
Re: How to "union" values in two columns from the same table? [message #265887 is a reply to message #265874] Fri, 07 September 2007 13:56 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
I'm sorry I cannot provide an answer right now smartin, but I am going to think about this.

I answered because I wanted to say that this is a cool question and it would be interesting to see the approaches that people come up with. I can think of one poorly performing query, but I am sure you don't want that.

This almost sounds like a quiz to test people's problem solving ability. Unless of course it is really easy and I am just not thinking. Let's see what people come up with.
Re: How to "union" values in two columns from the same table? [message #265889 is a reply to message #265874] Fri, 07 September 2007 14:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Of course you could post a test case that we can use to test our solution possible solution.

Regards
Michel
Re: How to "union" values in two columns from the same table? [message #265895 is a reply to message #265874] Fri, 07 September 2007 14:16 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Yes...but I'm really just looking for in theory, is it even possible? What path should I start down? I don't mind doing the test case and the solution even, the work. Just looking for the approach. Not looking for someone to take the time to actually work out the exact final answer.

Because, this is just one branch of several other possible branches in a larger issue, and if it isn't in theory doable then I want to close off this branch now and focus on the others. At first I was thinking this was a pivot situation, but it isn't really, it is different...

So a partial data set might look like:

create table test(a number, b number);

insert into test values (1, 1);
insert into test values (null, null);
insert into test values (2, 100);
insert into test values (2, 101);
insert into test values (2, 102);
insert into test values (3, null);
insert into test values (3, 300);
insert into test values (null, 400);
insert into test values (null, 500);
insert into test values (4, 500);
insert into test values (4, 101);
insert into test values (101,101);
insert into test values (2,2);
commit;

select * from test order by a, b;

select a from test t1
union
select b from test t2
;


MYDBA@orcl > select * from test order by a, b;

         A          B
---------- ----------
         1          1
         2          2
         2        100
         2        101
         2        102
         3        300
         3 (null)
         4        101
         4        500
       101        101
(null)            400
(null)            500
(null)     (null)

13 rows selected.

MYDBA@orcl >
MYDBA@orcl > select a from test t1
  2  union
  3  select b from test t2
  4  ;

         A
----------
         1
         2
         3
         4
       100
       101
       102
       300
       400
       500
(null)

11 rows selected.


I really don't want the null either, but I can live with it because a later join will knock it out.
Re: How to "union" values in two columns from the same table? [message #265897 is a reply to message #265895] Fri, 07 September 2007 14:24 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
In the far reaches of my mind, I though I vaguely remember an analytic function that would combine the values of two columns into a single column, almost like a pivot, and then you would have all the non-distinct values from the two columns. Maybe it was a dream, but I am sorry I even mention it as it surely was not available in 8i.

In any event, I think
select distinct x from (
  select a x from test t1
  union all
  select b x from test t2);

is a little (very little) more efficient with the data given.

FOO SCOTT>select a from test t1
2 union
3 select b from test t2
4 ;

Execution Plan
----------------------------------------------------------
Plan hash value: 1445684123

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |    26 |   338 |     8  (63)| 00:00:01 |
|   1 |  SORT UNIQUE        |      |    26 |   338 |     8  (63)| 00:00:01 |
|   2 |   UNION-ALL         |      |       |       |            |          |
|   3 |    TABLE ACCESS FULL| TEST |    13 |   169 |     3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| TEST |    13 |   169 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement

FOO SCOTT>select distinct x from (
2  select a x from test t1
3  union all
4  select b x from test t2);

Execution Plan
----------------------------------------------------------
Plan hash value: 2719131840

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |    26 |   338 |     7  (15)| 00:00:01 |
|   1 |  SORT UNIQUE         |      |    26 |   338 |     7  (15)| 00:00:01 |
|   2 |   VIEW               |      |    26 |   338 |     6   (0)| 00:00:01 |
|   3 |    UNION-ALL         |      |       |       |            |          |
|   4 |     TABLE ACCESS FULL| TEST |    13 |   169 |     3   (0)| 00:00:01 |
|   5 |     TABLE ACCESS FULL| TEST |    13 |   169 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement

[Updated on: Fri, 07 September 2007 14:46]

Report message to a moderator

Re: How to "union" values in two columns from the same table? [message #265900 is a reply to message #265874] Fri, 07 September 2007 15:00 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
That is interesting...the cost calculation on those two variations. But that has got to be rounding error or something similar in the cost calculation, because both plans are doing essentially the same thing: two fts, a union all, and a sort unique. I don't see how one could actually perform faster than the other. But I like the creativity of trying it.

However, I don't see running the query twice as an option for me, because if I go that route, then several other decisions change in my branches, and none of this applies anymore.

BTW, if its one thing I've had drilled into me over and over, from a bunch of query tuning work I'm doing at a site that uses 8i, its that lower cost does not equal better. I know the CBO is smarter in 10g than 8i, and there are some other issues at this site specifically, but man, I've turned a query that had a cost of like 100 that ran terrible into a query that had a cost of 1000 and flew. Almost as if there is no relation.

Oh, and just to do it, if I run your two union approaches through sqlplus autotrace explain and statistics (twice to get past the parsing etc overhead), each results in 14 consistent gets. Not that this is a large enough data set to really tell, but that does make sense.

However, I'm not duplicating your situation, because on my system, even though I get the same cost calculation as you showed, my two plans are slightly different than yours. One uses the new (compared to 8i) hash sort, while the other uses the sort unique. Resulting in the hash sort plan showing a 0 for memory sorts in the statistics compared to 1:

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |    26 |   338 |     8  (63)| 00:00:01 |
|   1 |  SORT UNIQUE        |      |    26 |   338 |     8  (63)| 00:00:01 |
|   2 |   UNION-ALL         |      |       |       |            |          |
|   3 |    TABLE ACCESS FULL| TEST |    13 |   169 |     3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| TEST |    13 |   169 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         14  consistent gets
          0  physical reads
          0  redo size
        515  bytes sent via SQL*Net to client
        381  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         11  rows processed


-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |    26 |   338 |     7  (15)| 00:00:01 |
|   1 |  HASH UNIQUE         |      |    26 |   338 |     7  (15)| 00:00:01 |
|   2 |   VIEW               |      |    26 |   338 |     6   (0)| 00:00:01 |
|   3 |    UNION-ALL         |      |       |       |            |          |
|   4 |     TABLE ACCESS FULL| TEST |    13 |   169 |     3   (0)| 00:00:01 |
|   5 |     TABLE ACCESS FULL| TEST |    13 |   169 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         14  consistent gets
          0  physical reads
          0  redo size
        515  bytes sent via SQL*Net to client
        381  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         11  rows processed



So that is interesting. But...I'm straying rather far from my own topic...
Re: How to "union" values in two columns from the same table? [message #265903 is a reply to message #265900] Fri, 07 September 2007 15:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you have to execute it n 8i don't try to optimize with 10g.
Difference indeed comes from rounding.
HASH UNIQUE does not exist in 8i.

Regards
Michel
Re: How to "union" values in two columns from the same table? [message #265906 is a reply to message #265874] Fri, 07 September 2007 15:33 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Right...I agree...I was just testing out joy_division's union variation technique real quick on my pc...just to see what would happen regarding the execution stats vs the projected explain plan. Academic curiosity really for future possible use.

However, I'm still curious if anyone has any suggestions to my original question regarding the situation I'm in now. Is it possible to "union" two columns from the same table, without including that table twice in the query? I don't see how, but thought I'd post and see if anyone had an idea.
Re: How to "union" values in two columns from the same table? [message #265909 is a reply to message #265874] Fri, 07 September 2007 16:18 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Yes, it can be done by cartesian product with the two-rows table and using DECODE in the SELECT clause.
You can use Oracle Row Generator Technique; some of them shall be available in 8i.
Re: How to "union" values in two columns from the same table? [message #265932 is a reply to message #265906] Sat, 08 September 2007 00:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can do something like:
SQL> select unique decode(n,1,a,b) col
  2  from test, 
  3       (select 1 n from dual union all select 2 n from dual)
  4  /
       COL
----------
         1

       100
       400
         2
       500
         4
       102
       300
       101
         3

11 rows selected.

I don't have an 8i to show the plan but in 10g this gives:
------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |    26 |   754 |     9  (12)| 00:00:01 |
|   1 |  HASH UNIQUE          |      |    26 |   754 |     9  (12)| 00:00:01 |
|   2 |   MERGE JOIN CARTESIAN|      |    26 |   754 |     8   (0)| 00:00:01 |
|   3 |    VIEW               |      |     2 |     6 |     4   (0)| 00:00:01 |
|   4 |     UNION-ALL         |      |       |       |            |          |
|   5 |      FAST DUAL        |      |     1 |       |     2   (0)| 00:00:01 |
|   6 |      FAST DUAL        |      |     1 |       |     2   (0)| 00:00:01 |
|   7 |    BUFFER SORT        |      |    13 |   338 |     8   (0)| 00:00:01 |
|   8 |     TABLE ACCESS FULL | TEST |    13 |   338 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement

Regards
Michel

[Edit] Ooops! sorry flyboy, didn't see your post, I didn't take my coffee yet this morning.

[Updated on: Sat, 08 September 2007 00:44]

Report message to a moderator

Re: How to "union" values in two columns from the same table? [message #265993 is a reply to message #265932] Sat, 08 September 2007 10:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Found a 8i.
In this case, the explain plan is the following:
SELECT STATEMENT
  SORT UNIQUE
    NESTED LOOPS
      VIEW  MICHEL.
        UNION-ALL
          TABLE ACCESS FULL SYS.DUAL
          TABLE ACCESS FULL SYS.DUAL
      TABLE ACCESS FULL MICHEL.TEST

Regards
Michel
Re: How to "union" values in two columns from the same table? [message #266305 is a reply to message #265874] Mon, 10 September 2007 07:57 Go to previous message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Thanks Flyboy and Michel, that was exactly what I was looking for. Appreciate the help.
Previous Topic: Problem with NOWAIT error when trying to drop index
Next Topic: RowType In Function List
Goto Forum:
  


Current Time: Sat Dec 10 03:21:40 CST 2016

Total time taken to generate the page: 0.09020 seconds