获取历史表的每个外键的最后一个条目的正确方法是什么？

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

我总是想知道在某个外键的历史表中找到最后一个条目的正确方法是什么?

Example:

We assume we have a table like this:

我们假设我们有一个这样的表:

tHistory (ID | FK_User | State | Timestamp)

tHistory(ID | FK_User |状态|时间戳)

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

在Oracle中读取每个FK_User ID的lates条目的正确方法是什么?我总是使用一个子选择,但它看起来不对我...还有其他方法吗?

3 个解决方案

#1

An alternative could be to use a correlated sub-query to determine the last date...

另一种方法是使用相关的子查询来确定最后的日期......

SELECT
  *
FROM
  tHistory
WHERE
  timestamp = (
    SELECT
      MAX(timestamp)
    FROM
      tHistory latest
    WHERE
      FK_User = tHistory.FK_User
  )

#2

SELECT  *
FROM    (
        SELECT  h.*,
                ROW_NUMBER() OVER 
                (PARTITION BY h.FK_User ORDER BY timestamp DESC) AS rn
        FROM    tHistory h
        )
WHERE   rn = 1

#3

Good question.

Before version 8i, you would need to use a subquery, for which you need to access the thistory table two times. When analytic functions were introduced, you could skip the second table access by using the ROW_NUMBER analytic function inside an inline view, like Quassnoi has showed you.

在版本8i之前,您需要使用子查询,您需要两次访问thistory表。引入分析函数时,您可以使用内联视图中的ROW_NUMBER分析函数跳过第二个表访问,就像Quassnoi向您展示的那样。

However, since version 9 there is an even better and more performant way in Oracle. Just group by the FK_USER and use the aggregate functions FIRST or LAST.

但是,从版本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

首先:http://download.oracle.com/docs/cd/B10501_01/server.920/a96540/functions45a.htm#SQLRF00641最后:http://download.oracle.com/docs/cd/B10501_01/server.920 /a96540/functions57a.htm#83735

Here is an example with a table thistory:

这是一个表格thistory的例子:

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

First the pre-8i variant with the subquery:

首先是带有子查询的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.

Now the analytic function variant:

现在解析函数变体:

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 variant 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 watch the plans carefully, you'll see that the last one is the most efficient. And it doesn't require an inline view.

如果你仔细观察计划,你会发现最后一个是最有效的。它不需要内联视图。

Hope this helps.

希望这可以帮助。

Regards,
Rob.

#1