Re: Why ORACLE apply "rowcount" before "distinct"?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 23 Dec 1999 07:43:43 -0500
Message-ID: <g5646s8ondtdllllml7l5eu7jsuv8dkih7@4ax.com>

A copy of this was sent to pavel1704_at_my-deja.com (if that email address didn't require changing) On Thu, 23 Dec 1999 12:03:09 GMT, you wrote:

>I have a query , that contain condition on maximum "rownum" , and also
>DISTINCT clause...
>
>SELECT
> COUNT(DISTINCT(DOC_KEY))
>FROM
> FOLDER_DOC
>WHERE
> ROWNUM < 6000 AND
> (
> FOLDER_KEY = '332' or
> FOLDER_KEY = '333' or
> FOLDER_KEY = '334' or
> FOLDER_KEY = '335'
> )
>
>The problem is , that ORACLE always , first apply "ROWNUM" restriction,
>and
>after that on remained dataset, apply "DISTINCT" condition. So if
>original
>( without DISTINCT and ROWNUM ) could bring 10000 records, among which
>7000
>are distinct, factually this query bring 5000 , because ORACLE first of
>all
>take 6000 first records , and ON THEM apply "DISTINCT" ,and it can
>result in
>only 4000 or 5000 records... Dose exist any way to change this order of
>execution , or some another solution (except writing PL/SQL
>procedures) ???
>

use inline views:

tkyte_at_8.0> select count( distinct(object_id) ) 2 from all_objects
3 where rownum < 100

  4     and ( owner = 'SYSTEM' or
  5                   owner = 'SYS' )

6 /

COUNT(DISTINCT(OBJECT_ID))

tkyte_at_8.0>
tkyte_at_8.0> select *
2 from ( select count(distinct(object_id))

  3             from all_objects
  4            where ( owner = 'SYSTEM' or
  5                            owner = 'SYS' )
  6             )

7 where rownum < 100
8 /

COUNT(DISTINCT(OBJECT_ID))

generate the answer you want in the inline view and then apply the rownum to that.

Given your example -- i'm confused as to the rownum at all since a count(distinct(X)) without a GROUP BY will always return exactly 1 row anyway.....

>Pavel
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu Dec 23 1999 - 06:43:43 CST