Skip to main content

Search

Welcome to Tibero GTS!

How to Control Join Order (Basic Example)

Document Type | Technical Information

Category | Tuning

Document Number | TTUTI014

 

Overview

Join order can be a factor that affects SQL performance.
This document explains how to control join order using hints (ordered, leading), and how to control it through join conditions without using hints.
Note
The test was conducted in the following environment.
 
[tibero@T1:/]$ tbboot -v Tibero 6 (DB 6.0 FS06_CS_1703) Linux stopbugs 2.6.9-89.ELsmp #1 SMP Mon Jun 22 12:31:33 EDT 2009 x86_64 x86_64 x86_64 GNU/Linux version (little-endian)
 
Patch files (none)
 
 
 
 

Method

Creating Tables for Example Execution

Create 5 tables: t1, t2, t3, t4, t5.

create table t1(c1, c2) as
select
    level, level
from dual
connect by level <= 5000;

create table t2(c1, c2) as
select
    level, level
from dual
connect by level <= 4000;

create table t3(c1, c2) as
select
    level, level
from dual
connect by level <= 3000;

create table t4(c1, c2) as
select
    level, level
from dual
connect by level <= 2000;

create table t5(c1, c2) as
select
    level, level
from dual
connect by level <= 1000;

exec dbms_stats.gather_table_stats('TIBERO', 'T1');
exec dbms_stats.gather_table_stats('TIBERO', 'T2');
exec dbms_stats.gather_table_stats('TIBERO', 'T3');
exec dbms_stats.gather_table_stats('TIBERO', 'T4');
exec dbms_stats.gather_table_stats('TIBERO', 'T5');
 
 

Controlling Join Order Using the ORDERED Hint

If you want to set the join order as T1 - T2 - T3 - T4 - T5, use the ORDERED hint as shown below and specify the tables in order after the FROM clause.

set feedback off;
set

select /*+ ordered */ *
from t1, t2, t3, t4, t5
where
    t1.c1 = t2.c1
    and t1.c1 = t3.c1
    and t1.c1 = t4.c1
    and t1.c1 = t5.c1
    and t1.c2 between 1 and 10;

set linesize 200
set pagesize 0
select * from table(dbms_xplan.display_cursor(null, null, 'BASIC LAST SQL -CARDS -COST -ELAPTIME'));

-----------------------------------------
| ID | Operation           | Name       |
-----------------------------------------
| 1  | HASH JOIN           |            |
| 2  | HASH JOIN           |            |
| 3  | HASH JOIN           |            |
| 4  | HASH JOIN           |            |
| 5  | TABLE ACCESS (FULL) | T1         |
| 6  | TABLE ACCESS (FULL) | T2         |
| 7  | TABLE ACCESS (FULL) | T3         |
| 8  | TABLE ACCESS (FULL) | T4         |
| 9  | TABLE ACCESS (FULL) | T5         |
-----------------------------------------
 
 

Controlling Join Order Using the LEADING Hint

If you want to set the join order as T1 - T2 - T4 - T3 - T5, use the LEADING(t1 t2 t4 t3 t5) hint as shown below.

select /*+ LEADING(t1 t2 t4 t3 t5) */ *
from t2, t5, t1, t3, t4
where
    t1.c1 = t2.c1
    and t1.c1 = t3.c1
    and t1.c1 = t4.c1
    and t1.c1 = t5.c1
    and t1.c2 between 1 and 10;

set linesize 200;
set pagesize 0;
select * from table(dbms_xplan.display_cursor(null, null, 'BASIC LAST SQL -CARDS -COST -ELAPTIME'));

-----------------------------------------
| ID | Operation           | Name       |
-----------------------------------------
| 1  | HASH JOIN           |            |
| 2  | HASH JOIN           |            |
| 3  | HASH JOIN           |            |
| 4  | HASH JOIN           |            |
| 5  | TABLE ACCESS (FULL) | T1         |
| 6  | TABLE ACCESS (FULL) | T2         |
| 7  | TABLE ACCESS (FULL) | T4         |
| 8  | TABLE ACCESS (FULL) | T3         |
| 9  | TABLE ACCESS (FULL) | T5         |
-----------------------------------------
 
 

Controlling Join Order Without Using Hints

If you want to set the join order as T1 - T2 - T4 - T3 - T5, write the WHERE clause as shown below.
To make the join with t4.c1 depend on the value of t2.c1, change the existing join condition t1.c1 = t4.c1 to t1.c1 + 0*t2.c1 = t4.c1, and similarly modify other join conditions as shown.
select *
from t2, t5, t1, t3, t4
where
    t1.c1 = t2.c1
    and t1.c1 + 0 * t2.c1 = t4.c1
    and t1.c1 + 0 * t4.c1 = t3.c1
    and t1.c1 + 0 * t3.c1 = t5.c1
    and t1.c2 between 1 and 10;

set linesize 200;
set pagesize 0;
select * from table(dbms_xplan.display_cursor(null, null, 'BASIC LAST SQL -CARDS -COST -ELAPTIME'));

-----------------------------------------
| ID | Operation           | Name       |
-----------------------------------------
| 1  | HASH JOIN           |            |
| 2  | HASH JOIN           |            |
| 3  | HASH JOIN           |            |
| 4  | HASH JOIN           |            |
| 5  | TABLE ACCESS (FULL) | T1         |
| 6  | TABLE ACCESS (FULL) | T2         |
| 7  | TABLE ACCESS (FULL) | T4         |
| 8  | TABLE ACCESS (FULL) | T3         |
| 9  | TABLE ACCESS (FULL) | T5         |
-----------------------------------------
 
 
 
 
 

Related to