OraFAQ Forum: SQL & PL/SQL » Alternate of UNION ALL

Home » SQL & PL/SQL » SQL & PL/SQL » Alternate of UNION ALL (Oracle 10G , Windows 7)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Alternate of UNION ALL [message #624500]

Tue, 23 September 2014 04:53

adilsami
Messages: 46
Registered: October 2007

Member

Hi,

Below is my query which is working fine , I need to replace the UNION ALL with a join .
How I can achieve that ?

My Code :

SELECT      DISTINCT CUSTOMER_CODE,
            VISIT_TIME,
            'Visited' Visit_Flag
FROM        TABLE_A
WHERE       KEY_ID = '555'
UNION ALL
SELECT      CUSTOMER_CODE,
            NULL, 
            'Not Visited'
FROM        TABLE_B
WHERE       KEY_ID = '555'
AND         CUSTOMER_CODE NOT IN 
            (SELECT DISTINCT CUSTOMER_CODE
             FROM TABLE_A
             WHERE KEY_ID = '555')

The above query returns 4 rows , as below :

CUSTOMER_CODE   VISIT_TIME     Visit_Flag
------------------------------------------
420191308	17:15	       Visited
410008065	13:42	       Visited
420192098	23:08	       Visited
420191300		       Not Visited

I am unable to get the last row which has no time with joins , need help !

Report message to a moderator

Re: Alternate of UNION ALL [message #624502 is a reply to message #624500]

Tue, 23 September 2014 04:55

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

Can you provide the create and insert statements for TABLE_A and TABLE_B?

Report message to a moderator

Re: Alternate of UNION ALL [message #624503 is a reply to message #624502]

Tue, 23 September 2014 05:06

adilsami
Messages: 46
Registered: October 2007

Member

DROP TABLE TABLE_A;
CREATE TABLE TABLE_A
(
   KEY_ID          NUMBER ,
   CUSTOMER_CODE   NUMBER ,
   VISIT_TIME      VARCHAR2 (20)
);

DROP TABLE TABLE_B;
CREATE TABLE TABLE_B
(
   KEY_ID          NUMBER (28),
   CUSTOMER_CODE   NUMBER (28)
   
);


INSERT INTO TABLE_A VALUES (555,420191308,'17:15');
COMMIT;
INSERT INTO TABLE_A VALUES (555,410008065,'13:42');
COMMIT;
INSERT INTO TABLE_A VALUES (555,420192098,'23:08');
COMMIT;

INSERT INTO TABLE_B VALUES (555,420191308);
COMMIT;
INSERT INTO TABLE_B VALUES (555,410008065);
COMMIT;
INSERT INTO TABLE_B VALUES (555,420192098);
COMMIT;
INSERT INTO TABLE_B VALUES (555,420191300);
COMMIT;

Report message to a moderator

Re: Alternate of UNION ALL [message #624504 is a reply to message #624500]

Tue, 23 September 2014 05:27

Michel Cadot
Messages: 68648
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

I need to replace the UNION ALL with a join .

Why?

Report message to a moderator

Re: Alternate of UNION ALL [message #624509 is a reply to message #624503]

Tue, 23 September 2014 05:35

pablolee
Messages: 2882
Registered: May 2007
Location: Scotland

Senior Member

Use an outer join

Report message to a moderator

Re: Alternate of UNION ALL [message #624512 is a reply to message #624509]

Tue, 23 September 2014 05:42

adilsami
Messages: 46
Registered: October 2007

Member

I tried using outer join but could not succeed , I never worked with full outer join

Report message to a moderator

Re: Alternate of UNION ALL [message #624513 is a reply to message #624512]

Tue, 23 September 2014 05:44

pablolee
Messages: 2882
Registered: May 2007
Location: Scotland

Senior Member

You don't need a full outer join. Show us what you did.

Report message to a moderator

Re: Alternate of UNION ALL [message #624515 is a reply to message #624512]

Tue, 23 September 2014 05:47

Michel Cadot
Messages: 68648
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Why do you want to change a good query to a less good one?

Report message to a moderator

Re: Alternate of UNION ALL [message #624519 is a reply to message #624515]

Tue, 23 September 2014 06:12

pablolee
Messages: 2882
Registered: May 2007
Location: Scotland

Senior Member

Michel Cadot wrote on Tue, 23 September 2014 11:47

Why do you want to change a good query to a less good one?

Would the union be better? It has to access table_a twice, whereas an outer join only accesses it once. (Just to note, happy to be proved wrong on this, I'm actually just interested)

ETA an autotrace shows 9 consistent gets for the union and 7 for the join.

[Updated on: Tue, 23 September 2014 06:16]

Report message to a moderator

Re: Alternate of UNION ALL [message #624533 is a reply to message #624519]

Tue, 23 September 2014 07:20

adilsami
Messages: 46
Registered: October 2007

Member

Guys , any body got solution for my query ?

Report message to a moderator

Re: Alternate of UNION ALL [message #624536 is a reply to message #624533]

Tue, 23 September 2014 07:29

pablolee
Messages: 2882
Registered: May 2007
Location: Scotland

Senior Member

We can all write a solution, this is pretty basic sql. The point is that we are trying to help you rrather than just do your work for you. Michel asked you a question, which you have chosen not to respond to, I imagine that is why he has not responded back to you he'll be waiting on an answer. I also asked you to post what you tried, you have not done that. I'm waiting for you to post your attemt at using outer joins (as you told us that you had). Frfom thee, we can help you further.

Report message to a moderator

Re: Alternate of UNION ALL [message #624537 is a reply to message #624533]

Tue, 23 September 2014 07:31

Michel Cadot
Messages: 68648
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

You can rewrite it like:

with
  result1 as (
SELECT      DISTINCT CUSTOMER_CODE,
            VISIT_TIME,
            'Visited' Visit_Flag
FROM        TABLE_A
WHERE       KEY_ID = '555'
  )
select * from result1
UNION ALL
SELECT      CUSTOMER_CODE,
            NULL, 
            'Not Visited'
FROM        TABLE_B
WHERE       KEY_ID = '555'
AND         CUSTOMER_CODE NOT IN 
            (select customer_code from result1)

And are you sure the DISTINCT is necessary?

[Updated on: Tue, 23 September 2014 07:35]

Report message to a moderator

Re: Alternate of UNION ALL [message #624540 is a reply to message #624537]

Tue, 23 September 2014 07:42

pablolee
Messages: 2882
Registered: May 2007
Location: Scotland

Senior Member

I must be missing something here Michel:

<Explain Plan for Code that usese an outer join (code hidden so as not to give the game away :)>
   
    KEY_ID CUSTOMER_CODE VISIT_TIME           NVL2(A.KEY_
---------- ------------- -------------------- -----------
       555     420191308 17:15                Visited
       555     410008065 13:42                Visited
       555     420192098 23:08                Visited
       555     420191300                      Not Visited


Execution Plan
----------------------------------------------------------
Plan hash value: 1029326647

------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |     4 |   256 |     5  (20)| 00:00:01 |
|*  1 |  HASH JOIN OUTER   |         |     4 |   256 |     5  (20)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| TABLE_B |     4 |   104 |     2   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| TABLE_A |     3 |   114 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("B"."CUSTOMER_CODE"="A"."CUSTOMER_CODE"(+) AND
              "B"."KEY_ID"="A"."KEY_ID"(+))

Note
-----
   - dynamic sampling used for this statement (level=2)


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

SQL> select      distinct customer_code,
  2              visit_time,
  3              'Visited' visit_flag
  4  from        table_a
  5  where       key_id = '555'
  6  union all
  7  select      customer_code,
  8              null,
  9              'Not Visited'
 10  from        table_b
 11  where       key_id = '555'
 12  and         customer_code not in
 13              (select distinct customer_code
 14               from table_a
 15               where key_id = '555');

CUSTOMER_CODE VISIT_TIME           VISIT_FLAG
------------- -------------------- -----------
    420192098 23:08                Visited
    410008065 13:42                Visited
    420191308 17:15                Visited
    420191300                      Not Visited


Execution Plan
----------------------------------------------------------
Plan hash value: 3013685498

-------------------------------------------------------------------------------
| Id  | Operation           | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |         |     7 |   322 |     8  (75)| 00:00:01 |
|   1 |  UNION-ALL          |         |       |       |            |          |
|   2 |   HASH UNIQUE       |         |     3 |   114 |     3  (34)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL| TABLE_A |     3 |   114 |     2   (0)| 00:00:01 |
|*  4 |   HASH JOIN ANTI NA |         |     4 |   208 |     5  (20)| 00:00:01 |
|*  5 |    TABLE ACCESS FULL| TABLE_B |     4 |   104 |     2   (0)| 00:00:01 |
|*  6 |    TABLE ACCESS FULL| TABLE_A |     3 |    78 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("KEY_ID"=555)
   4 - access("CUSTOMER_CODE"="CUSTOMER_CODE")
   5 - filter("KEY_ID"=555)
   6 - filter("KEY_ID"=555)

Note
-----
   - dynamic sampling used for this statement (level=2)


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

SQL> with
  2    result1 as (
  3  select      distinct customer_code,
  4              visit_time,
  5              'Visited' visit_flag
  6  from        table_a
  7  where       key_id = '555'
  8    )
  9  select * from result1
 10  union all
 11  select      customer_code,
 12              null,
 13              'Not Visited'
 14  from        table_b
 15  where       key_id = '555'
 16  and         customer_code not in
 17              (select customer_code from result1);

CUSTOMER_CODE VISIT_TIME           VISIT_FLAG
------------- -------------------- -----------
    420192098 23:08                Visited
    410008065 13:42                Visited
    420191308 17:15                Visited
    420191300                      Not Visited


Execution Plan
----------------------------------------------------------
Plan hash value: 4058838168

----------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                        | Rows  | Bytes | Cost (%CPU)| Time  |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                             |     7 |   258 |     7  (72)| 00:00:01 |
|   1 |  TEMP TABLE TRANSFORMATION |                             |       |       |            |       |
|   2 |   LOAD AS SELECT           | SYS_TEMP_0FD9D6CB9_F1410C05 |       |       |            |       |
|   3 |    HASH UNIQUE             |                             |     3 |   114 |     3  (34)| 00:00:01 |
|*  4 |     TABLE ACCESS FULL      | TABLE_A                     |     3 |   114 |     2   (0)| 00:00:01 |
|   5 |   UNION-ALL                |                             |       |       |            |       |
|   6 |    VIEW                    |                             |     3 |   102 |     2   (0)| 00:00:01 |
|   7 |     TABLE ACCESS FULL      | SYS_TEMP_0FD9D6CB9_F1410C05 |     3 |   102 |     2   (0)| 00:00:01 |
|*  8 |    HASH JOIN ANTI NA       |                             |     4 |   156 |     5  (20)| 00:00:01 |
|*  9 |     TABLE ACCESS FULL      | TABLE_B                     |     4 |   104 |     2   (0)| 00:00:01 |
|  10 |     VIEW                   |                             |     3 |    39 |     2   (0)| 00:00:01 |
|  11 |      TABLE ACCESS FULL     | SYS_TEMP_0FD9D6CB9_F1410C05 |     3 |   102 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter("KEY_ID"=555)
   8 - access("CUSTOMER_CODE"="CUSTOMER_CODE")
   9 - filter("KEY_ID"=555)

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
          2  recursive calls
          8  db block gets
         13  consistent gets
          1  physical reads
        576  redo size
        375  bytes sent via SQL*Net to client
        248  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          4  rows processed

Looking at the consistent gets, the rewritten code looks less efficient than even the original. Like I say, I'm most likely missing something here.

Report message to a moderator

Re: Alternate of UNION ALL [message #624541 is a reply to message #624540]

Tue, 23 September 2014 07:45

Michel Cadot
Messages: 68648
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

You have to execute it several times, some consistent gets are not from the query itself when you execute it the first time.

Report message to a moderator

Re: Alternate of UNION ALL [message #624543 is a reply to message #624541]

Tue, 23 September 2014 07:54

pablolee
Messages: 2882
Registered: May 2007
Location: Scotland

Senior Member

I did mate, executed each one 4 times.
One thing to note, I'm on 11.2.0.3.0 ...

Report message to a moderator

Re: Alternate of UNION ALL [message #624554 is a reply to message #624543]

Tue, 23 September 2014 08:14

Michel Cadot
Messages: 68648
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quite strange, I did it in the same version:

SQL> SELECT      DISTINCT CUSTOMER_CODE,
  2              VISIT_TIME,
  3              'Visited' Visit_Flag
  4  FROM        TABLE_A
  5  WHERE       KEY_ID = '555'
  6  UNION ALL
  7  SELECT      CUSTOMER_CODE,
  8              NULL,
  9              'Not Visited'
 10  FROM        TABLE_B
 11  WHERE       KEY_ID = '555'
 12  AND         CUSTOMER_CODE NOT IN
 13              (SELECT DISTINCT CUSTOMER_CODE
 14               FROM TABLE_A
 15               WHERE KEY_ID = '555')
 16  /
CUSTOMER_CODE VISIT_TIME           VISIT_FLAG
------------- -------------------- -----------
    410008065 13:42                Visited
    420191308 17:15                Visited
    420192098 23:08                Visited
    420191300                      Not Visited

4 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 4170878039

-------------------------------------------------------------------------------
| Id  | Operation           | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |         |     7 |   322 |    11  (73)| 00:00:01 |
|   1 |  UNION-ALL          |         |       |       |            |          |
|   2 |   HASH UNIQUE       |         |     3 |   114 |     4  (25)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL| TABLE_A |     3 |   114 |     3   (0)| 00:00:01 |
|*  4 |   HASH JOIN ANTI NA |         |     4 |   208 |     7  (15)| 00:00:01 |
|*  5 |    TABLE ACCESS FULL| TABLE_B |     4 |   104 |     3   (0)| 00:00:01 |
|*  6 |    TABLE ACCESS FULL| TABLE_A |     3 |    78 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("KEY_ID"=555)
   4 - access("CUSTOMER_CODE"="CUSTOMER_CODE")
   5 - filter("KEY_ID"=555)
   6 - filter("KEY_ID"=555)

Note
-----
   - dynamic sampling used for this statement (level=2)

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

SQL> with
  2    result1 as (
  3  SELECT      DISTINCT CUSTOMER_CODE,
  4              VISIT_TIME,
  5              'Visited' Visit_Flag
  6  FROM        TABLE_A
  7  WHERE       KEY_ID = '555'
  8    )
  9  select * from result1
 10  UNION ALL
 11  SELECT      CUSTOMER_CODE,
 12              NULL,
 13              'Not Visited'
 14  FROM        TABLE_B
 15  WHERE       KEY_ID = '555'
 16  AND         CUSTOMER_CODE NOT IN
 17              (select customer_code from result1)
 18  /
CUSTOMER_CODE VISIT_TIME           VISIT_FLAG
------------- -------------------- -----------
    410008065 13:42                Visited
    420191308 17:15                Visited
    420192098 23:08                Visited
    420191300                      Not Visited

4 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3277048672

---------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                            |     7 |   258 |     8  (75)| 00:00:01 |
|   1 |  TEMP TABLE TRANSFORMATION |                            |       |       |            |          |
|   2 |   LOAD AS SELECT           | SYS_TEMP_0FD9D6640_1AEA743 |       |       |            |          |
|   3 |    HASH UNIQUE             |                            |     3 |   114 |     4  (25)| 00:00:01 |
|*  4 |     TABLE ACCESS FULL      | TABLE_A                    |     3 |   114 |     3   (0)| 00:00:01 |
|   5 |   UNION-ALL                |                            |       |       |            |          |
|   6 |    VIEW                    |                            |     3 |   102 |     2   (0)| 00:00:01 |
|   7 |     TABLE ACCESS FULL      | SYS_TEMP_0FD9D6640_1AEA743 |     3 |   102 |     2   (0)| 00:00:01 |
|*  8 |    HASH JOIN ANTI NA       |                            |     4 |   156 |     6  (17)| 00:00:01 |
|*  9 |     TABLE ACCESS FULL      | TABLE_B                    |     4 |   104 |     3   (0)| 00:00:01 |
|  10 |     VIEW                   |                            |     3 |    39 |     2   (0)| 00:00:01 |
|  11 |      TABLE ACCESS FULL     | SYS_TEMP_0FD9D6640_1AEA743 |     3 |   102 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter("KEY_ID"=555)
   8 - access("CUSTOMER_CODE"="CUSTOMER_CODE")
   9 - filter("KEY_ID"=555)

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
          2  recursive calls
          8  db block gets
         21  consistent gets
          1  physical reads
        576  redo size
        807  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          4  rows processed

But the tables are too tiny to have something "consistent".
The proof;: if you remove DISTINCT, you get more consistent gets:

SQL> SELECT      CUSTOMER_CODE,
  2              VISIT_TIME,
  3              'Visited' Visit_Flag
  4  FROM        TABLE_A
  5  WHERE       KEY_ID = '555'
  6  UNION ALL
  7  SELECT      CUSTOMER_CODE,
  8              NULL,
  9              'Not Visited'
 10  FROM        TABLE_B
 11  WHERE       KEY_ID = '555'
 12  AND         CUSTOMER_CODE NOT IN
 13              (SELECT DISTINCT CUSTOMER_CODE
 14               FROM TABLE_A
 15               WHERE KEY_ID = '555')
 16  /
CUSTOMER_CODE VISIT_TIME           VISIT_FLAG
------------- -------------------- -----------
    420191308 17:15                Visited
    410008065 13:42                Visited
    420192098 23:08                Visited
    420191300                      Not Visited

4 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1988373719

-------------------------------------------------------------------------------
| Id  | Operation           | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |         |     7 |   322 |    10  (70)| 00:00:01 |
|   1 |  UNION-ALL          |         |       |       |            |          |
|*  2 |   TABLE ACCESS FULL | TABLE_A |     3 |   114 |     3   (0)| 00:00:01 |
|*  3 |   HASH JOIN ANTI NA |         |     4 |   208 |     7  (15)| 00:00:01 |
|*  4 |    TABLE ACCESS FULL| TABLE_B |     4 |   104 |     3   (0)| 00:00:01 |
|*  5 |    TABLE ACCESS FULL| TABLE_A |     3 |    78 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("KEY_ID"=555)
   3 - access("CUSTOMER_CODE"="CUSTOMER_CODE")
   4 - filter("KEY_ID"=555)
   5 - filter("KEY_ID"=555)

Note
-----
   - dynamic sampling used for this statement (level=2)


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

Report message to a moderator