RE: 9205 Query behavior

From: Jamadagni, Rajendra <Rajendra.Jamadagni_at_espn.com>
Date: Mon, 17 May 2004 11:51:22 -0400
Message-ID: <A186CBDC8B1D61438BC50F1A77E91F73075BD938@xchgbrsm1.corp.espn.pvt>

Wolfgang,
Do a set autotrace explain ... And you'll see that the in the last step, Oracle forgets the SORT (UNIQUE) ... Which is visible in step 2.

11:49:43 SQL> set autotrace on explain
11:49:51 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.
Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE 1 0 SORT (GROUP BY)
2 1 TABLE ACCESS (FULL) OF 'EMP' Step 2

DEPTNO

1 row selected.
Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE 1 0 SORT (UNIQUE)

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

Step 3

DEPTNO

        20
        20
        20

3 rows selected.
Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE 1 0 VIEW

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

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 !

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Wolfgang Breitling Sent: Monday, May 17, 2004 11:43 AM
To: oracle-l_at_freelists.org
Subject: Re: 9205 Query behavior

I wonder what kind of optimizer bug it is. I tried various hints, even RULE !, to no avail. However, adding rownum to the innermost sql produces the expected result:

SQL> l

1 select distinct deptno from ( select rownum, deptno, job, 2* min(sal) from scott.emp group by rownum, deptno, job) SQL> / DEPTNO

         10
         20
         30

3 rows selected.

SQL> l

1 select * from ( select distinct deptno from ( select rownum, deptno, job,

2* min(sal) from scott.emp group by rownum, deptno, job) where deptno = 20 )
SQL> / DEPTNO

1 row selected.

That seems to indicate some unnesting related error. However, using no_unnest hints does not change the behaviour.

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 Mon May 17 2004 - 10:52:59 CDT