Re: 10g: get multiple records with same value in specific column consecutively without 'order by'?
From: Gerard H. Pille <ghp_at_skynet.be>
Date: Wed, 08 Dec 2010 19:44:57 +0100
Message-ID: <4cffd229$0$14254$ba620e4c_at_news.skynet.be>
Frank Bergemann wrote:
> Hi,
>
> 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.
>
> Is there somehing like 'order by column_x ANY'?
> Or a 'group by' syntax which doesn't require aggregation?
>
> ???
>
> - thanks!
>
> rgds,
> Frank
Date: Wed, 08 Dec 2010 19:44:57 +0100
Message-ID: <4cffd229$0$14254$ba620e4c_at_news.skynet.be>
Frank Bergemann wrote:
> Hi,
>
> 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.
>
> Is there somehing like 'order by column_x ANY'?
> Or a 'group by' syntax which doesn't require aggregation?
>
> ???
>
> - thanks!
>
> rgds,
> Frank
Add a hint to force using the index, and a "where column_x > ''". In RBO times this used to do the trick.
But using the index, the overall performance will be lower, much lower, if you need to fetch all records. Received on Wed Dec 08 2010 - 12:44:57 CST