Re: find the record with the maxvalue

A simple answer might be that the select max() uses only an index and doesn't touch the table. This is true if lotjobsequence is the only or leading column in an index. Thus you would have two very quick index lookups and one direct table access by rowid. In the second example you would retrieve and then sort all rows matching the where clause before the first row is returned to your program. Depending on the number of rows returned, this could be significantly slower than the previous example.

The complicated answer might involve how the indexes are being used (or not used), caching, or any number of things. You should really run explain plan on the individual SQL statements. This will show you the difference in the execution plan and should show you why one takes longer. There should be a script in the rdbms/admin directory under the oracle directory to create the explain plan table that is used by the explain plan utility. Look for something with xplain in the name (it varies by release and platform). The syntax for the explain plan statement and examples should be in the SQL*Plus or SQL language manual.

Good Luck

> Table Lot has 6,000 rows.
> Table LotJob has 30,000,000 rows.
> Table Route has 5,000 rows
>
> The SPC Code 1 is pretty straitforward. It serace Lot table and get Lot.
> Then serch LotJob by Lot, and get MAX(LotJobSequence), and use this max
> value to find the record that contains this max value.
> it do 2 scan.
>
> SPC Code 2 uses ORDER BY to let the record with the MAX value on the
> first row and use FOREACH "once" to get the record.
> Because the SPL Code2 only do 1 scan, it should be faster than SPL Code 1.
> BUt, it doesn't.
>
> BUT, the SPC Code 1 take less time to execute!!!!
> (SPL Code1 13 secs and SPL Code2 30 secs)
>
> CAn you give me some advice?? Thanks.
> Jack
>
Received on Thu Nov 14 1996 - 00:00:00 CET