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

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 chris.saxon@oracle.com @chrisrsaxon & @sqldaily blogs.oracle.com/sql www.youtube.com/c/TheMagicofSQL
2. Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
3. 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.
4. Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Why isn’t Oracle using my 
 #@!% 
 index?!
5. Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
6. Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Image: Wikipedia
7. 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
8. Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | What’s this got to do with
 
 

9. Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | M&M => a row Document => an index A bag => a block
10. Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Method #1 => full table scan Method #2 => index range scan
11. Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Image: Pixabay
12. 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
13. Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | What if there’s a Promotion? silver sweets in 1% of bags
14. Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | FULL TABLE SCAN Visit each bag once INDEX RANGE SCAN One bag visit
15. Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Cost Rows Full Table Scan Index Range Scan
16. Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | The Cost-Based Optimizer estimates these calculations
17. 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
18. 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
19. 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
20. Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Clustering factor
21. Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | DEMO
22. Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
23. Copyright © 2015, Oracle and/or its affiliates. All rights reserved. |
24. Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | How do I Improve the clustering factor?
25. Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Tannin Heap Simon A. Eugster CC-BY-3.0
26. Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Organized Table
27. Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Primary
28. Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Colour Weight
29. Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Export Import Prod Test
30. Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Cost Rows Full Table Scan Index Range Scan Index Range Scan Index Range Scan
31. Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Rows “Toy Blocks (6831034024)” welcometolearn CC BY 2.0
32. Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Questions?