Re: New Feature: "Cost Based Transformation"

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Sat, 3 Sep 2005 07:31:10 +0000 (UTC)
Message-ID: <dfbjfu$ac0$1@nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com>

<xhoster_at_gmail.com> wrote in message
news:20050902152737.943$Z6_at_newsreader.com...
> mccmx_at_hotmail.com wrote:
>> Anyone heard of this new feature in 10g: "Cost Based Transformation".
>>
>> Its basically new functionality which enables the optimizer to cost
>> transformation operations like subquery unnesting and predicate pushing
>> etc.
>
> I don't understand this concept. Whatever access path is implied by your
> query-as-written, should still be among the legitimate access paths for
> the
> query after it is unnested. So how would it be possible/meaningful for an
> unnesting not to be costed? It seems like costing the unnesting is an
> implicit part of the CBO.
>
> Xho
>

Xho,

Consider the following:
select
outer.*
from emp outer
where outer.sal >
(select avg(inner.sal)
from emp inner
where inner.dept_no = outer.dept_no
);

In principle, the following will happen with different versions of Oracle:

8i: No form of unnesting considered.
SELECT STATEMENT
  FILTER
    TABLE ACCESS (FULL) OF 'EMP'
    SORT (AGGREGATE)
      TABLE ACCESS (FULL) OF 'EMP' 9i Subquery unnested to produce a join - only the join is costed SELECT STATEMENT
  HASH JOIN
    VIEW OF 'VW_SQ_1'

      SORT (GROUP BY)
        TABLE ACCESS (FULL) OF 'EMP'

TABLE ACCESS (FULL) OF 'EMP' 10g -

Either plan might appear, as the optimizer will cost both the un-transformed query and the transformed query and (in principle) choose the cheaper.

The article that Mark mentioned gives a list of transformations that are heuristic - i.e. rule-driven - as unnesting was in 9i, and a list of those that are costed, as unnesting is supposed to be in 10g. I'm not sure that the lists are entirely correct, though, but do give you the idea of the sorts of things that the optimizer could be doing to your query without considering the cost.

-- 
Regards

Jonathan Lewis

Now waiting on the publishers:    Cost Based Oracle - Volume 1

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

http://www.jlcomp.demon.co.uk/appearances.html
Public Appearances - schedule updated 8th July 2005

Received on Sat Sep 03 2005 - 02:31:10 CDT