Fast way to query latest record?

I have a table of the sort:

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

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

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?

Thanks,

[EDIT] Most answers here return a single value. That was my bad. What I meant was t开发者_StackOverflow社区o 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?

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

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

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.

---

CREATE TABLE XY
( USERID      INTEGER                 NOT NULL
, PLAN        VARCHAR2(8)             NOT NULL
, START_DATE  DATE                    NOT NULL
, END_DATE    DATE                    )
  TABLESPACE USERS;


INSERT INTO XY ( USERID, PLAN, START_DATE, END_DATE )
       VALUES ( 1, 'A', To_Date('22-05-2011 00:00:00', 'DD-MM-YYYY HH24:MI:SS'), To_Date('22-05-2011 00:00:00', 'DD-MM-YYYY HH24:MI:SS') );
INSERT INTO XY ( USERID, PLAN, START_DATE, END_DATE )
       VALUES ( 1, 'B', To_Date('01-04-2011 00:00:00', 'DD-MM-YYYY HH24:MI:SS'), NULL );
INSERT INTO XY ( USERID, PLAN, START_DATE, END_DATE )
       VALUES ( 2, 'A', To_Date('03-05-2011 00:00:00', 'DD-MM-YYYY HH24:MI:SS'), To_Date('04-05-2011 00:00:00', 'DD-MM-YYYY HH24:MI:SS') );
INSERT INTO XY ( USERID, PLAN, START_DATE, END_DATE )
       VALUES ( 2, 'B', To_Date('15-05-2011 00:00:00', 'DD-MM-YYYY HH24:MI:SS'), To_Date('20-05-2011 00:00:00', 'DD-MM-YYYY HH24:MI:SS') );
COMMIT WORK;

SELECT USERID, PLAN, END_DATE, START_DATE
  FROM (SELECT USERID,
               PLAN,
               END_DATE,
               START_DATE,
               ROW_NUMBER() OVER(PARTITION BY USERID ORDER BY END_DATE DESC) SEQUEN
          FROM XY)
 WHERE SEQUEN < 2

---

This may help:

SELECT user,plan,end_date,start_date 
FROM ( SELECT users,plans,end_date,start_date, DENSE_RANK() OVER ( PARTITION BY user 
                                                                   ORDER BY end_date DESC) sequen 
        FROM table_name 
     ) 
WHERE sequen <= 2

---

Have you tried to limit the resultset with rownum?

select  plan
from    (
        select  plan
        from    YourTable
        where   User = 1
        order by
                case when end_date is null then '99991231' else end_date end desc
        )
where   rownum < 2

---

AFAIK Using CASE and sub queries will cause your query to become very slow. So better to use them with care. How About:

SELECT User, Plan, start_Date, MAX(End_Date) FROM Plans WHERE User NOT IN 
(SELECT User FROM Plans WHERE End_Date IS NULL)
GROUP BY Start_Date, Plan, User  
UNION  
SELECT User,Plan,Start_Date FROM Plans WHERE End_Date IS NULL

I'm not a SQL guru. consider this just as a suggestion.
Hope this helps.

---

Does this work?

SELECT U.user 
,(SELECT Plan FROM t WHERE t.user=u.user AND end_date IS NULL LIMIT 1) AS Current_Plan
,(SELECT Plan FROM t WHERE t.user=u.user AND end_date IS NOT NULL ORDER BY end_date DESC LIMIT 1) AS Last_Plan
FROM 
( SELECT DISTINCT USER FROM t ) AS U

If it is slow, please send us the EXPLAIN output for the query.

---

How about this?

select PLAN
from USER_TABLE
where END_DATE is null or END_DATE = (
        select max(END_DATE)
        from USER_TABLE
        where USER = 1 and END_DATE is not null)
    and USER = 1

---

I suggest the following :

with t as 
(select 1 as col_id, 1 as USER_id, 'A' as PLAN , 20110101 as START_DATE, NULL as  END_DATE from dual union all
 select 2,1,'B', 20100101,20101231 from dual union all
 select 3,2,'A', 20100102,20100505 from dual union all
 select 4,2,'C', 20100101,20100102 from dual)
--
SELECT user_id, plan
  FROM (SELECT user_id,
               plan,
               MAX(nvl(END_DATE, 99999999)) over(PARTITION BY user_id) max_date,
               nvl(END_DATE, 99999999) END_DATE
          FROM t)
 WHERE max_date = end_date