Re: group by nosort

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Thu, 8 Jul 1999 11:19:52 +0100
Message-ID: <931429472.967.0.nnrp-06.9e984b29@news.demon.co.uk>

There is an interesting inconsistency here:

One pair of 'reasonable' paths would be:

SORT (GROUP BY NOSORT)

     MERGE JOIN
       INDEX (FULL SCAN) OF T_PK (UNIQUE)
       INDEX (FULL SCAN) OF T_PK (UNIQUE)

SORT (GROUP BY NOSORT)

     MERGE JOIN
       SORT (JOIN)
         TABLE ACCESS (FULL) OF T
       SORT (JOIN)
         TABLE ACCESS (FULL) OF T

After all, since your join mechanism is a merge-join, the cost of getting the two row sources ought to be identical. I would put this down to a quirk (bug ?) in what goes on in the

Recost for ORDER BY ***** section of optimisation

Having said that, it's always done to statistics - even if the occasional path does throw the CBO into an error. As a quick demo that Oracle is capable of recognising that the index is the smart strategy for 'both' tables, I have just run the following:

set autotrace on explain

select

     j1.view_name,
     j1.owner,

count(*)
from

     jpl1 j1,
     jpl1 j2
where
         j1.view_name = j2.view_name

and j1.owner = j2.owner
group by

j1.view_name, j1.owner
;

Where jpl1 has a unique index (view_name,owner) with the following execution plan:

SELECT STATEMENT Optimizer=CHOOSE (Cost=8 Card=392 Bytes=53312) SORT (GROUP BY NOSORT) (Cost=8 Card=392 Bytes=53312)

     NESTED LOOPS (Cost=8 Card=392 Bytes=53312)
        INDEX (FULL SCAN) OF 'J_PK1' (UNIQUE) (Cost=8 Card=392 Bytes=39984)
        INDEX (UNIQUE SCAN) OF 'J_PK1' (UNIQUE)

(Oracle 8.0.4.0.0)

Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk

Sybrand Bakker wrote in message
<931409708.20529.0.pluto.d4ee154e_at_news.demon.nl>...
>First of all, you could question, why you are joining the table to itself
in
>the second table.

>Secondly, you just demonstrated the parser is unable to detect (in the same
>second example) this is one and the same table you are aliasing.

>The only other thing is an index hint. However, this index hint must
include
>an alias, it won't probably help you.

>
>Kirill Richine <krichine_at_radss.com> wrote in message
>news:3783e1fd.91440584_at_news.cadvision.com...
>> Suppose I have a huge table T (c1, c2, c3)
>> and there is a unique index on it T_PK (c1, c2)
>>
>> When I go
>> select c1, c2, count (*)
>> from t
>> group by c1, c2
>> the execution plan is something like this:
>>
>> SORT (GROUP BY NOSORT)
>> INDEX (FULL SCAN) OF T_PK (UNIQUE)
>>
>> If, however, I go
>> select a.c1, a.c2, count (*)
>> from t a, t b
>> where a.c1 = b.c1 and a.c2 = b.c2
>> group by a.c1, a.c2
>>
>> the execution plan goes haywire:
>> SORT (GROUP BY NOSORT)
>> MERGE JOIN
>> INDEX (FULL SCAN) OF T_PK (UNIQUE)
>> SORT (JOIN)
>> TABLE ACCESS (FULL) OF T
>>
>> Why is it doing this?
>> It should be smart enough to see that the only thing it needs to
>> access here is the pk index, which means it does not need to sort in
>> the end. Am I expecting too much?
>>
Received on Thu Jul 08 1999 - 05:19:52 CDT