Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Re: Oracle - explicit index use

Re: Oracle - explicit index use

From: Connor McDonald <connor_mcdonald_at_yahoo.com>
Date: Thu, 14 Sep 2000 19:47:25 +0800
Message-ID: <39C0BACD.736F@yahoo.com>

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 worse
Received on Thu Sep 14 2000 - 06:47:25 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US