Skip to content

Instantly share code, notes, and snippets.

@kleontev

kleontev/join_order.log

Created June 18, 2023 13:48
Show Gist options
  • Save kleontev/71a0d8a318de65a664e241de3f0eeecc to your computer and use it in GitHub Desktop.
Save kleontev/71a0d8a318de65a664e241de3f0eeecc to your computer and use it in GitHub Desktop.
Download ZIP
a quick demo that join order matters performance-wise
Raw
join_order.log
SQL>
SQL> select /*+leading(t3 t2 t1)*/ count(*)
2 from test_10_rows t1
3 join test_1m_rows t2 on t1.id = t2.id
4 join test_2m_rows t3 on t2.id = t3.id
5 /
COUNT(*)
----------
20
Elapsed: 00:00:03.57
SQL>
SQL> select * from table(dbms_xplan.display_cursor(format=>'allstats last'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 0kywxzvagxcwb, child number 0
-------------------------------------
select /*+leading(t3 t2 t1)*/ count(*) from test_10_rows t1 join
test_1m_rows t2 on t1.id = t2.id join test_2m_rows t3 on t2.id = t3.id
Plan hash value: 2901036689
------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem | Used-Tmp|
------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:03.57 | 428K| 433K| 4619 | | | | |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:03.57 | 428K| 433K| 4619 | | | | |
|* 2 | HASH JOIN | | 1 | 20 | 20 |00:00:03.57 | 428K| 433K| 4619 | 2546K| 2546K| 1163K (0)| |
| 3 | TABLE ACCESS FULL | TEST_10_ROWS | 1 | 10 | 10 |00:00:00.01 | 2 | 5 | 0 | | | | |
|* 4 | HASH JOIN | | 1 | 1996K| 2000K|00:00:03.49 | 428K| 433K| 4619 | 97M| 12M| 107M (1)| 38M|
| 5 | TABLE ACCESS FULL| TEST_2M_ROWS | 1 | 2000K| 2000K|00:00:01.13 | 285K| 285K| 0 | | | | |
| 6 | TABLE ACCESS FULL| TEST_1M_ROWS | 1 | 1000K| 1000K|00:00:00.39 | 142K| 142K| 0 | | | | |
------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T1"."ID"="T2"."ID")
4 - access("T2"."ID"="T3"."ID")
25 rows selected.
Elapsed: 00:00:00.03
SQL>
SQL> select /*+leading(t1 t2 t3)*/ count(*)
2 from test_10_rows t1
3 join test_1m_rows t2 on t1.id = t2.id
4 join test_2m_rows t3 on t2.id = t3.id
5 /
COUNT(*)
----------
20
Elapsed: 00:00:01.05
SQL>
SQL> select * from table(dbms_xplan.display_cursor(format=>'allstats last'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 811bfwwbxtyqy, child number 0
-------------------------------------
select /*+leading(t1 t2 t3)*/ count(*) from test_10_rows t1 join
test_1m_rows t2 on t1.id = t2.id join test_2m_rows t3 on t2.id = t3.id
Plan hash value: 3277594784
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:01.05 | 428K| 428K| | | |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:01.05 | 428K| 428K| | | |
|* 2 | HASH JOIN | | 1 | 20 | 20 |00:00:01.05 | 428K| 428K| 2546K| 2546K| 1127K (0)|
|* 3 | HASH JOIN | | 1 | 10 | 10 |00:00:00.35 | 142K| 142K| 2546K| 2546K| 1148K (0)|
| 4 | TABLE ACCESS FULL| TEST_10_ROWS | 1 | 10 | 10 |00:00:00.01 | 2 | 0 | | | |
| 5 | TABLE ACCESS FULL| TEST_1M_ROWS | 1 | 1000K| 1000K|00:00:00.29 | 142K| 142K| | | |
| 6 | TABLE ACCESS FULL | TEST_2M_ROWS | 1 | 2000K| 2000K|00:00:00.59 | 285K| 285K| | | |
-----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T2"."ID"="T3"."ID")
3 - access("T1"."ID"="T2"."ID")
25 rows selected.
Elapsed: 00:00:00.03
SQL>
SQL> exit
Raw
join_order.sql
set echo on
set timi on
set lines 200
set pages 1000
set serverout off
cl scr
whenever sqlerror continue
drop table test_10_rows purge;
drop table test_1m_rows purge;
drop table test_2m_rows purge;
whenever sqlerror exit fail
create table test_10_rows as select rownum as id from dual connect by rownum <= 10;
create table test_1m_rows as select rownum as id, lpad('x', 1000, 'y') as fill from dual connect by rownum <= 1e6;
create table test_2m_rows as select * from test_1m_rows union all select * from test_1m_rows;
alter session set statistics_level = all;
spool join_order.log
select /*+leading(t3 t2 t1)*/ count(*)
from test_10_rows t1
join test_1m_rows t2 on t1.id = t2.id
join test_2m_rows t3 on t2.id = t3.id
/
select * from table(dbms_xplan.display_cursor(format=>'allstats last'));
select /*+leading(t1 t2 t3)*/ count(*)
from test_10_rows t1
join test_1m_rows t2 on t1.id = t2.id
join test_2m_rows t3 on t2.id = t3.id
/
select * from table(dbms_xplan.display_cursor(format=>'allstats last'));
exit
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment