Dion Cho – Oracle Performance Storyteller

We are natural born scientists

Right Join – The secret of swapping join input

with 6 comments

It’s not well known that Oracle really enjoys swapping the join input to get join performance enhancement.

For instance,

select * from t1, t2 where t1.c1 = t2.c1(+)

The join order of above query should be { t1 –> t2 }. It’s natural born feature of outer join. But as of Oracle 10g, the join order can be swapped. The join order is now { t2 –> t1 }. Yes, the input table(t1) is located right, not left, thus named right join.

The main usage of right join is hash outer, hash semi and hash anti join.

Following is a simple test case to demonstrate the benefit of right join.

1. Make objects

UKJA@ukja102> -- t1 is big
UKJA@ukja102> create table t1(c1, c2)
2  as
3  select level, rpad('x',10)
4  from dual
5  connect by level <= 200000
6  ;

Table created.

UKJA@ukja102> -- t2 is small
UKJA@ukja102> create table t2(c1, c2)
2  as
3  select level, rpad('x',10)
4  from dual
5  connect by level <= 100
6  ;

Table created.

UKJA@ukja102> exec dbms_stats.gather_table_stats(user, '&1', no_invalidate=>false);

PL/SQL procedure successfully completed.

UKJA@ukja102> exec dbms_stats.gather_table_stats(user, '&1', no_invalidate=>false);

PL/SQL procedure successfully completed.

2. This is how general hash join works. The small table is always positioned left and chosen as a build table.

UKJA@ukja102> -- general join
UKJA@ukja102> select /*+ gather_plan_statistics */ count(*)
2  from t1, t2
3  where t1.c1 = t2.c1
4  ;

COUNT(*)
----------
100

UKJA@ukja102> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last outline'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  6dxdkru1dwwsc, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(*) from t1, t2 where t1.c1 = t2.c1

Plan hash value: 4274056747

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE     |      |      1 |      1 |      1 |00:00:02.53 |     588 |       |       |          |
|*  2 |   HASH JOIN         |      |      1 |      1 |    100 |00:00:02.53 |     588 |  1066K|  1066K| 1138K (0)|
|   3 |    TABLE ACCESS FULL| T2   |      1 |    100 |    100 |00:00:00.01 |       3 |       |       |          |
|   4 |    TABLE ACCESS FULL| T1   |      1 |    199K|    200K|00:00:00.80 |     585 |       |       |          |
-----------------------------------------------------------------------------------------------------------------

...

36 rows selected.

3. How about outer join? Left small table is an ideal choice.

UKJA@ukja102> -- outer join with small build table
UKJA@ukja102> select /*+ gather_plan_statistics */ count(*)
2  from t1, t2
3  where t2.c1 = t1.c1(+)
4  ;

COUNT(*)
----------
100

UKJA@ukja102> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last outline'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  fp5uv3ugpja3h, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(*) from t1, t2 where t2.c1 = t1.c1(+)

Plan hash value: 851489259

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE     |      |      1 |      1 |      1 |00:00:02.53 |     588 |       |       |          |
|*  2 |   HASH JOIN OUTER   |      |      1 |    100 |    100 |00:00:02.53 |     588 |  1066K|  1066K| 1146K (0)|
|   3 |    TABLE ACCESS FULL| T2   |      1 |    100 |    100 |00:00:00.01 |       3 |       |       |          |
|   4 |    TABLE ACCESS FULL| T1   |      1 |    199K|    200K|00:00:00.80 |     585 |       |       |          |
-----------------------------------------------------------------------------------------------------------------

...
36 rows selected.

4. What if a very big table(t1) is left part of the outer join? Building up big table is big job. For this reason, Oracle swaps the join order. You see that the memory usage is same as previous one. The secret is the magical “SWAP_JOIN_INPUTS” hint.

