Re: Sorting Oracle Records by TimeStamp field Question!

From: Jim Kennedy <kennedy-downwithspammersfamily_at_attbi.net>
Date: Thu, 20 Nov 2003 15:32:22 GMT
Message-ID: <a85vb.254617$HS4.2298151@attbi_s01>

"Mario" <mariano.gagliardi_at_filogix.com> wrote in message news:39c27ec.0311200607.8d4d8d9_at_posting.google.com...
> I have a question regarding the results returned by the following
> query:
>
> select EVENT_ID, PRIMARY_KEY from EVENT where PTS_CONFIRM = 0 and
> ROWNUM = 1 order by EVENT_PC
>
>
> where the index is defined as
> CREATE INDEX EVENT_PC ON EVENT (PTS_CONFIRM, CREATE_TIMESTAMP)
>
> My question(s) are as follows:
> 1) Will this not provide me with the oldest record where the
> PTS_CONFIRM field is zero? I am being told that it cannot guarantee
> that we will retrieve the oldest record? How is this possible?
>
> 2) Secondly, I am told having a timestamp field in an index is an
> expensive operation is this true?

The rownum is determined BEFORE the order by and thus your query does not work. How about

select EVENT_ID, PRIMARY_KEY from EVENT e where e.primary_key=(select max(create_timestamp) from EVENT e2 where e2.pts_confirm=0);

The index should be used unless the table is small then it is cheaper for Oracle to scan the table.

For one record an order by is unnecessary. Jim Received on Thu Nov 20 2003 - 09:32:22 CST