Re: Select max fro a table!

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
>
>
>
Received on Fri Jan 22 1999 - 14:10:54 CST