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

Frank:

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?

On 8 Dez., 19:05, John Hurley <hurleyjo... (AT) yahoo (DOT) com> wrote:
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

Frank Bergemann wrote:
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.

On 8 Dez., 18:29, Frank Bergemann <FBergem... (AT) web (DOT) de> wrote:
This is, what i was looking for:
http://www.oracle.com/technology/sam...ral_Cas e.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."