快速查询最新记录的方法? [英] Fast way to query latest record?

问题描述

我有一个这样的表:

USER |  PLAN |  START_DATE  |   END_DATE
1    |  A    |  20110101    |   NULL
1    |  B    |  20100101    |   20101231
2    |  A    |  20100101    |   20100505

如果END_DATE为null，则表示该用户当前具有该计划.

In a way that if END_DATE is null, means that this user has that plan currently active.

我要查询的是: (a)他现行的现行计划，或(b)他参与的最新计划.对于每个给定的用户，我只需要返回一行.

What I want to query is: (a) the current plan he has active, or (b) the lastest plan he was into. I need only one row returned for each given user.

现在，我设法通过使用联合和子查询做到了这一点，但是碰巧表很大，而且效率不高. 你们中的每个人都可以有一种更快的查询方式吗?

Now, I managed to do that in using unions and sub queries, but it happens that table is massive and these are not efficient enough. Would any of you guys have a quicker way to query that?

谢谢

这里的大多数答案都返回一个值.那是我的坏事.我的意思是为每个用户返回一个值，但一次返回所有用户.我已经修改了我可以回答的问题(并纠正了问题)，但只是明确说明以备将来参考.

Most answers here return a single value. That was my bad. What I meant was to return a single value per user but all users at once. I've adapted the answers I could (and corrected the question) but just making it clear for future reference.

推荐答案

如果没有有关数据和表的更多信息，这个问题很难回答.当您在评论中说您拥有所需的所有索引时，这些索引是什么?

This question is a little hard to answer without further information about the data and the table. When you say in your comment that you have all the indexes that you need, what are these indexes?

此外，时间段是否邻接且不重叠?您能获取最近的START_DATE期间吗?

Also, are the time periods abutting and non-overlapping? Can you just get the period with the latest START_DATE?

查看END_DATE的问题是普通的B树索引不能索引NULL.因此，形式为where end_date is nulll的谓词不太可能使用索引.您可以在列中使用位图索引，因为那些索引类型会将索引设为空，但是由于位图索引的其他一些缺点，这可能并不理想.

The problem with looking at END_DATE is that a normal B-Tree index doesn't index NULLs. So, a predicate of the form where end_date is nulll is unlikely to use the index. You could use a bitmap index with the column as those type of indexes do index nulls but that might not be ideal because of some of the other drawbacks of bitmap indexes.

由于上述原因，我可能会使用与以下查询类似的查询:

For the reasons given above, I would probably use a query similar to the one below:

select user, plan, start_date, end_date
from (
  select 
    user, 
    plan, 
    start_date, 
    end_date, 
    row_number() over (partition by user order start_date desc) as row_num_1,
    row_number() over (partition by user order end_date desc nulls first) as row_num_2
  from user_table
  where user = :userid
)
where row_num_1 = 1

根据确切的要求，您可以在此处使用row_num_1或row_num_2列.

You could probably use either the row_num_1 or the row_num_2 column here depending on the exact requirements.

OR

select user, plan, start_date, end_date
from (
  select 
    user, 
    plan, 
    start_date, 
    end_date, 
  from user_table
  where user = :userid
  order by start_date desc
)
where rownum = 1

无论您是试图让所有用户还是一个用户，第一个查询都应该起作用.第二个查询仅适用于一个用户.

The first query should work whether you are trying get all the users back or just one. The second query will only work with one user.

如果您可以使用架构的更多详细信息(索引，开始/结束日期的含义)来扩展问题，则可能会得到更好的答案.

If you can augment the question with more details of the schema (indexes, meaning of the start/end date) you are likely to get better answers.

这篇关于快速查询最新记录的方法?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！