UKJA@ukja102> -- outer join with big build table
UKJA@ukja102> select /*+ gather_plan_statistics */ count(*)
2  from t1, t2
3  where t1.c1 = t2.c1(+)
4  ;

COUNT(*)
----------
200000

UKJA@ukja102>
UKJA@ukja102> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last outline'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  c141vrf262yg7, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(*) from t1, t2 where t1.c1 = t2.c1(+)

Plan hash value: 3306442619

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE        |      |      1 |      1 |      1 |00:00:03.76 |     588 |       |       |          |
|*  2 |   HASH JOIN RIGHT OUTER|      |      1 |    199K|    200K|00:00:03.20 |     588 |  1066K|  1066K| 1146K (0)|
|   3 |    TABLE ACCESS FULL   | T2   |      1 |    100 |    100 |00:00:00.01 |       3 |       |       |          |
|   4 |    TABLE ACCESS FULL   | T1   |      1 |    199K|    200K|00:00:00.80 |     585 |       |       |          |
--------------------------------------------------------------------------------------------------------------------

Outline Data
-------------

/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "T1"@"SEL$1")
FULL(@"SEL$1" "T2"@"SEL$1")
LEADING(@"SEL$1" "T1"@"SEL$1" "T2"@"SEL$1")
USE_HASH(@"SEL$1" "T2"@"SEL$1")
SWAP_JOIN_INPUTS(@"SEL$1" "T2"@"SEL$1")
END_OUTLINE_DATA
*/

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("T1"."C1"="T2"."C1")

37 rows selected.

5. What if the swapping is disabled? See how more memory we need to build up the big table. In production, this would be a disaster.

UKJA@ukja102> -- outer join with big build table and swap disabled
UKJA@ukja102> select /*+ gather_plan_statistics no_swap_join_inputs(t2) */ count(*)
2  from t1, t2
3  where t1.c1 = t2.c1(+)
4  ;

COUNT(*)
----------
200000

UKJA@ukja102> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last outline'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  cbx3kd6puqhsd, child number 0
-------------------------------------
select /*+ gather_plan_statistics no_swap_join_inputs(t2) */ count(*) from t1, t2 where t1.c1 = t2.c1(+)

Plan hash value: 3781991007

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE     |      |      1 |      1 |      1 |00:00:03.93 |     588 |       |       |          |
|*  2 |   HASH JOIN OUTER   |      |      1 |    199K|    200K|00:00:03.39 |     588 |  4486K|  1381K| 6557K (0)|
|   3 |    TABLE ACCESS FULL| T1   |      1 |    199K|    200K|00:00:00.80 |     585 |       |       |          |
|   4 |    TABLE ACCESS FULL| T2   |      1 |    100 |    100 |00:00:00.01 |       3 |       |       |          |
-----------------------------------------------------------------------------------------------------------------

Outline Data
-------------

/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "T1"@"SEL$1")
FULL(@"SEL$1" "T2"@"SEL$1")
LEADING(@"SEL$1" "T1"@"SEL$1" "T2"@"SEL$1")
USE_HASH(@"SEL$1" "T2"@"SEL$1")
END_OUTLINE_DATA
*/

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("T1"."C1"="T2"."C1")

36 rows selected.

6. Fortunately, Oracle automatically swaps the join order whenever considered efficient. Very nice feature, isn’t it? Following test case clearly shows that semi join and anti join also get a great benefit from right join implementation.

UKJA@ukja102> -- semi join with small preceding table
UKJA@ukja102> select /*+ gather_plan_statistics */ count(*)
2  from t2
3  where c1 in (select c1 from t1)
4  ;

COUNT(*)
----------
100

UKJA@ukja102>
UKJA@ukja102> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last outline'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  fvznb82zkymja, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(*) from t2 where c1 in (select c1 from t1)

