Tweet
Tweet

Slide 1

Slide 1 text

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

Slide 2

Slide 2 text

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

Slide 3

Slide 3 text

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?

Slide 4

Slide 4 text

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

Slide 5

Slide 5 text

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

Slide 6

Slide 6 text

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

Slide 7

Slide 7 text

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

Slide 8

Slide 8 text

Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon Hash ( suit, value )

Slide 9

Slide 9 text

Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon Hash ( suit, value )

Slide 10

Slide 10 text

Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon Hash ( suit, value )

Slide 11

Slide 11 text

Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon Hash ( suit, value )

Slide 12

Slide 12 text

Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon Hash ( suit, value )

Slide 13

Slide 13 text

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

Slide 14

Slide 14 text

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

Slide 15

Slide 15 text

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

Slide 16

Slide 16 text

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

Slide 17

Slide 17 text

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

Slide 18

Slide 18 text

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

Slide 19

Slide 19 text

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

Slide 20

Slide 20 text

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

Slide 21

Slide 21 text

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

Slide 22

Slide 22 text

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

Slide 23

Slide 23 text

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

Slide 24

Slide 24 text

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

Slide 25

Slide 25 text

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

Slide 26

Slide 26 text

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

Slide 27

Slide 27 text

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

Slide 28

Slide 28 text

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

Slide 29

Slide 29 text

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

Slide 30

Slide 30 text

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

Slide 31

Slide 31 text

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

Slide 32

Slide 32 text

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

Slide 33

Slide 33 text

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

Slide 34

Slide 34 text

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

Slide 35

Slide 35 text

(Any) Top-N Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

Slide 36

Slide 36 text

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 )

Slide 37

Slide 37 text

Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon suit = suit fetch first 3 rows

Slide 38

Slide 38 text

Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon suit = suit fetch first 3 rows

Slide 39

Slide 39 text

Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon suit = suit fetch first 3 rows

Slide 40

Slide 40 text

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 )

Slide 41

Slide 41 text

Sorted Top-N Join Copyright © 2020 Oracle and/or its affiliates. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon

Slide 42

Slide 42 text

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

Slide 43

Slide 43 text

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 )

Slide 44

Slide 44 text

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 )

Slide 45

Slide 45 text

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

Slide 46

Slide 46 text

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

Slide 47

Slide 47 text

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

Slide 48

Slide 48 text

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

Slide 49

Slide 49 text

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

Slide 50

Slide 50 text

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

Slide 51

Slide 51 text

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

Slide 52

Slide 52 text

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

Slide 53

Slide 53 text

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 )

Slide 54

Slide 54 text

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

Slide 55

Slide 55 text

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

Slide 56

Slide 56 text

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