Re: Optimizing max function

On Tue, 20 Jul 1999 21:41:46 +0800, Connor McDonald <connor_mcdonald_at_yahoo.com> wrote:

>Even better:
>
>select /*+ INDEX_DESC(a b) */ the_column
>from table a
>where rownum < 2;
>
>will scoot to the "bottom" of the index and just get that one value
>(which will be the max)

Does using the INDEX_DESC hint guarantee that the largest value will be retrieved? I know an ORDER BY clause won't work in this case because the sort is performed after the ROWNUM expression is evaluated.

Just to complicate matters, my index is actually a compound index. I am specifying the value for the first part of the index in a WHERE clause, then looking for the largest value in the second part. For example, my index is on SET_ID,GROUP_NO and I am looking for the largest value for GROUP_NO where SET_ID=2. My cursor is:

	select   /*+ index_desc (storage_q storage_q_ix) */ GROUP_NO
	from	   STORAGE_Q
	where	   SET_ID = 2
	order by GROUP_NO desc;

but as you know this retrieves all of the rows when I am only interested in the first. Will replacing the ORDER BY with AND ROWNUM < 2 give me the same results?

Thanks for the help.

--
Mark Wagoner
To reply, remove no.spam from my e-mail address Received on Wed Jul 21 1999 - 13:22:54 CDT