Upgrade to Pro — share decks privately, control downloads, hide ads and more …
Sign up for free

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
220
Generating days between two dates
Avatar for Chris chrissaxon
0
290
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
750
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
180
Which Indexes Should I Create?
Avatar for Chris chrissaxon
0
220

Other Decks in Technology

See All in Technology
Datadog の学び方 - あるいは、オブザーバビリティを学ぶとは何か
Avatar for iwa mananyuki
1
540
自分で立ててみるLLMサービス
Avatar for 世良泰明 y_sera15
0
160
AI 時代の Azure エンジニアリング ～ 私たちは何を磨き、何を任せるのか ～
Avatar for Akira Inoue chack411
1
590
AIがコードを書く時代に、エンジニアは何で自分の価値を証明するのか / In an era where AI writes code how do engineers prove their value
Avatar for CARTA Engineering carta_engineering
2
830
12 個 Tool と、 4,096 Token
Avatar for ラワンアウンピョウ hlum
0
130
SDDの運用にめげずに向き合った話
Avatar for SansanTech sansantech
PRO
1
140
パッチ更新でterraform initが落ちた日
Avatar for gr1m0h grimoh
1
120
AgentCoreで実践するハーネスエンジニアリング
Avatar for やくも yakumo
1
300
FactoryBotアンチパターン / factory_bot anti-patterns
Avatar for toshimaru toshimaru
0
150
WasmをRustにする
Avatar for SansanTech sansantech
PRO
0
160
Claude Code本は、どのように執筆チームで書かれたか
Avatar for Oikon oikon48
1
360
TinyGoでオブザーバビリティ？OpenTelemetryはTinyGoで使えるのか / Is OpenTelemetry available in TinyGo environment?
Avatar for ymotongpoo ymotongpoo
2
130

Featured

See All Featured
Test your architecture with Archunit
Avatar for Yoan thirion
2
2.4k
Kristin Tynski - Automating Marketing Tasks With AI
Avatar for Tech SEO Connect techseoconnect
PRO
0
540
AI Search:
Where Are We & What Can We Do About It?
Avatar for Aleyda Solis aleyda
0
8.1k
Prompt Engineering for Job Search
Avatar for Mfonobong Umondia mfonobong
0
500
XXLCSS - How to scale CSS and keep your sanity
Avatar for Zaharenia Atzitzikaki sugarenia
250
1.3M
Into the Great Unknown - MozCon
Avatar for Noah Learner thekraken
41
2.8k
A Soul's Torment
Avatar for Nat Ma seathinner
8
3.7k
How People are Using Generative and Agentic AI to Supercharge Their Products, Projects, Services and Value Streams Today
Avatar for Helen Beal helenjbeal
2
360
エンジニアに許された特別な時間の終わり
Avatar for watany watany
109
250k
Responsive Adventures: Dirty Tricks From The Dark Corners of Front-End
Avatar for Vitaly Friedman smashingmag
254
22k
Chrome DevTools: State of the Union 2024 - Debugging React & Beyond
Avatar for Addy Osmani addyosmani
10
1.4k
Utilizing Notion as your number one productivity tool
Avatar for Mfonobong Umondia mfonobong
4
620

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