Re: SELECT DISTINCT slow, how can I speed up

On Thu, 14 Aug 2008 11:18:41 -0700 (PDT), Barry Bulsara <bbulsara23_at_hotmail.com> 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
parse
execute
fetch

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
>statement.

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

-- 
Sybrand Bakker
Senior Oracle DBA