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

How Joins Work

Sponsored · SiteGround - Reliable hosting with speed, security, and support you can count on. →
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
150
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
230
Finding the Longest Common Substring & Gestalt Pattern Matching with SQL & PL/SQL
Avatar for Chris chrissaxon
0
730
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
プロダクトだけじゃない、社内プロセスにおける自動化・省力化ノススメ
Avatar for KAKEHASHI kakehashi
PRO
1
3.7k
Keeping applications secure by evolving OAuth 2.0 and OpenID Connect
Avatar for Alexander Schwartz ahus1
PRO
1
160
環境凍結という Toil を倒す -セルフサービス型 Ephemeral テスト環境の 設計と実践
Avatar for Yuji Shirai shirouz
1
2.3k
Amazon EVS で VCF 9.0 / 9.1 のサポート開始まとめ
Avatar for mtoyoda mtoyoda
0
290
関数型の考えを TypeScript に持ち込んで、テストしやすい純粋関数を増やす / Pure at the Core, Effects at the Edge: Bringing Functional Thinking into TypeScript
Avatar for 株式会社カミナシ kaminashi
1
110
人を動かすのは時間ではなく、納得感 〜新任EMが入社3ヶ月、組織を2回変えた話〜
Avatar for KAKEHASHI kakehashi
PRO
3
220
DatabricksにおけるMCPソリューション
Avatar for Takaaki Yayoi taka_aki
1
250
穢れた技術選定について
Avatar for watany watany
7
550
なぜ私たちのSREプラクティスはなかなか機能しないのか 〜システムより先に組織を見る〜 / Why our SRE practices aren't really working
Avatar for VTRyo vtryo
3
3.7k
Empower GenAI with Agile - あなたのアジャイルが生成AIのバフになる仕組み
Avatar for Hiroyuki Ito (TheHiro) hageyahhoo
1
180
ゴールデンパスは敷いただけでは道にならない ─ 企画部門のエンジニアが技術標準を事業価値に変えるまで
Avatar for みずほリサーチ&テクノロジーズ株式会社 先端技術研究部 mhrtech
0
140
依頼文化をやめる日 EM視点で語るPlatform EngineeringとInclusive SRE / Discussing Platform Engineering and Inclusive SRE from an EM's Perspective
Avatar for katsukamaru shin1988
4
5.4k

Featured

See All Featured
Visualizing Your Data: Incorporating Mongo into Loggly Infrastructure
Avatar for mongodb mongodb
49
10k
"I'm Feeling Lucky" - Building Great Search Experiences for Today's Users (#IAC19)
Avatar for Dan Newman danielanewman
230
23k
Prompt Engineering for Job Search
Avatar for Mfonobong Umondia mfonobong
0
370
[Rails World 2023 - Day 1 Closing Keynote] - The Magic of Rails
Avatar for Eileen M. Uchitelle eileencodes
38
2.9k
The Curse of the Amulet
Avatar for Matthew Lei leimatthew05
2
13k
Scaling GitHub
Avatar for Zach Holman holman
464
140k
Abbi's Birthday
Avatar for Violet Bock coloredviolet
3
8.6k
Chrome DevTools: State of the Union 2024 - Debugging React & Beyond
Avatar for Addy Osmani addyosmani
10
1.3k
Build your cross-platform service in a week with App Engine
Avatar for Jose L jlugia
234
18k
The Web Performance Landscape in 2024 [PerfNow 2024]
Avatar for Tammy Everts tammyeverts
12
1.2k
Effective software design: The role of men in debugging patriarchy in IT @ Voxxed Days AMS
Avatar for Kenny Baas-Schwegler baasie
0
450
The AI Search Optimization Roadmap by Aleyda Solis
Avatar for Aleyda Solis aleyda
1
6k

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