9205 Query behavior

From: Jamadagni, Rajendra <Rajendra.Jamadagni_at_espn.com>
Date: Sun, 16 May 2004 17:55:57 -0400
Message-ID: <A186CBDC8B1D61438BC50F1A77E91F73075BD8E9@xchgbrsm1.corp.espn.pvt>

On 9205 database, load $OH/admin/utlsampl.sql

run following query ... and observe ...

set feedback on
prompt Step 1
select deptno, job, min(sal) from scott.emp group by deptno, job
/

prompt Step 2
select distinct deptno from ( select deptno, job, min(sal) from scott.emp group by deptno, job ) where deptno = 20
/

prompt Step 3
select * from ( select distinct deptno from ( select deptno, job, min(sal) from scott.emp group by deptno, job ) where deptno = 20 )
/

The output I see is as follows ...

oraclei_at_rhea-FAMDEV1> sqlplus scott/tiger SQL*Plus: Release 9.2.0.5.0 - Production on Sun May 16 17:45:36 2004 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to:
Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production With the Partitioning, Real Application Clusters, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production

17:45:37 SQL> @st1
Step 1

DEPTNO JOB MIN(SAL)
---------- --------- ----------

        10 CLERK           1300
        10 MANAGER         2450
        10 PRESIDENT       5000
        20 CLERK            800
        20 ANALYST         3000
        20 MANAGER         2975
        30 CLERK            950
        30 MANAGER         2850
        30 SALESMAN        1250

9 rows selected.

Step 2

DEPTNO

1 row selected.

Step 3

DEPTNO

        20
        20
        20

3 rows selected.

What's up with the three rows in step 3??

I noticed this first reported on Metalink so wanted to try it out. According to metalink posting, this can be observed on 10g as well. I just reproduced this under 9202.

Maybe it's that today is Sunday and I am working, but if step 2 returns 1 row, why does step 3 return 3 rows?? The execution plan for the last step is as follows ...

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE 1 0 VIEW

   2    1     SORT (GROUP BY)
   3    2       TABLE ACCESS (FULL) OF 'EMP'

It seems to be missing a SORT (UNIQUE) step required for DISTINCT clause.

Raj

Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. select standard_disclaimer from company_requirements; QOTD: Any clod can have facts, having an opinion is an art !

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Sun May 16 2004 - 16:53:13 CDT