Plan hash value: 791474359

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE     |      |      1 |      1 |      1 |00:00:00.01 |       7 |       |       |          |
|*  2 |   HASH JOIN SEMI    |      |      1 |      1 |    100 |00:00:00.01 |       7 |  1066K|  1066K| 1146K (0)|
|   3 |    TABLE ACCESS FULL| T2   |      1 |    100 |    100 |00:00:00.01 |       3 |       |       |          |
|   4 |    TABLE ACCESS FULL| T1   |      1 |    199K|    100 |00:00:00.01 |       4 |       |       |          |
-----------------------------------------------------------------------------------------------------------------

Outline Data
-------------

/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$5DA710D3")
UNNEST(@"SEL$2")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$2")
FULL(@"SEL$5DA710D3" "T2"@"SEL$1")
FULL(@"SEL$5DA710D3" "T1"@"SEL$2")
LEADING(@"SEL$5DA710D3" "T2"@"SEL$1" "T1"@"SEL$2")
USE_HASH(@"SEL$5DA710D3" "T1"@"SEL$2")
END_OUTLINE_DATA
*/

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("C1"="C1")

39 rows selected.

UKJA@ukja102>
UKJA@ukja102> -- semi join with big preceding table
UKJA@ukja102> select /*+ gather_plan_statistics */ count(*)
2  from t1
3  where c1 in (select c1 from t2)
4  ;

COUNT(*)
----------
100

UKJA@ukja102>
UKJA@ukja102> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last outline'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  2yj9f6nygxnr9, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(*) from t1 where c1 in (select c1 from t2)

Plan hash value: 2146439244

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE       |      |      1 |      1 |      1 |00:00:02.69 |     588 |       |       |          |
|*  2 |   HASH JOIN RIGHT SEMI|      |      1 |      1 |    100 |00:00:02.69 |     588 |  1066K|  1066K| 1146K (0)|
|   3 |    TABLE ACCESS FULL  | T2   |      1 |    100 |    100 |00:00:00.01 |       3 |       |       |          |
|   4 |    TABLE ACCESS FULL  | T1   |      1 |    199K|    200K|00:00:00.80 |     585 |       |       |          |
-------------------------------------------------------------------------------------------------------------------

Outline Data
-------------

/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$5DA710D3")
UNNEST(@"SEL$2")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$2")
FULL(@"SEL$5DA710D3" "T1"@"SEL$1")
FULL(@"SEL$5DA710D3" "T2"@"SEL$2")
LEADING(@"SEL$5DA710D3" "T1"@"SEL$1" "T2"@"SEL$2")
USE_HASH(@"SEL$5DA710D3" "T2"@"SEL$2")
SWAP_JOIN_INPUTS(@"SEL$5DA710D3" "T2"@"SEL$2")
END_OUTLINE_DATA
*/

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("C1"="C1")

40 rows selected.

UKJA@ukja102>
UKJA@ukja102>
UKJA@ukja102> -- semi join with big preceding table and swap disabled
UKJA@ukja102> select /*+ gather_plan_statistics */ count(*)
2  from t1
3  where c1 in (select /*+ no_swap_join_inputs(t2) */ c1 from t2)
4  ;

COUNT(*)
----------
100

UKJA@ukja102>
UKJA@ukja102> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last outline'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  79baf8pvx8m69, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(*) from t1 where c1 in (select /*+ no_swap_join_inputs(t2) */
c1 from t2)

Plan hash value: 3471188453

------------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE      |      |      1 |      1 |      1 |00:00:02.60 |     588 |       |       |          |
|*  2 |   HASH JOIN          |      |      1 |      1 |    100 |00:00:02.60 |     588 |  1066K|  1066K| 1138K (0)|
|   3 |    SORT UNIQUE       |      |      1 |    100 |    100 |00:00:00.01 |       3 |  9216 |  9216 | 8192  (0)|
|   4 |     TABLE ACCESS FULL| T2   |      1 |    100 |    100 |00:00:00.01 |       3 |       |       |          |
|   5 |    TABLE ACCESS FULL | T1   |      1 |    199K|    200K|00:00:00.80 |     585 |       |       |          |
------------------------------------------------------------------------------------------------------------------

