Re: Optimzer Oddities

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 30 Apr 2004 16:18:42 +0000 (UTC)
Message-ID: <c6tu92$keq$1@sparta.btinternet.com>

The optimizer can rewrite subqueries that contain aggregates to turn them into joins, and has been able to for quite some time.

After a couple of minutes thought, I can't come up with a way to rewrite this particular query.

-- 
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

June  2004      UK - Optimising Oracle Seminar
July 2004 USA West Coast, Optimising Oracle Seminar
August 2004 Charlotte NC, Optimising Oracle Seminar
September 2004 USA East Coast, Optimising Oracle Seminar
September2004 UK - Optimising Oracle Seminar

"srivenu" <srivenu_at_hotmail.com> wrote in message
news:1a68177.0404292315.4857ad7e_at_posting.google.com...


> Jonathan,

> Please correct me if i'am wrong in making the observation that since

> the sub query contains an aggregate function, the optimizer cannot

> rewrite the query and covert it into a join.

> Or are there any other ways that the CBO might rewrite the query ?

> thanks & regards

> Srivenu

Received on Fri Apr 30 2004 - 11:18:42 CDT