Re: Problems with Order by in 7.3.2.3?

You could try making sure the optimizer is set to rule and not cost based or else select the results into a temp table and orderby there.

Stephen A. Rodgers wrote:

> We're running a 205GB data warehouse on Sun Solaris with Oracle 7.3.2.3.
>
> I have a simple query which selects one column from a table with a where
> clause that
> says WHERE IN another single table query. With NO order by on this SQL,
> I get 209
> rows returned within 1/2 second. If I place an ORDER BY clause on the
> statement
> and order by the single column that's being returned, I get nothing back
> for over 40 mins.!!!
> (In fact, I give up and kill the query at that point).
>
> I don't understand how adding the ORDER BY to a query that returns one
> column and
> 209 rows could trash the query, but it seems to be doing that on my
> database. When I
> do explain plans on the query, both with and without the ORDER BY
> clause, the plan
> seems to change (not including the fact that an extra step to do the
> sort is of course
> added). This appears to be a bug to me, but I'm wondering if anyone
> else has come
> across this problem.
>
> The sort parameters are all tweaked pretty nicely on this database, but
> even if they weren't,
> it doesn't seem that sorting 209 rows would be a problem even with
> fairly badly set
> parameters.
>
> Anyone come across anything like this??
>
> Thanks,
>
> Stephen A. Rodgers
Received on Wed Jan 14 1998 - 00:00:00 CST