Re: question for the wise?

On Fri, 10 May 2002 07:50:00 GMT, "FC" <flavio_at_tin.it> wrote:

>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
>
>

Looks to me you have one inline view (always differentiate between inline views and subqueries, these are inline views) too much you should be able to simply join tote and an inline view with the group by from tote_log. Don't know why you are making it yourself so difficult
One side effect of inline views is that many people start using them immediately, without even trying to resolve it in ordinary sql. Also: you are advised *NOT* to *SIMPLIFY* your statements on initial posts, unless you want to keep this group busy by not telling the complete story.

Regards

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Fri May 10 2002 - 03:13:45 CDT