Re: 10g: get multiple records with same value in specific column consecutively without 'order by'?
Date: Wed, 8 Dec 2010 11:06:05 -0800 (PST)
Message-ID: <2180736f-943b-4f8d-a271-0668bd65538b_at_j29g2000yqm.googlegroups.com>
On 8 Dez., 18:29, Frank Bergemann <FBergem..._at_web.de> 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
This is, what i was looking for:
http://www.oracle.com/technology/sample_code/tech/pl_sql/htdocs/x/Table_Functions_Cursor_Expressions/Parallelizing_Table_Function_Execution_General_Case.htm
"Given that the input rows will be partitioned between different slaves, the integrity of the algorithm requires that all the rows for a given department go to the same slave, and that all these rows are delivered consecutively. (Strictly speaking, the requirement for consecutive delivery is negotiable, but the design of the algorithm to handle this case would need to be much more elaborate. For that reason, Oracle commits to consecutive delivery.) We use the term clustered to signify this type of delivery, and cluster key for the column (in this case “department”) on which the aggregations done. But significantly, the algorithm does not care in what order of cluster key it receives each successive cluster, and Oracle does not guarantee any particular order here." Received on Wed Dec 08 2010 - 13:06:05 CST