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

Finding All the Red M&Ms: A Story of Indexes an...

Riga Dev Day
March 13, 2016
43

Finding All the Red M&Ms: A Story of Indexes and Full Table Scans by Chris Saxon

Riga Dev Day

March 13, 2016
Tweet

More Decks by Riga Dev Day

Transcript

  1. Copyright © 2015, Oracle and/or its affiliates. All rights reserved.

    | Finding all the Red M&Ms A story of indexes and full table scans Chris Saxon, Oracle Developer Advocate for SQL [email protected] @chrisrsaxon & @sqldaily blogs.oracle.com/sql www.youtube.com/c/TheMagicofSQL
  2. Copyright © 2015, Oracle and/or its affiliates. All rights reserved.

    | Safe Harbor Statement The following is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described for Oracle’s products remains at the sole discretion of Oracle.
  3. Copyright © 2015, Oracle and/or its affiliates. All rights reserved.

    | Why isn’t Oracle using my 
 #@!% 
 index?!
  4. Copyright © 2015, Oracle and/or its affiliates. All rights reserved.

    | Colou r BA G Colou r BA G Colou r BA G Colou r BA G Colou r BA G Blue 73 Brown 56 Green 11 Orang e 19 Red 2 Blue 31 Brown 40 Green 31 Orang e 19 Red 41 Blue 1 Brown 41 Green 70 Orang e 85 Red 21 Blue 64 Brown 65 Green 4 Red 42 Red 32 Blue 69 Brown 23 Green 80 Red 100 Red 12 Blue 85 Brown 21 Green 28 Red 70 Red 83 Blue 84 Brown 73 Green 44 Red 38 Yello w 29 Blue 40 Brown 97 Green 99 Red 33 Yello w 31 Blue 41 Brown 57 Green 32 Red 63 Yello w 51 Blue 88 Brown 63 Orang e 85 Red 22 Yello w 33 Blue 100 Brown 100 Orang e 21 Red 80 Yello w 4 Blue 6 Brown 80 Orang e 76 Red 80 Yello w 9
  5. Copyright © 2015, Oracle and/or its affiliates. All rights reserved.

    | M&M => a row Document => an index A bag => a block
  6. Copyright © 2015, Oracle and/or its affiliates. All rights reserved.

    | Method #1 => full table scan Method #2 => index range scan
  7. Copyright © 2015, Oracle and/or its affiliates. All rights reserved.

    | FULL TABLE SCAN Visit each bag once INDEX RANGE SCAN Visit each bag ~5 times
  8. Copyright © 2015, Oracle and/or its affiliates. All rights reserved.

    | What if there’s a Promotion? silver sweets in 1% of bags
  9. Copyright © 2015, Oracle and/or its affiliates. All rights reserved.

    | FULL TABLE SCAN Visit each bag once INDEX RANGE SCAN One bag visit
  10. Copyright © 2015, Oracle and/or its affiliates. All rights reserved.

    | Cost Rows Full Table Scan Index Range Scan
  11. Copyright © 2015, Oracle and/or its affiliates. All rights reserved.

    | The Cost-Based Optimizer estimates these calculations
  12. Copyright © 2015, Oracle and/or its affiliates. All rights reserved.

    | "M & Ms (3685102458)" by Greg Willis from Denver, CO, usa Uploaded by russavia. CC BY-S
  13. Copyright © 2015, Oracle and/or its affiliates. All rights reserved.

    | Colou r BA G Colou r BA G Colou r BA G Colou r BA G Colou r BA G Blue 1 Brown 7 Green 13 Orang e 19 Red 25 Blue 1 Brown 7 Green 13 Orang e 19 Red 25 Blue 1 Brown 7 Green 13 Orang e 19 Red 25 Blue 2 Brown 8 Green 14 Red 20 Red 26 Blue 2 Brown 8 Green 14 Red 20 Red 26 Blue 2 Brown 8 Green 14 Red 20 Red 26 Blue 3 Brown 9 Green 15 Red 21 Yello w 27 Blue 3 Brown 9 Green 15 Red 21 Yello w 27 Blue 3 Brown 9 Green 15 Red 21 Yello w 27 Blue 4 Brown 10 Orang e 16 Red 22 Yello w 28 Blue 4 Brown 10 Orang e 16 Red 22 Yello w 28 Blue 4 Brown 10 Orang e 16 Red 22 Yello w 28
  14. Copyright © 2015, Oracle and/or its affiliates. All rights reserved.

    | Colou r BA G Colou r BA G Colou r BA G Colou r BA G Colou r BA G Blue 1 Brown 3 Green 4 Orang e 7 Red 9 Blue 1 Brown 4 Green 4 Orang e 8 Red 9 Blue 2 Brown 5 Green 5 Orang e 9 Red 9 Blue 2 Brown 5 Green 6 Red 1 Red 9 Blue 3 Brown 5 Green 6 Red 2 Red 10 Blue 4 Brown 6 Green 7 Red 2 Red 10 Blue 6 Brown 7 Green 8 Red 3 Yello w 1 Blue 7 Brown 8 Green 8 Red 3 Yello w 1 Blue 7 Brown 8 Green 10 Red 4 Yello w 2 Blue 8 Brown 9 Orang e 1 Red 4 Yello w 2 Blue 9 Brown 9 Orang e 2 Red 5 Yello w 3 Blue 9 Brown 10 Orang e 3 Red 6 Yello w 5
  15. Copyright © 2015, Oracle and/or its affiliates. All rights reserved.

    | Cost Rows Full Table Scan Index Range Scan Index Range Scan Index Range Scan
  16. Copyright © 2015, Oracle and/or its affiliates. All rights reserved.

    | Rows “Toy Blocks (6831034024)” welcometolearn CC BY 2.0