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

Home -> Community -> Usenet -> c.d.o.server -> Re: Select max from a table!

Re: Select max from a table!

From: Kevin Bass <akil1_at_mindspring.com>
Date: Sat, 23 Jan 1999 13:12:13 GMT
Message-ID: <78chs4$d1b$1@camel15.mindspring.com>


There are several variables involved when performing a max on a table. The size of the table can play a role is determining the speed of your sql statement and the existance of a primary key (if there is one) can also play a role in determining the speed of your sql statement.

The sql statement that you orginally used is sufficent for the purpose of getting the maximum value of your date field. Your sql statement takes so long because it is performing a full table scan on Table_A. Some question that you may ask yourself are:
(1) How many records are in Table_A?
(2) Is val_dte a date field? (It is marked as not null but what is the datatype of the field)
(3) Is val_dte the primary key and/or index for Table_A?

Using a hint to force Table_A to know and use the index can be helpful when you do not use the primary key field or indexed field within your sql statement.

Kevin

"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
>>
>>
>>
Received on Sat Jan 23 1999 - 07:12:13 CST

Original text of this message

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