December 17, 2009
In a recent comp.databases.oracle.misc Usenet thread:
http://groups.google.com/group/comp.databases.oracle.misc/browse_thread/thread/42c0d1550278250b
The following question was asked:
I need help with a query which involves the 2 tables defined below. What I need to do is choose the record with the max “Eff Date” from “Table A” for a particular “Emp No.” and update the “Desc” from that record in the field “Desc” of “Table B” for the same “Emp No.”. I am able to choose the max “Eff Date” record for each employee from Table A but somehow not able to updated the same “Desc” in “Table B”.
Request you to please help the query. Any help would be appreciated.
Thanks!Table A Emp No. Group Eff Date Desc 1234 CI 01/01/1989 X 1234 CI 01/02/2000 X 1234 CI 01/02/2006 A 2345 AF 01/01/1990 X 2345 AF 01/02/2005 A
Table B Emp No. Group Desc 1234 CI X 2345 AF A 3456 CI A
I provided the following suggestion:
Watch the query and results closely as one possible solution is built (there are other methods):
CREATE TABLE T1 (
EMP_NO NUMBER,
GROUPING VARCHAR2(5),
EFF_DATE DATE,
DESCR VARCHAR2(5));
CREATE TABLE T2 (
EMP_NO NUMBER,
GROUPING VARCHAR2(5),
DESCR VARCHAR2(5));
INSERT INTO T1 VALUES (1234,'CI',TO_DATE('01/01/1989','MM/DD/YYYY'),'X');
INSERT INTO T1 VALUES (1234,'CI',TO_DATE('01/02/2000','MM/DD/YYYY'),'X');
INSERT INTO T1 VALUES (1234,'CI',TO_DATE('01/02/2006','MM/DD/YYYY'),'A');
INSERT INTO T1 VALUES (2345,'AF',TO_DATE('01/01/1990','MM/DD/YYYY'),'X');
INSERT INTO T1 VALUES (2345,'AF',TO_DATE('01/02/2005','MM/DD/YYYY'),'A');
INSERT INTO T2 VALUES (1234,'CI','XNN');
INSERT INTO T2 VALUES (2345,'AF','ANN');
INSERT INTO T2 VALUES (3456,'CI','ANN');
COMMIT;
SELECT EMP_NO, GROUPING, DESCR FROM T2; EMP_NO GROUP DESCR ------ ----- ----- 1234 CI XNN 2345 AF ANN 3456 CI ANN
SELECT EMP_NO, GROUPING, EFF_DATE, DESCR FROM T1; EMP_NO GROUP EFF_DATE DESCR ------ ----- --------- ----- 1234 CI 01-JAN-89 X 1234 CI 02-JAN-00 X 1234 CI 02-JAN-06 A 2345 AF 01-JAN-90 X 2345 AF 02-JAN-05 A
SELECT EMP_NO, GROUPING, EFF_DATE, ROW_NUMBER() OVER (PARTITION BY EMP_NO, GROUPING ORDER BY EFF_DATE DESC) RN, DESCR FROM T1; EMP_NO GROUP EFF_DATE RN DESCR ------ ----- --------- ---------- ----- 1234 CI 02-JAN-06 1 A 1234 CI 02-JAN-00 2 X 1234 CI 01-JAN-89 3 X 2345 AF 02-JAN-05 1 A 2345 AF 01-JAN-90 2 X
SELECT EMP_NO, GROUPING, EFF_DATE, DESCR FROM (SELECT EMP_NO, GROUPING, EFF_DATE, ROW_NUMBER() OVER (PARTITION BY EMP_NO, GROUPING ORDER BY EFF_DATE DESC) RN, DESCR FROM T1) WHERE RN=1; EMP_NO GROUP EFF_DATE DESCR ------ ----- --------- ----- 1234 CI 02-JAN-06 A 2345 AF 02-JAN-05 A
UPDATE T2 SET DESCR=( SELECT DESCR FROM (SELECT EMP_NO, GROUPING, ROW_NUMBER() OVER (PARTITION BY EMP_NO, GROUPING ORDER BY EFF_DATE DESC) RN, DESCR FROM T1) T1 WHERE RN=1 AND T1.EMP_NO=T2.EMP_NO AND T1.GROUPING=T2.GROUPING) WHERE (T2.EMP_NO,T2.GROUPING) IN ( SELECT EMP_NO, GROUPING FROM (SELECT EMP_NO, GROUPING, ROW_NUMBER() OVER (PARTITION BY EMP_NO, GROUPING ORDER BY EFF_DATE DESC) RN, DESCR FROM T1) WHERE RN=1); 2 rows updated.
SELECT EMP_NO, GROUPING, DESCR FROM T2; EMP_NO GROUP DESCR ------ ----- ----- 1234 CI A 2345 AF A 3456 CI ANN
Note that in the above, I assumed that the combination of EMP_NO and GROUPING had to be the same.
~~~~~~~~~~~~~~~~~~~~~~~
Maxim Demenko provided a very different approach to solving the problem that is both compact and impressive:
SQL> merge into t2 t2 2 using ( 3 select emp_no,grouping, 4 max(descr) keep(dense_rank last order by eff_date) descr 5 from t1 group by emp_no,grouping) t1 6 on (t1.emp_no=t2.emp_no 7 and t1.grouping=t2.grouping) 8 when matched then update set t2.descr=t1.descr 9 ; 2 rows merged.
Execution Plan
----------------------------------------------------------
Plan hash value: 3235844370
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | MERGE STATEMENT | | 2 | 16 | 8 (25)| 00:00:01 |
| 1 | MERGE | T2 | | | | |
| 2 | VIEW | | | | | |
|* 3 | HASH JOIN | | 2 | 108 | 8 (25)| 00:00:01 |
| 4 | TABLE ACCESS FULL | T2 | 3 | 99 | 3 (0)| 00:00:01 |
| 5 | VIEW | | 5 | 105 | 4 (25)| 00:00:01 |
| 6 | SORT GROUP BY | | 5 | 150 | 4 (25)| 00:00:01 |
| 7 | TABLE ACCESS FULL| T1 | 5 | 150 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T1"."EMP_NO"="T2"."EMP_NO" AND
"T1"."GROUPING"="T2"."GROUPING")
~~~~~~~~~~~~~~~~~~~~~~~
Since Maxim provided an execution plan, let’s compare the efficiency of the two methods with a larger test case that uses the same table definitions:
TRUNCATE TABLE T1;
TRUNCATE TABLE T2;
INSERT INTO T1
SELECT
DECODE(MOD(ROWNUM,10),
0,0000,
1,1111,
2,2222,
3,3333,
4,4444,
5,5555,
6,6666,
7,7777,
8,8888,
9,9999),
DECODE(MOD(ROWNUM,6),
0,'AA',
1,'BB',
2,'CC',
3,'DD',
4,'EE',
5,'FF'),
TRUNC(SYSDATE+SIN(ROWNUM/180*3.141592)*1000),
UPPER(DBMS_RANDOM.STRING('A',1))
FROM
DUAL
CONNECT BY
LEVEL<=1000000;
INSERT INTO T2
SELECT
DECODE(MOD(ROWNUM,10),
0,0000,
1,1111,
2,2222,
3,3333,
4,4444,
5,5555,
6,6,
7,7,
8,8,
9,9),
DECODE(MOD(ROWNUM,11),
0,'AA',
1,'BB',
2,'CC',
3,'DD',
4,'EE',
5,'FF',
6,'GG',
7,'HH',
8,'II',
9,'JJ',
10,'KK'),
UPPER(DBMS_RANDOM.STRING('A',3))
FROM
DUAL
CONNECT BY
LEVEL<=100;
COMMIT;
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1',CASCADE=>TRUE)
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T2',CASCADE=>TRUE)
SET PAGESIZE 1000 SET LINESIZE 150 ALTER SESSION SET STATISTICS_LEVEL='ALL'; ALTER SYSTEM FLUSH BUFFER_CACHE; ALTER SYSTEM FLUSH BUFFER_CACHE; SPOOL C:\CHECKTIMING.TXT UPDATE T2 SET DESCR=( SELECT DESCR FROM (SELECT EMP_NO, GROUPING, ROW_NUMBER() OVER (PARTITION BY EMP_NO, GROUPING ORDER BY EFF_DATE DESC) RN, DESCR FROM T1) T1 WHERE RN=1 AND T1.EMP_NO=T2.EMP_NO AND T1.GROUPING=T2.GROUPING) WHERE (T2.EMP_NO,T2.GROUPING) IN ( SELECT EMP_NO, GROUPING FROM (SELECT EMP_NO, GROUPING, ROW_NUMBER() OVER (PARTITION BY EMP_NO, GROUPING ORDER BY EFF_DATE DESC) RN, DESCR FROM T1) WHERE RN=1); SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST')); SELECT * FROM T2 WHERE LENGTH(DESCR)=1 ORDER BY EMP_NO; ROLLBACK; ALTER SYSTEM FLUSH BUFFER_CACHE; ALTER SYSTEM FLUSH BUFFER_CACHE; merge into t2 t2 using ( select emp_no,grouping, max(descr) keep(dense_rank last order by eff_date) descr from t1 group by emp_no,grouping) t1 on (t1.emp_no=t2.emp_no and t1.grouping=t2.grouping) when matched then update set t2.descr=t1.descr ; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST')); SELECT * FROM T2 WHERE LENGTH(DESCR)=1 ORDER BY EMP_NO; ROLLBACK; SPOOL OFF
What is the output of the above?
SQL_ID 8w16pv37zxuh5, child number 0
-------------------------------------
UPDATE T2 SET DESCR=( SELECT DESCR FROM (SELECT
EMP_NO, GROUPING, ROW_NUMBER() OVER (PARTITION
BY EMP_NO, GROUPING ORDER BY EFF_DATE DESC) RN, DESCR
FROM T1) T1 WHERE RN=1 AND T1.EMP_NO=T2.EMP_NO
AND T1.GROUPING=T2.GROUPING) WHERE (T2.EMP_NO,T2.GROUPING) IN (
SELECT EMP_NO, GROUPING FROM (SELECT
EMP_NO, GROUPING, ROW_NUMBER() OVER (PARTITION BY
EMP_NO, GROUPING ORDER BY EFF_DATE DESC) RN, DESCR FROM
T1) WHERE RN=1)
Plan hash value: 2277482977
-----------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1 | | 0 |00:00:06.12 | 59000 | 3282 | 5 | | | |
| 1 | UPDATE | T2 | 1 | | 0 |00:00:06.12 | 59000 | 3282 | 5 | | | |
|* 2 | HASH JOIN SEMI | | 1 | 1 | 17 |00:00:03.06 | 3289 | 3280 | 5 | 816K| 816K| 1167K (0)|
| 3 | TABLE ACCESS FULL | T2 | 1 | 100 | 100 |00:00:00.03 | 7 | 6 | 0 | | | |
| 4 | VIEW | VW_NSO_1 | 1 | 1000K| 30 |00:00:03.03 | 3282 | 3274 | 5 | | | |
|* 5 | VIEW | | 1 | 1000K| 30 |00:00:03.03 | 3282 | 3274 | 5 | | | |
|* 6 | WINDOW SORT PUSHED RANK| | 1 | 1000K| 67 |00:00:03.03 | 3282 | 3274 | 5 | 36864 | 36864 | 25M (1)|
| 7 | TABLE ACCESS FULL | T1 | 1 | 1000K| 1000K|00:00:00.01 | 3275 | 3269 | 0 | | | |
|* 8 | VIEW | | 17 | 16667 | 17 |00:00:03.06 | 55675 | 0 | 0 | | | |
|* 9 | WINDOW SORT PUSHED RANK | | 17 | 16667 | 566K|00:00:02.53 | 55675 | 0 | 0 | 1541K| 615K| 1369K (0)|
|* 10 | TABLE ACCESS FULL | T1 | 17 | 16667 | 566K|00:00:01.13 | 55675 | 0 | 0 | | | |
-----------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T2"."EMP_NO"="EMP_NO" AND "T2"."GROUPING"="GROUPING")
5 - filter("RN"=1)
6 - filter(ROW_NUMBER() OVER ( PARTITION BY "EMP_NO","GROUPING" ORDER BY INTERNAL_FUNCTION("EFF_DATE") DESC )<=1)
8 - filter("RN"=1)
9 - filter(ROW_NUMBER() OVER ( PARTITION BY "EMP_NO","GROUPING" ORDER BY INTERNAL_FUNCTION("EFF_DATE") DESC )<=1)
10 - filter(("EMP_NO"=:B1 AND "GROUPING"=:B2))
EMP_NO GROUP DESCR
---------- ----- -----
0 CC W
0 EE F
1111 BB F
1111 DD U
1111 FF Y
2222 AA W
2222 CC T
2222 EE K
3333 BB Z
3333 FF I
3333 DD W
4444 AA Z
4444 EE G
4444 CC S
5555 DD L
5555 BB Y
5555 FF X
SQL_ID 93cj2ck69n4kg, child number 0
-------------------------------------
merge into t2 t2 using ( select emp_no,grouping, max(descr)
keep(dense_rank last order by eff_date) descr from t1 group by
emp_no,grouping) t1 on (t1.emp_no=t2.emp_no and
t1.grouping=t2.grouping) when matched then update set t2.descr=t1.descr
Plan hash value: 4231777338
-----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------
| 0 | MERGE STATEMENT | | 1 | | 1 |00:00:01.53 | 3301 | 3277 | | | |
| 1 | MERGE | T2 | 1 | | 1 |00:00:01.53 | 3301 | 3277 | | | |
| 2 | VIEW | | 1 | | 17 |00:00:01.50 | 3282 | 3275 | | | |
|* 3 | HASH JOIN | | 1 | 43 | 17 |00:00:01.50 | 3282 | 3275 | 921K| 921K| 1181K (0)|
| 4 | VIEW | | 1 | 43 | 30 |00:00:01.47 | 3275 | 3269 | | | |
| 5 | SORT GROUP BY | | 1 | 43 | 30 |00:00:01.47 | 3275 | 3269 | 73728 | 73728 | |
| 6 | TABLE ACCESS FULL| T1 | 1 | 1000K| 1000K|00:00:00.01 | 3275 | 3269 | | | |
| 7 | TABLE ACCESS FULL | T2 | 1 | 100 | 100 |00:00:00.03 | 7 | 6 | | | |
-----------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T1"."EMP_NO"="T2"."EMP_NO" AND "T1"."GROUPING"="T2"."GROUPING")
EMP_NO GROUP DESCR
---------- ----- -----
0 CC Z
0 EE Z
1111 BB Z
1111 DD X
1111 FF Z
2222 AA Z
2222 CC Z
2222 EE Z
3333 BB Z
3333 FF Z
3333 DD Z
4444 AA Z
4444 EE Z
4444 CC Z
5555 DD Z
5555 BB Z
5555 FF Z
From the point of view of performance, Maxim’s solution is a clear winner. It is interesting to note that the value of the DESCR column in table T2 differs for the two approaches.
Recent Comments