RE: 9205 Query behavior

From: Connor McDonald <hamcdc_at_yahoo.co.uk>
Date: Tue, 18 May 2004 08:51:11 +0100 (BST)
Message-ID: <20040518075111.95210.qmail@web25204.mail.ukl.yahoo.com>

Well, Oracle *did* say that rule optimizer was on the way out...

SQL> select *
2 from
3 ( select distinct deptno

  4     from ( select deptno, job, min(sal)
  5            from scott.emp
  6            group by deptno, job )
  7     where deptno = 20 )

8 /

DEPTNO

        20
        20
        20

3 rows selected.

SQL> select /*+ ALL_ROWS */ *
2 from
3 ( select distinct deptno

  4     from ( select deptno, job, min(sal)
  5            from scott.emp
  6            group by deptno, job )
  7     where deptno = 20 )

8 /

DEPTNO

1 row selected.

Have to admit - there's been plenty of times in the past when 'rule' worked when a CBO decision gave incorrect results. Nice to see CBO getting its own back :-)

Cheers
Connor

"Natural Join B.V." <lex.de.haan_at_naturaljoin.nl> wrote: > you might want to check out this (related) bug:
>
> http://metalink.oracle.com/metalink/plsql/ml2_documents.showFrameDocument?
> p_database_id=BUG&p_id=3327512
>
> fixed in 10g Release 2, and I guess it will be backported too...
>
> Cheers,
> Lex.
>
> > When trying out hints I only focused on no_unnest and totally forgot about
> > no_merge which should have been the more obvious one. That is consistent
> > with the result I got using the rownum pseudo-column which also prevents

Connor McDonald
Co-author: "Mastering Oracle PL/SQL - Practical Solutions" ISBN: 1590592174 web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: connor_mcdonald_at_yahoo.com

Coming Soon! "Oracle Insight - Tales of the OakTable"

"GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day"

Yahoo! Messenger - Communicate instantly..."Ping" your friends today! Download Messenger Now http://uk.messenger.yahoo.com/download/index.html

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 Tue May 18 2004 - 02:48:04 CDT