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

How Joins Work

Avatar for Chris Chris
January 01, 2020
Technology
170
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
160
All About Insert
Avatar for Chris chrissaxon
0
210
Generating days between two dates
Avatar for Chris chrissaxon
0
280
Converting rows to columns and back again
Avatar for Chris chrissaxon
0
250
Finding the Longest Common Substring & Gestalt Pattern Matching with SQL & PL/SQL
Avatar for Chris chrissaxon
0
740
DBA Masterclass Application Tuning
Avatar for Chris chrissaxon
0
3.2k
A Preview of Oracle Database 20c PLSQL Enhancements
Avatar for Chris chrissaxon
0
300
Why Is the Optimizer Estimating the Wrong Number of Rows?
Avatar for Chris chrissaxon
0
170
Which Indexes Should I Create?
Avatar for Chris chrissaxon
0
210

Other Decks in Technology

See All in Technology
設計の世代交代を乗り越える、14年続くAndroidアプリの開発戦略
Avatar for SansanTech sansantech
PRO
1
150
【Oracle AI Spotlight ウェビナー】AWSか、Azureか、Google Cloudか。その議論にオラクルを含める意義。
Avatar for oracle4engineer oracle4engineer
PRO
2
240
Azure App Service / Container Apps の組み込み認証
Avatar for Kuniteru Asami kuniteru
0
210
bet_ai_day_2026_session02
Avatar for 株式会社AgenticSec agenticsec
2
1.3k
Tab5をRubyで動くパソコンにする
Avatar for Katsuhiko Kageyama kishima
2
250
AI時代のAPI品質を支えるガードレール / API Guardrails for API quality in the AI ​​era
Avatar for Yoichi Kawasaki yokawasa
1
160
GuardDuty 検知対応を DevOps Agent で効率化しようとしている話 / GuardDuty Investigations with DevOps Agent
Avatar for MasahiroKawahara masahirokawahara
1
330
AI 駆動 Terraform 開発/SRE_BizReach_MIXI_2
Avatar for Visional Engineering ＆ Design visional_engineering_and_design
2
1.9k
GoにおけるFFIのこれまでとこれから
Avatar for Masaaki Goshima goccy
1
540
Sigmaユーザーのための有用リソース一挙公開 & Sigmaで使えるMCP #sigma_ucj /useful-resources-for-sigma-computing-users-and-mcps-with-sigma
Avatar for shinyaa31 shinyaa31
0
150
なぜSRE・セキュリティは評価されないのか？守りの組織を事業成長エンジンに変えた実践
Avatar for サイバーセキュリティクラウド cscengineer
PRO
3
2.3k
「重なり」は迎える側がつくる ― 人もAIエージェントも歓迎するプロダクトエンジニアリング ―
Avatar for Go Akazawa go0517go
PRO
0
260

Featured

See All Featured
Getting science done with accelerated Python computing platforms
Avatar for Jacob Tomlinson jacobtomlinson
2
470
Building the Perfect Custom Keyboard
Avatar for Naoto Takai takai
2
860
Making Projects Easy
Avatar for Brett Harned brettharned
120
6.7k
Max Prin - Stacking Signals: How International SEO Comes Together (And Falls Apart)
Avatar for Tech SEO Connect techseoconnect
PRO
0
450
XXLCSS - How to scale CSS and keep your sanity
Avatar for Zaharenia Atzitzikaki sugarenia
249
1.3M
Odyssey Design
Avatar for Ryan Kendrick rkendrick25
PRO
2
800
Digital Ethics as a Driver of Design Innovation
Avatar for Per Axbom axbom
PRO
1
410
Embracing the Ebb and Flow
Avatar for Simon Collison colly
88
5.2k
16th Malabo Montpellier Forum Presentation
Avatar for AKADEMIYA2063 akademiya2063
PRO
0
370
The B2B funnel & how to create a winning content strategy
Avatar for Katarina Dahlin katarinadahlin
PRO
1
510
Documentation Writing (for coders)
Avatar for Carmen Chung carmenintech
77
5.5k
Understanding Cognitive Biases in Performance Measurement
Avatar for Philip Tellis bluesmoon
32
3k

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