Why ORACLE apply "rowcount" before "distinct"?

From: <pavel1704_at_my-deja.com>
Date: Thu, 23 Dec 1999 12:03:09 GMT
Message-ID: <83t316$ark$1@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
only 4000 or 5000 records... Dose exist any way to change this order of execution , or some another solution (except writing PL/SQL procedures) ???

Pavel

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Dec 23 1999 - 06:03:09 CST