Fast way to query latest record?

Question

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 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.

Answer 1

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.

Answer 2

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