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

From: Hoang-Vu PHUNG <hoang-vu_at_freesurf.ch>
Date: Thu, 23 Dec 1999 13:38:46 +0100
Message-ID: <83t56i$itd$1@news1.sunrise.ch>

Hi,
try to use subquery for example
select my_count
from
(select COUNT(DISTINCT(DOC_KEY)) "my_count" FROM
FOLDER_DOC
WHERE
  FOLDER_KEY = '332' or
   FOLDER_KEY = '333' or
FOLDER_KEY = '334' or
  FOLDER_KEY = '335'
)
where rowcount < 6000

Hope that helps

<pavel1704_at_my-deja.com> wrote in message news:83t316$ark$1_at_nnrp1.deja.com...
> 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
Received on Thu Dec 23 1999 - 06:38:46 CST