Geek asks and answers

What is the correct way to get the last record for each foreign key of the history table?

I always wondered what the correct way would be to find the last entry in the history table of a particular foreign key?

Example:

Suppose we have a table:

tHistory (ID | FK_User | Status | Timestamp)

What would be the correct way in Oracle to read the lates entry for each FK_User identifier? I always used a subtitle for this, but for me it doesn't look ... any other ways to do this?

+3
source share
3 answers

An alternative would be to use a correlated subquery to determine the latest date ...

SELECT
  *
FROM
  tHistory
WHERE
  timestamp = (
    SELECT
      MAX(timestamp)
    FROM
      tHistory latest
    WHERE
      FK_User = tHistory.FK_User
  )
+4
source 
SELECT  *
FROM    (
        SELECT  h.*,
                ROW_NUMBER() OVER 
                (PARTITION BY h.FK_User ORDER BY timestamp DESC) AS rn
        FROM    tHistory h
        )
WHERE   rn = 1
+2
source

.

8i , . , , ROW_NUMBER , Quassnoi.

, 9, Oracle . FK_USER FIRST LAST.

FIRST: http://download.oracle.com/docs/cd/B10501_01/server.920/a96540/functions45a.htm#SQLRF00641 LAST: http://download.oracle.com/docs/cd/B10501_01/server.920/a96540/functions57a.htm#83735

:

SQL> create table thistory (id,fk_user,state,timestamp)
  2  as
  3  select 1, 'Me', 'D', sysdate from dual union all
  4  select 2, 'Me', 'C', sysdate-1 from dual union all
  5  select 3, 'Me', 'B', sysdate-2 from dual union all
  6  select 4, 'Me', 'A', sysdate-3 from dual union all
  7  select 5, 'You', 'B', sysdate-11 from dual union all
  8  select 6, 'You', 'A', sysdate-12 from dual
  9  /

Table created.

SQL> exec dbms_stats.gather_table_stats(user,'thistory')

PL/SQL procedure successfully completed.

SQL> alter session set statistics_level = all
  2  /

Session altered.

SQL> set serveroutput off

pre-8i :

SQL> select *
  2    from thistory
  3   where timestamp =
  4         ( select max(timestamp)
  5             from thistory latest
  6            where fk_user = thistory.fk_user
  7         )
  8  /

        ID FK_USER S TIMESTAMP
---------- ------- - -------------------
         1 Me      D 19-05-2011 11:20:48
         5 You     B 08-05-2011 11:20:48

2 rows selected.

SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'))
  2  /

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------
SQL_ID  306v8p42zdz34, child number 0
-------------------------------------
select *   from thistory  where timestamp =        ( select max(timestamp)            from thistory latest
       where fk_user = thistory.fk_user        )

Plan hash value: 2894184026

----------------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------
|*  1 |  HASH JOIN           |          |      1 |      2 |      2 |00:00:00.01 |       7 |  1155K|  1155K|  478K (0)|
|   2 |   VIEW               | VW_SQ_1  |      1 |      2 |      2 |00:00:00.01 |       3 |       |       |          |
|   3 |    HASH GROUP BY     |          |      1 |      2 |      2 |00:00:00.01 |       3 |       |       |          |
|   4 |     TABLE ACCESS FULL| THISTORY |      1 |      6 |      6 |00:00:00.01 |       3 |       |       |          |
|   5 |   TABLE ACCESS FULL  | THISTORY |      1 |      6 |      6 |00:00:00.01 |       4 |       |       |          |
----------------------------------------------------------------------------------------------------------------------

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

   1 - access("TIMESTAMP"="VW_COL_1" AND "FK_USER"="THISTORY"."FK_USER")


22 rows selected.

:

SQL> select *
  2    from ( select h.*
  3                , row_number() over (partition by h.fk_user order by timestamp desc) as rn
  4             from thistory h
  5         )
  6   where rn = 1
  7  /

        ID FK_USER S TIMESTAMP                   RN
---------- ------- - ------------------- ----------
         1 Me      D 19-05-2011 11:20:48          1
         5 You     B 08-05-2011 11:20:48          1

2 rows selected.

SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'))
  2  /

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------
SQL_ID  b7zscht24wa2s, child number 0
-------------------------------------
select *   from ( select h.*               , row_number() over (partition by h.fk_user order by timestamp desc)
as rn            from thistory h        )  where rn = 1

Plan hash value: 2357375523

--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------
|*  1 |  VIEW                    |          |      1 |      6 |      2 |00:00:00.01 |       3 |       |       |          |
|*  2 |   WINDOW SORT PUSHED RANK|          |      1 |      6 |      6 |00:00:00.01 |       3 |  9216 |  9216 | 8192  (0)|
|   3 |    TABLE ACCESS FULL     | THISTORY |      1 |      6 |      6 |00:00:00.01 |       3 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------

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

   1 - filter("RN"=1)
   2 - filter(ROW_NUMBER() OVER ( PARTITION BY "H"."FK_USER" ORDER BY INTERNAL_FUNCTION("TIMESTAMP") DESC )<=1)


21 rows selected.

And finally, the most efficient option using aggregate functions:

SQL> select max(id) keep (dense_rank last order by timestamp) id
  2       , fk_user
  3       , max(state) keep (dense_rank last order by timestamp) state
  4       , max(timestamp)
  5    from thistory
  6   group by fk_user
  7  /

        ID FK_USER S MAX(TIMESTAMP)
---------- ------- - -------------------
         1 Me      D 19-05-2011 11:20:48
         5 You     B 08-05-2011 11:20:48

2 rows selected.

SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'))
  2  /

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------
SQL_ID  9gcbnuf776q27, child number 0
-------------------------------------
select max(id) keep (dense_rank last order by timestamp) id      , fk_user      , max(state) keep
(dense_rank last order by timestamp) state      , max(timestamp)   from thistory  group by fk_user

Plan hash value: 76026975

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------
|   1 |  SORT GROUP BY     |          |      1 |      2 |      2 |00:00:00.01 |       3 |  9216 |  9216 | 8192  (0)|
|   2 |   TABLE ACCESS FULL| THISTORY |      1 |      6 |      6 |00:00:00.01 |       3 |       |       |          |
--------------------------------------------------------------------------------------------------------------------


14 rows selected.

If you closely follow the plans, you will see that the last of them is the most effective. And it does not require an inline view.

Hope this helps.

Regards,
Rob.

+1
source

More articles:


All Articles