Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Select max from a table!
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