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  |
 |
Unclefool
Messages: 65
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  |
 |
Michel Cadot
Messages: 68418
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');
|
|
|
|
|
|
|
|
|
|
| Re: Flatten table to get unique VALUES [message #685372 is a reply to message #685371] |
Fri, 17 December 2021 10:16 |
|
Michel Cadot
Messages: 68418
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 #685384 is a reply to message #685381] |
Sun, 19 December 2021 11:46 |
John Watson
Messages: 8804
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 |
Solomon Yakobson
Messages: 3212
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>
|
|
|
|
| Re: Flatten table to get unique VALUES [message #685394 is a reply to message #685393] |
Mon, 20 December 2021 11:22 |
John Watson
Messages: 8804
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)
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 
|
|
|
|
Goto Forum:
Current Time: Tue Mar 28 13:20:10 CDT 2023
|
] 
Search
Help
Members
Register
Login
Home
