Re: Cartesian join: What is this BUFFER SORT accomplishing?

From: Radoulov, Dimitre <cichomitiko_at_gmail.com>
Date: Tue, 21 Nov 2006 22:29:09 +0100
Message-ID: <45636fa4$0$49197$14726298@news.sunsite.dk>

"Jaap W. van Dijk" <j.w.vandijk.removethis_at_hetnet.nl> wrote in message news:456367a0.2501765_at_news.hetnet.nl...
> Hi,
>
> I'm performing a (intended) cartesian join of a table of one row, to a
> big table (9.2.0.5 on Open VMS):
>
> SELECT *
> FROM one_row_table, big_table.
>
> This is the EXPLAIN PLAN:
>
> SELECT STATEMENT
> MERGE JOIN CARTESIAN
> TABLE ACCESS FULL one_row_table
> BUFFER SORT
> TABLE ACCESS FULL big_table
>
> a. What does the BUFFER SORT accomplish? Nothing needs to be compared,
> so why need a sort?

Not answering all your questions.
From Tom Kyte's forum:

<quote>
buffer sort is an optimization -- in this case, it is buffering the results of
that full scan so as to avoid having to perform LIOS on the data over and over
and over in the cartesian join. the data may or may not actually be sorted for
real.
</quote>

> b. Why is it called a BUFFER SORT, and not SORT (JOIN) as for normal
> mergejoins? Is it something else?
> c. If we need sorts, why then is the first table not sorted?

If you want to disable it, quoting Wolfgang Breitling from the oracle forum:

<quote>
Try setting _optimizer_sortmerge_join_enabled to false. You can do that at the session level.
Be advised that tampering with _ (hidden) parameters is unsupported unless directed so by Oracle support (and I am not Oracle support). </quote>

My quick test (10.2), note LIO/elapsed:

SQL> select * from
2 onerow, big;

41918 rows selected.

Elapsed: 00:00:03.87

Execution Plan

Plan hash value: 2395362084

| 0 | SELECT STATEMENT | | 42963 | 5454K| 142 (5)| 00:00:02 |
| 1 | MERGE JOIN CARTESIAN| | 42963 | 5454K| 142 (5)| 00:00:02 |
| 2 | TABLE ACCESS FULL | ONEROW | 1 | 2 | 3 (0)| 00:00:01 |
| 3 | BUFFER SORT | | 42963 | 5370K| 139 (6)| 00:00:02 |
| 4 | TABLE ACCESS FULL | BIG | 42963 | 5370K| 139 (6)| 00:00:02 |

Note

dynamic sampling used for this statement

Statistics

          7  recursive calls
          0  db block gets
        656  consistent gets
          0  physical reads
          0  redo size
    2243933  bytes sent via SQL*Net to client
      31203  bytes received via SQL*Net from client
       2796  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      41918  rows processed

SQL> alter session set "_optimizer_sortmerge_join_enabled"=false;

Session altered.

Elapsed: 00:00:00.00
SQL> select * from
2 onerow, big;

41918 rows selected.

Elapsed: 00:00:03.60

Execution Plan

Plan hash value: 4044947260

|   0 | SELECT STATEMENT   |        | 42963 |  5454K|   142   (5)| 00:00:02 
|
|   1 |  NESTED LOOPS      |        | 42963 |  5454K|   142   (5)| 00:00:02 
|
|   2 |   TABLE ACCESS FULL| ONEROW |     1 |     2 |     3   (0)| 00:00:01 
|
|   3 |   TABLE ACCESS FULL| BIG    | 42963 |  5370K|   139   (6)| 00:00:02 
|
-----------------------------------------------------------------------------

Note

dynamic sampling used for this statement

Statistics

          0  recursive calls
          0  db block gets
       3333  consistent gets
          0  physical reads
          0  redo size
    2243933  bytes sent via SQL*Net to client
      31203  bytes received via SQL*Net from client
       2796  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      41918  rows processed

Regards
Dimitre Received on Tue Nov 21 2006 - 15:29:09 CST