Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.tools -> Re: Oracle - explicit index use
niallp_at_my-deja.com wrote:
>
> In Oracle, is there a way to access a single row
> based on a specific index. Here's an example:
> I have a table with a unique index made up from
> two columns, user_id, time_stamp. There may be
> thousands of rows for a each user_id. I want to
> access the most recent row for a particular
> user_id without having to read thousands of rows
> and finding the max time_stamp. How can this be
> accomplished in Oracle?
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Check out the INDEX_DESC hint.
Also, a common workaround to this problem is to have an additional column with flags the most recent timestamp for each userid, eg "CURRENT" being Y or null.
A trigger can keep this column updated (watch out for mutating issues)
You could even go to the next level and partition on this column so that you effectively have one table of current rows and another of historical rows. (You'll to allow row migration across the partition)
HTH
-- =========================================== Connor McDonald http://www.oracledba.co.uk We are born naked, wet and hungry...then things get worseReceived on Thu Sep 14 2000 - 06:47:25 CDT