Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: question for the wise?

Re: question for the wise?

From: Richard Kuhler <noone_at_nowhere.com>
Date: Fri, 10 May 2002 17:32:15 GMT
Message-ID: <zuTC8.111835$zN.50817806@twister.socal.rr.com>

  1. What are the columns for IDX_TOTE_LOG_BY_L_T_S?
  2. What is the plan with the constant instead of the bind?

Richard

FC wrote:
>
> "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 - 12:32:15 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US