Re: question for the wise?

From: FC <flavio_at_tin.it>
Date: Fri, 10 May 2002 07:50:00 GMT
Message-ID: <IYKC8.43157$zW3.497072@news1.tin.it>

Let me clarify a few of points here:

1. Oracle is 8.1.7.0.0, so I can't rely on 9i's features.
2. the benchmark is very stable, executing the same code over and over again does not improve response time.
3. the actual SQL statement follows:

select a.line, a.stn, a.done_cps
  from (select line, stn, sum((select nvl(sum(cps),0) from table (select cnt

                                 from tote_log
                                where line = x.line
                                  and stn = x.stn
                                  and tote = x.tote)
                             )) as done_cps

from tote_log x
/* SOLUTION 1 */

where line = 2 and stn between 1 and info_const.last_manual
/* SOLUTION 2 */

-- where line = 2 and stn between 1 and 23 group by line, stn) a

There is an index on tote_log made up of columns line, tote, stn respectively.

actual benchmarks:

1. 2.560 - 2.501
2. 2.103 - 2.073

Received on Fri May 10 2002 - 02:50:00 CDT