Re: SELECT DISTINCT slow, how can I speed up

From: <>
Date: Thu, 14 Aug 2008 21:35:54 +0200
Message-ID: <>

On Thu, 14 Aug 2008 11:18:41 -0700 (PDT), Barry Bulsara <> wrote:

>If I type SELECT DISTINCT a1,a2,a3,a5,a10
>only 20-50 rows come back and the query takes 28 seconds and there is
>a lot less network IO.

Sure, every query consists of

Distinct requires SORT and SORT is in the execute phase. Execute is on the database server.

>I only want the 20-50 rows but I don't want to wait 28 seconds. As
>Oracle seems to find all 1792 rows in 2.14seconds, why does it take 28
>seconds only to return the unique ones.

using DISTINCT requires a SORT operation. Sorts can be tuned.
>These are ad hoc queries so I am not trying to optimize a single
>query. My question is Is there a more quick way of generally returning
>a DISTINCT set of rows other than using DISTINCT in the SELECT

You would still need a SORT, wouldn't you?

Sybrand Bakker
Senior Oracle DBA
Received on Thu Aug 14 2008 - 14:35:54 CDT

Original text of this message