Re: 10g: get multiple records with same value in specific column consecutively without 'order by'?
From: Frank Bergemann <FBergemann_at_web.de>
Date: Wed, 8 Dec 2010 10:21:42 -0800 (PST)
Message-ID: <0da327fb-99d1-414b-81e2-e0c28abd5c85_at_n10g2000yqd.googlegroups.com>
On 8 Dez., 19:05, John Hurley <hurleyjo..._at_yahoo.com> wrote:
> Frank:
>
>
>
> > i have a performance problem for distributing selected data to
> > parallel pipelined functions. The worker-threads are started with huge
> > delay, because *all* data from base 'select' are fetched first, then
> > they are distributed (delayed) to the worker-threads. I want the data
> > to be pumped to the worker-thread right away - while the base 'select'
> > is still busy.
> > The problem is due to a trailing 'order by column_x' in the base
> > 'select'. The reason for that again is, that i want to be sure, that
> > all records which have the same value for column_x are delivered
> > successively one after another without other records interleaving.
>
> > I would like to have something less restrictive than 'order by'.
> > Like 'group by'. But there is nothing to aggregate here. I just want
> > to force, that all records with column_x = <some value> are delivered
> > successively in the stream. But i don't care, if all records, which
> > have column_x = 7 are delivered before or after all records, which
> > have column_x = 5.
>
> Can you add into the SQL a where clause ( WHERE column_x
> = :bind_variable ) and still use the ORDER BY column_x and make sure
> that you have effective index on column_x?
>
> Do you have an index on the column at all that you are ordering by?
Date: Wed, 8 Dec 2010 10:21:42 -0800 (PST)
Message-ID: <0da327fb-99d1-414b-81e2-e0c28abd5c85_at_n10g2000yqd.googlegroups.com>
On 8 Dez., 19:05, John Hurley <hurleyjo..._at_yahoo.com> wrote:
> Frank:
>
>
>
> > i have a performance problem for distributing selected data to
> > parallel pipelined functions. The worker-threads are started with huge
> > delay, because *all* data from base 'select' are fetched first, then
> > they are distributed (delayed) to the worker-threads. I want the data
> > to be pumped to the worker-thread right away - while the base 'select'
> > is still busy.
> > The problem is due to a trailing 'order by column_x' in the base
> > 'select'. The reason for that again is, that i want to be sure, that
> > all records which have the same value for column_x are delivered
> > successively one after another without other records interleaving.
>
> > I would like to have something less restrictive than 'order by'.
> > Like 'group by'. But there is nothing to aggregate here. I just want
> > to force, that all records with column_x = <some value> are delivered
> > successively in the stream. But i don't care, if all records, which
> > have column_x = 7 are delivered before or after all records, which
> > have column_x = 5.
>
> Can you add into the SQL a where clause ( WHERE column_x
> = :bind_variable ) and still use the ORDER BY column_x and make sure
> that you have effective index on column_x?
>
> Do you have an index on the column at all that you are ordering by?
Yes, there is an index on this column.
But if i use WHERE column_x = :bind_variable, what to provide for
the :bind_variable?
I mean, i want all of them.
rgds,
Frank
Received on Wed Dec 08 2010 - 12:21:42 CST