Outline Data
-------------

/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$5DA710D3")
UNNEST(@"SEL$2")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$2")
FULL(@"SEL$5DA710D3" "T2"@"SEL$2")
FULL(@"SEL$5DA710D3" "T1"@"SEL$1")
LEADING(@"SEL$5DA710D3" "T2"@"SEL$2" "T1"@"SEL$1")
USE_HASH(@"SEL$5DA710D3" "T1"@"SEL$1")
END_OUTLINE_DATA
*/

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("C1"="C1")

41 rows selected.

UKJA@ukja102>
UKJA@ukja102>
UKJA@ukja102> -- anti join with small preceding table
UKJA@ukja102> select /*+ gather_plan_statistics */ count(*)
2  from t2
3  where c1 not in (select c1 from t1 where c1 is not null)
4         and c1 is not null
5  ;

COUNT(*)
----------
0

UKJA@ukja102>
UKJA@ukja102>
UKJA@ukja102> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last outline'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  9ums9uzv4s7h1, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(*) from t2 where c1 not in (select c1 from t1 where c1 is not
null)       and c1 is not null

Plan hash value: 973407487

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE     |      |      1 |      1 |      1 |00:00:02.62 |     588 |       |       |          |
|*  2 |   HASH JOIN ANTI    |      |      1 |     99 |      0 |00:00:02.62 |     588 |  1066K|  1066K| 1146K (0)|
|*  3 |    TABLE ACCESS FULL| T2   |      1 |    100 |    100 |00:00:00.01 |       3 |       |       |          |
|*  4 |    TABLE ACCESS FULL| T1   |      1 |    199K|    200K|00:00:00.80 |     585 |       |       |          |
-----------------------------------------------------------------------------------------------------------------

Outline Data
-------------

/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$5DA710D3")
UNNEST(@"SEL$2")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$2")
FULL(@"SEL$5DA710D3" "T2"@"SEL$1")
FULL(@"SEL$5DA710D3" "T1"@"SEL$2")
LEADING(@"SEL$5DA710D3" "T2"@"SEL$1" "T1"@"SEL$2")
USE_HASH(@"SEL$5DA710D3" "T1"@"SEL$2")
END_OUTLINE_DATA
*/

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("C1"="C1")
3 - filter("C1" IS NOT NULL)
4 - filter("C1" IS NOT NULL)

42 rows selected.

UKJA@ukja102>
UKJA@ukja102>
UKJA@ukja102> -- anti join with big preceding table
UKJA@ukja102> select /*+ gather_plan_statistics */ count(*)
2  from t1
3  where c1 not in (select c1 from t2 where c1 is not null)
4         and c1 is not null
5  ;

COUNT(*)
----------
199900

UKJA@ukja102>
UKJA@ukja102> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last outline'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  5zaf4u76dq0jr, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(*) from t1 where c1 not in (select c1 from t2 where c1 is not
null)       and c1 is not null

Plan hash value: 1636627778

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE       |      |      1 |      1 |      1 |00:00:03.91 |     588 |       |       |          |
|*  2 |   HASH JOIN RIGHT ANTI|      |      1 |    199K|    199K|00:00:03.40 |     588 |  1066K|  1066K| 1146K (0)|
|*  3 |    TABLE ACCESS FULL  | T2   |      1 |    100 |    100 |00:00:00.01 |       3 |       |       |          |
|*  4 |    TABLE ACCESS FULL  | T1   |      1 |    199K|    200K|00:00:00.80 |     585 |       |       |          |
-------------------------------------------------------------------------------------------------------------------

Outline Data
-------------

/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$5DA710D3")
UNNEST(@"SEL$2")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$2")
FULL(@"SEL$5DA710D3" "T1"@"SEL$1")
FULL(@"SEL$5DA710D3" "T2"@"SEL$2")
LEADING(@"SEL$5DA710D3" "T1"@"SEL$1" "T2"@"SEL$2")
USE_HASH(@"SEL$5DA710D3" "T2"@"SEL$2")
SWAP_JOIN_INPUTS(@"SEL$5DA710D3" "T2"@"SEL$2")
END_OUTLINE_DATA
*/

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("C1"="C1")
3 - filter("C1" IS NOT NULL)
4 - filter("C1" IS NOT NULL)

43 rows selected.

UKJA@ukja102>
UKJA@ukja102>
UKJA@ukja102> -- anti join with big preceding table and swap disabled
UKJA@ukja102> select /*+ gather_plan_statistics no_swap_join_inputs(t2) */ count(*)
2  from t1
3  where c1 not in (select /*+ no_swap_join_inputs(t2) */ c1 from t2 where c1 is not null)
4         and c1 is not null
5  ;

COUNT(*)
----------
199900

UKJA@ukja102>
UKJA@ukja102> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last outline'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  4x9mk1btb97x2, child number 0
-------------------------------------
select /*+ gather_plan_statistics no_swap_join_inputs(t2) */ count(*) from t1 where c1 not in (select
/*+ no_swap_join_inputs(t2) */ c1 from t2 where c1 is not null)       and c1 is not null

Plan hash value: 1513027705

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE     |      |      1 |      1 |      1 |00:00:03.95 |     588 |       |       |          |
|*  2 |   HASH JOIN ANTI    |      |      1 |    199K|    199K|00:00:03.38 |     588 |  4486K|  1381K| 6493K (0)|
|*  3 |    TABLE ACCESS FULL| T1   |      1 |    199K|    200K|00:00:00.80 |     585 |       |       |          |
|*  4 |    TABLE ACCESS FULL| T2   |      1 |    100 |    100 |00:00:00.01 |       3 |       |       |          |
-----------------------------------------------------------------------------------------------------------------

Outline Data
-------------

/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$5DA710D3")
UNNEST(@"SEL$2")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$2")
FULL(@"SEL$5DA710D3" "T1"@"SEL$1")
FULL(@"SEL$5DA710D3" "T2"@"SEL$2")
LEADING(@"SEL$5DA710D3" "T1"@"SEL$1" "T2"@"SEL$2")
USE_HASH(@"SEL$5DA710D3" "T2"@"SEL$2")
END_OUTLINE_DATA
*/

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("C1"="C1")
3 - filter("C1" IS NOT NULL)
4 - filter("C1" IS NOT NULL)

42 rows selected.

But keep in mind that without good statistics, this benefit from automatic swapping is not guaranteed. Also in the complex cases when Oracle can’t calculate the realistic cardinality, the benefit would be blown away.

Under these situations, SWAP_JOIN_INPUTS hint would be a cure.

About these ads

Written by Dion Cho

March 3, 2009 at 7:40 am

6 Responses

Subscribe to comments with RSS.

  1. you did it.
    The Story is lookalike for me except no_swap_join_inputs hint.

    extremedb

    March 3, 2009 at 4:49 pm

  2. Yes. :)
    I didn’t expect you to visit my English blog.

    Anyway, thanks for your inspiration!

    Dion Cho

    March 4, 2009 at 12:00 am

  3. You wrote:
    The join order is now { t1 –> t2 }

    Should it be:
    The join order is now { t2 –> t1 }

    Regards,
    Rudi Kristanto.

    Rudi Kristanto

    March 4, 2009 at 1:40 pm

    • Rudi.
      Fixed. Thanks for the correction.

      Dion Cho

      March 4, 2009 at 11:18 pm

  4. nice explanation :)

    Sharad

    July 9, 2009 at 1:09 pm

  5. Excellent article. Will you please write much more about this subject.

    jeff

    May 15, 2010 at 2:04 pm


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 61 other followers

%d bloggers like this: