Upgrade to Pro — share decks privately, control downloads, hide ads and more …

How Joins Work

Avatar for Chris Chris
January 01, 2020
Technology
160
0

How Joins Work

Avatar for Chris

Chris

January 01, 2020

More Decks by Chris

See All by Chris
Create reusable SQL expressions with SQL macros
Avatar for Chris chrissaxon
0
140
All About Insert
Avatar for Chris chrissaxon
0
190
Generating days between two dates
Avatar for Chris chrissaxon
0
260
Converting rows to columns and back again
Avatar for Chris chrissaxon
0
220
Finding the Longest Common Substring & Gestalt Pattern Matching with SQL & PL/SQL
Avatar for Chris chrissaxon
0
720
DBA Masterclass Application Tuning
Avatar for Chris chrissaxon
0
3.2k
A Preview of Oracle Database 20c PLSQL Enhancements
Avatar for Chris chrissaxon
0
290
Why Is the Optimizer Estimating the Wrong Number of Rows?
Avatar for Chris chrissaxon
0
160
Which Indexes Should I Create?
Avatar for Chris chrissaxon
0
200

Other Decks in Technology

See All in Technology
AGENTS.mdとSkillsで始めるAIエージェント活用
Avatar for そのだ sonoda_mj
3
220
MCP Appsを作ってみよう
Avatar for iwamot iwamot
PRO
4
660
LayerXにおけるセキュリティ管理の現在地と次の一手
Avatar for tosho tosho
0
210
RAG を使わないという選択肢
Avatar for tatsutaka tatsutaka
1
250
AI駆動開発を通して感じた、 AI時代のデザイナーの役割変化
Avatar for WHIsaiyo whisaiyo
3
2.2k
AIソロプレナー時代に2ヶ月で20人増員した事業創造会社の開発組織の話
Avatar for Koji Miyata miyatakoji
0
670
AIっぽい文章を採点して人間らしく直すアプリを作ってみた
Avatar for Yuuki Yamashita yama3133
2
200
2026年6月23日 Syncable Tech + Start Python Club にて
Avatar for Kimikazu Kato hamukazu
0
120
Android の公式 Skill / Android skills
Avatar for Yuki Anzai yanzm
0
150
Claude Codeをどのように キャッチアップしているか
Avatar for Oikon oikon48
13
8.2k
日本 Fintech 未来予測レポート 2027〜2028年（オリジナル版）
Avatar for 8maki 8maki
0
2.2k
白金鉱業Meetup_Vol.24_「AIエージェントは分けるほど良い」は本当か？ / Is it true that “the more you divide AI agents, the better”?
Avatar for BrainPad brainpadpr
1
390

Featured

See All Featured
A designer walks into a library…
Avatar for Paul Jervis Heath pauljervisheath
211
24k
The State of eCommerce SEO: How to Win in Today's Products SERPs - #SEOweek
Avatar for Aleyda Solis aleyda
2
11k
JavaScript: Past, Present, and Future - NDC Porto 2020
Avatar for David Neal reverentgeek
52
6k
How To Stay Up To Date on Web Technology
Avatar for Chris Coyier chriscoyier
790
250k
Claude Code のすすめ
Avatar for schroneko schroneko
67
230k
Measuring & Analyzing Core Web Vitals
Avatar for Philip Tellis bluesmoon
9
870
What does AI have to do with Human Rights?
Avatar for Per Axbom axbom
PRO
1
2.2k
Testing 201, or: Great Expectations
Avatar for Joseph Mastey jmmastey
46
8.2k
Beyond borders and beyond the search box: How to win the global "messy middle" with AI-driven SEO
Avatar for David Carrasco davidcarrasco
3
160
Helping Users Find Their Own Way: Creating Modern Search Experiences
Avatar for Dan Newman danielanewman
31
3.2k
Marketing to machines
Avatar for Jono Alderson jonoalderson
1
5.4k
Building the Perfect Custom Keyboard
Avatar for Naoto Takai takai
2
790

