RE: 9205 Query behavior
- From: "Jamadagni, Rajendra" <Rajendra.Jamadagni@xxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 17 May 2004 11:51:22 -0400
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
----------
20
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Wolfgang Breitling
Sent: Monday, May 17, 2004 11:43 AM
To: oracle-l@xxxxxxxxxxxxx
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
----------
20
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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
