Re: Select max fro a table!

Most likely, the index is unordered. Given a key, it will return the physical location of the record via some hash function or tree traversal, however it need not be ordered in the way you might expect.

On Fri, 22 Jan 1999 20:10:54 -0000, "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote:

>It is a quirky feature that Oracle will not make the 'obvious'
>choice of path in this case, but uses your PK to select all the
>dates (because that is quicker than a tablescan) and then
>does a 'sort group by'.
>
>Try
> select /*+ index_desc(table_a index_pk_name) */
> val_dte
> from
> table_a
> where rownum = 1
> ;
>
>I don't like this hint, as it depends on the existence of
>the index completely, and would silently go wrong if
>someone dropped and rebuilt the index with a different
>name - but you may feel happy with it.
>
>Jonathan Lewis
>Yet another Oracle-related web site: www.jlcomp.demon.co.uk
>
>
>
>Yeung Man wrote in message <788km3$5hc1_at_news.hk.linkage.net>...
>>Hi,
>> We have a table as Table_A ( val_dte not null, count number(4)) where
>>val_dte is primary key. If we issue the sql
>> select max(val_dte) from TAble_A;
>> The sql has a long time to run. We use the explain plan as find the sql
>>use the pk. Do anyone have idea what going on.
>> TIA.
>>
>>Best Regards,
>>Man
>>
>>
>>
>
>

MarK Z, 6b, rhymingly Received on Thu Jan 28 1999 - 13:49:03 CST