Transcript

  1. Your SQL Office Hours session will begin soon… How Joins

    Work Chris Saxon @ChrisRSaxon & @SQLDaily https://www.youtube.com/c/TheMagicofSQL https://blogs.oracle.com/sql Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

  2. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | Welcome to Ask TOM Office Hours! Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

  3. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon So how do joins work?

  4. Unindexed Joins Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql

    www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

  5. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | DEMO Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

  6. Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

  7. Hash Join Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql

    www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

  8. Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

    Hash ( suit, value )

  9. Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

    Hash ( suit, value )

  10. Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

    Hash ( suit, value )

  11. Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

    Hash ( suit, value )

  12. Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

    Hash ( suit, value )

  13. Merge Join Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql

    www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

  14. Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

  15. Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

  16. Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

    suit = suit and value = value

  17. Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

    suit = suit and value = value

  18. Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

    suit = suit and value = value

  19. Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

    suit = suit and value = value

  20. Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

    suit = suit and value = value

  21. Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

    value > value

  22. Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

    value > value

  23. Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

    value > value

  24. Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

    value > value

  25. Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

    value > value

  26. Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

    value > value

  27. Nested Loops Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql

    www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

  28. Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

    suit = suit and value = value

  29. Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

    suit = suit and value = value

  30. Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

    suit <> suit and value <> value

  31. Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

    suit <> suit and value <> value

  32. Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

    suit <> suit and value <> value

  33. Top-N Queries Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql

    www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

  34. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | DEMO Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

  35. (Any) Top-N Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql

    www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

  36. Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

    suit = suit fetch first 3 rows Hash ( suit )

  37. Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

    suit = suit fetch first 3 rows

  38. Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

    suit = suit fetch first 3 rows

  39. Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

    suit = suit fetch first 3 rows

  40. Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

    suit = suit fetch first 3 rows Index ( suit )

  41. Sorted Top-N Join Copyright © 2020 Oracle and/or its affiliates.

    blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

  42. Sorted Top-N Join Sort Limit 1 2 3 Copyright ©

    2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon Need to read all the rows! => Hash/merge join

  43. Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

    suit = suit order by suit fetch first 3 rows Index ( suit )

  44. Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

    suit = suit order by suit fetch first 3 rows Index ( suit ) Index ( suit )

  45. Filter Outer Table Copyright © 2020 Oracle and/or its affiliates.

    blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

  46. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | DEMO Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

  47. Adaptive Plans Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql

    www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

  48. HASH JOIN | NESTED LOOPS | NESTED LOOPS | STATISTICS

    COLLECTOR | TABLE ACCESS FULL | CARD_DECK INDEX RANGE SCAN | VALUE_SUIT_I TABLE ACCESS BY INDEX ROWID| CARD_DECK TABLE ACCESS FULL | CARD_DECK Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

  49. HASH JOIN | NESTED LOOPS | NESTED LOOPS | STATISTICS

    COLLECTOR | TABLE ACCESS FULL | CARD_DECK INDEX RANGE SCAN | VALUE_SUIT_I TABLE ACCESS BY INDEX ROWID| CARD_DECK TABLE ACCESS FULL | CARD_DECK Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon Is # rows < threshold? No Yes

  50. HASH JOIN | NESTED LOOPS | NESTED LOOPS | STATISTICS

    COLLECTOR | TABLE ACCESS FULL | CARD_DECK INDEX RANGE SCAN | VALUE_SUIT_I TABLE ACCESS BY INDEX ROWID| CARD_DECK TABLE ACCESS FULL | CARD_DECK Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon Yes; use nested loops

  51. HASH JOIN | NESTED LOOPS | NESTED LOOPS | STATISTICS

    COLLECTOR | TABLE ACCESS FULL | CARD_DECK INDEX RANGE SCAN | VALUE_SUIT_I TABLE ACCESS BY INDEX ROWID| CARD_DECK TABLE ACCESS FULL | CARD_DECK Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon No; use hash join

  52. Summary Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL

    @ChrisRSaxon

  53. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon Join type Big-O Hash Join O ( #T1 + #T2 ) Merge Join O ( #T1 log #T1 + #T2 log #T2 ) Nested Loops O ( #T1 * #T2 )

  54. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon Join type Big-O Index on join cols? Hash Join O ( #T1 + #T2 ) No impact Merge Join O ( #T1 log #T1 + #T2 log #T2 ) Avoid sort of outer table Nested Loops O ( #T1 * #T2 ) Lookup inner table

  55. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon Join type Big-O Index on join cols? Best for Hash Join O ( #T1 + #T2 ) No impact All rows Large data sets Merge Join O ( #T1 log #T1 + #T2 log #T2 ) Avoid sort of outer table Sorted data sets Nested Loops O ( #T1 * #T2 ) Lookup inner table Small fraction of rows

  56. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | asktom.oracle.com #MakeDataGreatAgain Ryan McGuire / Gratisography Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon