Oracle 11g: Another New Algorithm

If you are or have ever been a SQL developer, it’s very likely you’ve been asked to return the rows from two joined tables, including all the rows from both tables that do not have a corresponding row in the other table. Oracle 9i introduced the FULL OUTER JOIN syntax to better address this scenario. Now it looks as if 11g has introduced a new algorithm to handle that.

So how can you get a look at this?

Step 1: Create a table

create table gark(col1 number);

Step 2: Write the query

select a.col1,b.col1
  from gark a full outer join gark b
   on a.col1=b.col1;

Step 3: Display the 11g plan

PLAN_TABLE_OUTPUT
----------------------------------------------------------
Plan hash value: 4017346954

----------------------------------------------------------
| Id  | Operation             | Name     | Rows  | Bytes |
----------------------------------------------------------
|   0 | SELECT STATEMENT      |          |     1 |    26 |
|   1 |  VIEW                 | VW_FOJ_0 |     1 |    26 |
|*  2 |   HASH JOIN FULL OUTER|          |     1 |    26 |
|   3 |    TABLE ACCESS FULL  | GARK     |       |       |
|   4 |    TABLE ACCESS FULL  | GARK     |     1 |    13 |
----------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("A"."COL1"="B"."COL1")

Note
-----
   - dynamic sampling used for this statement

It doesn’t look very impressive, except…

Step 4: Display the 10g plan

PLAN_TABLE_OUTPUT
-----------------------------------------------------
Plan hash value: 3812554007

-----------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes |
-----------------------------------------------------
|   0 | SELECT STATEMENT     |      |     2 |    52 |
|   1 |  VIEW                |      |     2 |    52 |
|   2 |   UNION-ALL          |      |       |       |
|*  3 |    HASH JOIN OUTER   |      |     1 |   104 |
|   4 |     TABLE ACCESS FULL| GARK |     1 |    65 |
|   5 |     TABLE ACCESS FULL| GARK |     1 |    39 |
|*  6 |    HASH JOIN ANTI    |      |     1 |    26 |
|   7 |     TABLE ACCESS FULL| GARK |     1 |    13 |
|   8 |     TABLE ACCESS FULL| GARK |     1 |    13 |
-----------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("A"."COL1"="B"."COL1"(+))
   6 - access("A"."COL1"="B"."COL1")

Note
-----
   - dynamic sampling used for this statement

One step further…

Looking at these two plans, I thought, “It’s obvious what Oracle changed and how they did it !” I thought I would illustrate the benefit of this new algorithm by raising the “TABLE ACCESS FULL” payload. To do that, you can change the table and data as below:

alter table gark add (col2 varchar2(1000));

begin
  for i in 1..100000 loop
     insert into gark values (i,lpad('$',999,'$'));
  end loop;
commit;
end;
/

exec dbms_stats.gather_table_stats(user,'GARK');

In my configuration, I’ve moved from ~62900 consistent gets (10.2.0.1) to ~34300 consistent gets (11.1.0.6). From this, you might think we know what happened behind the scenes. However, this simple test case shows up two additional differences. Obviously, nothing in Oracle works as simply as we imagine it does; and 11g is unlikely to change this one (and only?) optimizer rule. I’ll describe those two other changes in my next posts when I’ll be able to explain them.