Home » SQL & PL/SQL » SQL & PL/SQL » Flatten table to get unique VALUES (19.2)
Flatten table to get unique VALUES [message #685362] Thu, 16 December 2021 17:43 Go to next message
Unclefool
Messages: 82
Registered: August 2021
Member
I have a table that contains 4 fields. first_client, first_client_email, second_client, second_client_email ( see TABLE T1)

Where:

the first client number and the second client number can be different
the first client number and the second client number can be the same
I want to get a unique list of all emails so that I have one column that shows the client number and a second column to show the clients email address. How do I flatten the table to get my desired result and stick them into table T2?


CREATE TABLE T1(
first_client_number    NUMBER,
first_client_email VARCHAR2(25),    second_client_number NUMBER,    second_client_email VARCHAR2(25)
);

INSERT  INTO t1
(first_client_number,
first_client_email,    second_client_number,second_client_email) 
SELECT 1111, 'na1@na.com',
2222,
'na2@na.com' FROM DUAL UNION ALL
SELECT 3333,    'na3@na.com',    3333,    'na3@na.com' FROM DUAL UNION ALL
SELECT 4444,    'na4@na.com',    4444,    'na4@na.com' FROM DUAL UNION ALL
SELECT 
6666,     'na6@na.com'    ,7777    ,'na7@na.com' FROM DUAL 


CREATE TABLE T2(
client_number NUMBER ,constraint T2_pk primary key (client_number),
client_email VARCHAR2(25)
   CONSTRAINT client_email_nn NOT NULL,
CONSTRAINT client_email_uk
   UNIQUE (client_email)
);

Re: Flatten table to get unique VALUES [message #685363 is a reply to message #685362] Fri, 17 December 2021 00:46 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You can't have, at the same time:

Quote:
the first client number and the second client number can be different
the first client number and the second client number can be the same

and

Quote:
primary key (client_number), UNIQUE (client_email)

Add these rows:
insert into t1 values (8888, 'na8@na.com', 8888, 'na8b@na.com');
insert into t1 values (9999, 'na9@na.com', 9998, 'na9@na.com');
what is then the expected result?

Re: Flatten table to get unique VALUES [message #685366 is a reply to message #685363] Fri, 17 December 2021 06:45 Go to previous messageGo to next message
Unclefool
Messages: 82
Registered: August 2021
Member
Thanks for replying

With the sample data I provided in table T1 the expected results in Table T2 should be as follows.

CLIENT_NUMBER CLIENT_EMAIL
------------- ---------------------
3333 na3@na.com
4444 na4@na.com
2222 na2@na.com
6666 na6@na.com
7777 na7@na.com
1111 na1@na.com
Re: Flatten table to get unique VALUES [message #685370 is a reply to message #685366] Fri, 17 December 2021 07:59 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

The most important is with the data I added.

Re: Flatten table to get unique VALUES [message #685371 is a reply to message #685363] Fri, 17 December 2021 09:19 Go to previous messageGo to next message
Unclefool
Messages: 82
Registered: August 2021
Member
I see your point with the data you added I can't have unique index on CLIENT_EMAIL for now let's forget about the PK and unique INDEX in table T2. I can write a query and let the application team direct me on how they want to proceed.

With your data I would expect the following results

CLIENT_NUMBER CLIENT_EMAIL
9999 na9@na.com
1111 na1@na.com
3333 na3@na.com
4444 na4@na.com
7777 na7@na.com
2222 na2@na.com
6666 na6@na.com
9998 na9@na.com
8888 na8@na.com
Re: Flatten table to get unique VALUES [message #685372 is a reply to message #685371] Fri, 17 December 2021 10:16 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Your result is not correct, where is (8888, 'na8b@na.com') from the data?
If you want the distinct couple (id, mail) then it is:
SQL> select distinct
  2         decode(line, 1, first_client_number, second_client_number) client_number,
  3         decode(line, 1, first_client_email, second_client_email) client_email
  4  from t1,
  5        (select 1 line from dual union all select 2 from dual)
  6  order by 1, 2
  7  /
CLIENT_NUMBER CLIENT_EMAIL
------------- -------------------------
         1111 na1@na.com
         2222 na2@na.com
         3333 na3@na.com
         4444 na4@na.com
         6666 na6@na.com
         7777 na7@na.com
         8888 na8@na.com
         8888 na8b@na.com
         9998 na9@na.com
         9999 na9@na.com

10 rows selected.
Re: Flatten table to get unique VALUES [message #685381 is a reply to message #685362] Fri, 17 December 2021 13:23 Go to previous messageGo to next message
Unclefool
Messages: 82
Registered: August 2021
Member
Perfect thank you. Happy holidays
Re: Flatten table to get unique VALUES [message #685384 is a reply to message #685381] Sun, 19 December 2021 11:46 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
I haven't thought this through in detail, but I have an alternative solution that does come up with a lower cost (19.3, Windows). First mine, then Michel's:
orclz>
orclz> with
  2  first as (select first_client_number,first_client_email from t1),
  3  second as (select second_client_number,second_client_email from t1)
  4  select * from first union select * from second;

FIRST_CLIENT_NUMBER FIRST_CLIENT_EMAIL
------------------- -------------------------
               1111 na1@na.com
               2222 na2@na.com
               3333 na3@na.com
               4444 na4@na.com
               6666 na6@na.com
               7777 na7@na.com
               8888 na8@na.com
               8888 na8b@na.com
               9998 na9@na.com
               9999 na9@na.com

10 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 543047041

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |    12 |   324 |     8  (25)| 00:00:01 |
|   1 |  SORT UNIQUE        |      |    12 |   324 |     8  (25)| 00:00:01 |
|   2 |   UNION-ALL         |      |       |       |            |          |
|   3 |    TABLE ACCESS FULL| T1   |     6 |   162 |     3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| T1   |     6 |   162 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

orclz> select distinct
  2             decode(line, 1, first_client_number, second_client_number) client_number,
  3             decode(line, 1, first_client_email, second_client_email) client_email
  4      from t1,
  5            (select 1 line from dual union all select 2 from dual)
  6      order by 1, 2;

  CLIENT_NUMBER CLIENT_EMAIL
--------------- -------------------------
           1111 na1@na.com
           2222 na2@na.com
           3333 na3@na.com
           4444 na4@na.com
           6666 na6@na.com
           7777 na7@na.com
           8888 na8@na.com
           8888 na8b@na.com
           9998 na9@na.com
           9999 na9@na.com

10 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 619002396

------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |    12 |   684 |    10  (20)| 00:00:01 |
|   1 |  SORT UNIQUE          |      |    12 |   684 |     9  (12)| 00:00:01 |
|   2 |   MERGE JOIN CARTESIAN|      |    12 |   684 |     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        |      |     6 |   324 |     9  (12)| 00:00:01 |
|   8 |     TABLE ACCESS FULL | T1   |     6 |   324 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

orclz>
Re: Flatten table to get unique VALUES [message #685393 is a reply to message #685384] Mon, 20 December 2021 09:56 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
Using LATERAL (or CROSS APPLY):

select  distinct l.*
  from  t1,
        lateral(
                select  case level when 1 then first_client_number else second_client_number end client_number,
                        case level when 1 then first_client_email else second_client_email end client_email
                  from  dual
                  connect by level <= 2
               ) l
  order by client_number,
           client_email
/

CLIENT_NUMBER CLIENT_EMAIL
------------- -------------------------
         1111 na1@na.com
         2222 na2@na.com
         3333 na3@na.com
         4444 na4@na.com
         6666 na6@na.com
         7777 na7@na.com

6 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3957879201

---------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                 |     4 |   324 |    13  (16)| 00:00:01 |
|   1 |  SORT UNIQUE                    |                 |     4 |   324 |    12   (9)| 00:00:01 |
|   2 |   NESTED LOOPS                  |                 |     4 |   324 |    11   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL            | T1              |     4 |   216 |     3   (0)| 00:00:01 |
|   4 |    VIEW                         | VW_LAT_A18161FF |     1 |    27 |     2   (0)| 00:00:01 |
|*  5 |     CONNECT BY WITHOUT FILTERING|                 |       |       |            |          |
|   6 |      FAST DUAL                  |                 |     1 |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------

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

   5 - filter(LEVEL<=2)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

SQL>
SY.
Re: Flatten table to get unique VALUES [message #685394 is a reply to message #685393] Mon, 20 December 2021 11:22 Go to previous message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
This is my plan for the SY solution:
Execution Plan
----------------------------------------------------------
Plan hash value: 3957879201

---------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                 |     6 |   342 |    17  (12)| 00:00:01 |
|   1 |  SORT UNIQUE                    |                 |     6 |   342 |    16   (7)| 00:00:01 |
|   2 |   NESTED LOOPS                  |                 |     6 |   342 |    15   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL            | T1              |     6 |   180 |     3   (0)| 00:00:01 |
|   4 |    VIEW                         | VW_LAT_A18161FF |     1 |    27 |     2   (0)| 00:00:01 |
|*  5 |     CONNECT BY WITHOUT FILTERING|                 |       |       |            |          |
|   6 |      FAST DUAL                  |                 |     1 |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------

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

   5 - filter(LEVEL<=2)
I may be misreading the plans, but:

The cost for my plan is clear: two scans of the table costed at 3 IO units each, plus 2 CPU units for removing the duplicates.
The cost of the SY plan is also good: 3 IO units for the scan, plus 6 * 2 units for the iterations of of the lateral, plus two more CPU units at the end.
But the cost arithmetic for my MC plan is weird. The cost of the view is 4, returning 2 rows, and the cost of the scan and sort is 9. So the cost of the cartesian join should be 4 + 2*9 = 22. But it isn't - it is 8. Which could be correct, because the cost of 9 at operation 7 is ludicrous: 2 would be more like it. And 4 + 2*2 is indeed 8.

Overall, I suspect that my solution would be the worst at large scale, because there is no way out of the two scans. I suspect that the MC solution would have the same problem. SY's might scale well by switching to a hash join.

Nice one, UncleFool! If you can share your actual results, it would be interesting. Well, interesting to someone as boring as me Sad
Previous Topic: ORA 12545 | UTL_HTTP
Next Topic: Split a row into multiple rows
Goto Forum:
  


Current Time: Fri Mar 29 05:38:25 CDT 2024