Re: Problems with Order by in 7.3.2.3?

From: Olaf Naumann <ONaumann_at_netcologne.de>
Date: 1998/01/12
Message-ID: <34ba8721.5012652@news.netcologne.de>#1/1

On Sat, 10 Jan 1998 13:24:08 -0500, "Stephen A. Rodgers" <trodgers_at_mindspring.com> 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).

the optimizer does often works really bad if the statistics are outdated. try to run a ANALYZE TABLE MYTABLE ESTIMATE STATISTICS for all tables involved. sometime it helps.

if you just need the query once use a temporary table: 1. CREATE TEMP_TABLE as select ...
2. select * from TEMP_TABLE ORDER BY ...

olaf Received on Mon Jan 12 1998 - 00:00:00 CST