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

How Joins Work

Chris
January 01, 2020

How Joins Work

Chris

January 01, 2020
Tweet

More Decks by Chris

Other Decks in Technology

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. 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
  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. Sorted Top-N Join Copyright © 2020 Oracle and/or its affiliates.

    blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
  7. 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
  8. 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 )
  9. Filter Outer Table Copyright © 2020 Oracle and/or its affiliates.

    blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
  10. 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
  11. 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
  12. 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
  13. 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
  14. 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
  15. 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 )
  16. 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
  17. 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
  18. 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