Re: question for the wise?

From: FC <flavio_at_tin.it>
Date: Fri, 10 May 2002 12:37:05 GMT
Message-ID: <R9PC8.26941$CN3.821867@news2.tin.it>

"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message news:730ndu0qgpleplne2j7j5u8kt3u5310lq2_at_4ax.com...
> >
> 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
>

Yeah, you're right, the outer select is redundant, I deleted it, I found out I had already written a simpler version indeed, but I've had so many headches with this cumbersome object syntax that I got lost.

The SQL statement then becomes:

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
where line = 2

   and stn between 1 and info_const.last_manual group by line, stn

Cnt is a varray of 10 elements containing an object made up of several fields, <cps> is one of them.

Here is its plan:

SELECT STATEMENT
  SORT GROUP BY
    INDEX RANGE SCAN IDX_TOTE_LOG_BY_L_T_S So, the original question is still there. Is there any way to make the SQL statement above faster avoinding hard-coded values?

PS: I apologise for symplifying the SQL statement. Received on Fri May 10 2002 - 07:37:05 CDT