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

Which Indexes Should I Create?

D7b6e701f0155fc189bbca6c89223b3c?s=47 Chris
July 01, 2020
Technology
0
63

Which Indexes Should I Create?

An overview of how to decide which indexes to create on your database tables

D7b6e701f0155fc189bbca6c89223b3c?s=128

Chris

July 01, 2020
Tweet

More Decks by Chris

See All by Chris
D7b6e701f0155fc189bbca6c89223b3c?s=48 chrissaxon
0
53
D7b6e701f0155fc189bbca6c89223b3c?s=48 chrissaxon
0
27
D7b6e701f0155fc189bbca6c89223b3c?s=48 chrissaxon
0
83
D7b6e701f0155fc189bbca6c89223b3c?s=48 chrissaxon
0
88
D7b6e701f0155fc189bbca6c89223b3c?s=48 chrissaxon
0
2.9k
D7b6e701f0155fc189bbca6c89223b3c?s=48 chrissaxon
0
85
D7b6e701f0155fc189bbca6c89223b3c?s=48 chrissaxon
0
19
D7b6e701f0155fc189bbca6c89223b3c?s=48 chrissaxon
0
37
D7b6e701f0155fc189bbca6c89223b3c?s=48 chrissaxon
0
47k

Other Decks in Technology

See All in Technology
71c9ebde850996d2533c5df4df2c93c6?s=48 opdavies
0
470
5b47136bedcba2799edf4fcd27ea66d7?s=48 yaegashi
3
610
6249f569a29ccb5fd9c1b1b3e572ee89?s=48 omuomugin
1
290
44b799f91cc48e38c5fe9f5d9c5ecc61?s=48 takaichi00
10
2.2k
C4221ba79492601eb59a9ef5d4f4bbaa?s=48 nakamuuu
3
160
Be2a68a5ffde6683d2a5bd213ce8b814?s=48 aviciida
1
100
01c581e230dd588a5c9b7a8c243c4f43?s=48 u1nakayama
0
340
B1bddcb899ec3060fe9913f3cb70dbb6?s=48 lmt_swallow
4
1.1k
79dcb04101764d7bf66f898dd446838e?s=48 tsukubachallenge
0
250
966e29b84ae7dbde170f66b0bc75b7a6?s=48 ishiayaya
0
180
E0db06b2e246ef948efc84bfc3909117?s=48 stkzk3110
0
440
7cd783377515bdf8207062840b7b2f4e?s=48 soracom
PRO
0
240

Featured

See All Featured
465724d73fe3a92c0879fdfb43a3a6f3?s=48 philhawksworth
195
9k
0c6fd6a08d0f898159cda7cafcacf07f?s=48 afnizarnur
180
14k
1f74b13f1e5c6c69cb5d7fbaabb1e2cb?s=48 sferik
612
56k
32de0bd2ba869609d26fd052a4622778?s=48 cromwellryan
107
6.5k
E190023b66e2b8aa73a842b106920c93?s=48 zenorocha
302
40k
F29327647a9cff5c69618bae420792ea?s=48 tenderlove
58
3.8k
25c7c18223fb42a4c6ae1c8db6f50f9b?s=48 mojombo
364
63k
9375a9529679f1b42b567a640d775e7d?s=48 schacon
152
6.8k
E43f8dfd01057f8304f5f8fb9a294d7d?s=48 jeffersonlam
333
16k
3ab1249be442027903e1180025340b3f?s=48 reverentgeek
171
7.6k
A9704266587836f7e784235e5073b93e?s=48 jmmastey
20
5k
24fc194843a71f10949be18d5a692682?s=48 gr2m
86
11k

Transcript

  1. Your SQL Office Hours begins soon… Which Indexes Should I

    Create? Chris Saxon @ChrisRSaxon & @SQLDaily https://www.youtube.com/c/TheMagicofSQL https://blogs.oracle.com/sql

  2. 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, timing, and pricing of any features or functionality described for Oracle’s products may change and remains at the sole discretion of Oracle Corporation. Statements in this presentation relating to Oracle’s future plans, expectations, beliefs, intentions and prospects are “forward-looking statements” and are subject to material risks and uncertainties. A detailed discussion of these factors and other risks that affect our business is contained in Oracle’s Securities and Exchange Commission (SEC) filings, including our most recent reports on Form 10-K and Form 10-Q under the heading “Risk Factors.” These filings are available on the SEC’s website or on Oracle’s website at http://www.oracle.com/investor. All information in this presentation is current as of September 2020 and Oracle undertakes no duty to update any statement in light of new information or future events. Safe Harbor Copyright © 2020 Oracle and/or its affiliates.

  3. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | Welcome to Ask TOM Office Hours!

  4. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | DEMO

  5. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | Index Columns i1 customer_id i2 customer_id, order_datetime i3 order_datetime i4 order_status i5 store_id, order_status i6 order_status, customer_id

  6. How does the database search indexes?

  7. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | 101, rowid 102, rowid 102, rowid … 110, rowid 1 .. 100 101 .. 200 … 901 .. 1000 create index brick_weight_i on bricks ( weight ); 901 .. 910 911 .. 920 … 991 .. 1000 101 .. 110 111 .. 120 … 191 .. 200 1 .. 10 11 .. 20 … 91 .. 100 991, rowid 993, rowid 994, rowid … 1000, rowid 11, rowid 12, rowid 12, rowid … 20, rowid 1, rowid 2, rowid 4, rowid … 10, rowid 901, rowid 903, rowid 904, rowid … 910, rowid … … 91, rowid 92, rowid 95, rowid … 100, rowid …

  8. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | 101, rowid 102, rowid 102, rowid … 110, rowid 1 .. 100 101 .. 200 … 901 .. 1000 create index brick_weight_i on bricks ( weight ); 901 .. 910 911 .. 920 … 991 .. 1000 101 .. 110 111 .. 120 … 191 .. 200 1 .. 10 11 .. 20 … 91 .. 100 991, rowid 993, rowid 994, rowid … 1000, rowid 11, rowid 12, rowid 12, rowid … 20, rowid 1, rowid 2, rowid 4, rowid … 10, rowid 901, rowid 903, rowid 904, rowid … 910, rowid … … 91, rowid 92, rowid 95, rowid … 100, rowid weight between 90 and 110; …

  9. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | blue green red yellow red, cube red, cube red, cuboid red, star yellow, cube yellow, cuboid yellow, cuboid yellow, star create index brick_colour_shape_i on bricks ( colour, shape ); green, cube green, cube green, cuboid green, star blue, cube blue, cube blue, cuboid blue, cuboid colour = 'red';

  10. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | blue green red yellow red, cube red, cube red, cuboid red, star yellow, cube yellow, cuboid yellow, cuboid yellow, star create index brick_colour_shape_i on bricks ( colour, shape ); green, cube green, cube green, cuboid green, star blue, cube blue, cube blue, cuboid blue, cuboid shape = 'cube';

  11. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon Leading columns in where

  12. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | Where clause Index columns customer_id = … customer_id customer_id = … order_datetime > … customer_id, order_datetime order_status = … store_id = … order_status, store_id order_status = … customer_id = … order_status, customer_id

  13. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | Where clause Index columns customer_id = … customer_id customer_id = … order_datetime = … customer_id, order_datetime order_status = … store_id = … order_status, store_id order_status = … customer_id = … order_status, customer_id

  14. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | Where clause Index columns customer_id = … customer_id customer_id = … order_datetime = … customer_id, order_datetime order_status = … store_id = … order_status, store_id order_status = … customer_id = … order_status, customer_id

  15. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | Where clause Index columns customer_id = … customer_id customer_id = … order_datetime = … customer_id, order_datetime order_status = … store_id = … order_status, store_id order_status = … customer_id = … order_status, customer_id

  16. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | Where clause Index columns customer_id = … customer_id customer_id = … order_datetime = … customer_id, order_datetime order_status = … store_id = … order_status, store_id order_status = … customer_id = … order_status, customer_id

  17. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | Why not create them all? Storage is cheap!

  18. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | PROD SBY DR STAGE DEV DEV DEV DEV

  19. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | Storage is still cheap!

  20. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | PROD 10Tb

  21. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | PROD 10Tb 128Gb >>

  22. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | How can the data stay cached?

  23. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | TRANSACTIONS

  24. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | TRANSACTIONS customer_id = … status = 'OPEN' customer_id = … trans_date > …

  25. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | TRANSACTIONS customer_id = … status = 'OPEN' customer_id = … trans_date > …

  26. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | TRANSACTIONS customer_id = … status = 'OPEN' customer_id = … trans_date > …

  27. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | The tables & indexes fit in

  28. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | customer_id = … Where

  29. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | customer_id = … customer_id, order_datetime customer_id, order_status Where Indexes

  30. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | customer_id = … customer_id, order_datetime store_id, order_status customer_id, order_status Where Indexes store_id = … order_datetime > … order_status = …

  31. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon Create few indexes

  32. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | customer_id = … status = …

  33. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    |

  34. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | ( status, customer_id ) CANCELLED, 1 CANCELLED, 2 CANCELLED, 3 CANCELLED, 4 … COMPLETE, 1 COMPLETE, 2 COMPLETE, 3 COMPLETE, 4 … OPEN, 1 OPEN, 2 OPEN, 3 OPEN, 4 … ( status, customer_id ) 0:CANCELLED 0, 1 0, 2 0, 3 0, 4… 1:COMPLETE, 1, 1 1, 2 1, 3 1, 4… 2: OPEN 2, 1 2, 2 2, 3 2, 4…

  35. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | ( status, customer_id ) CANCELLED, 1 CANCELLED, 2 CANCELLED, 3 CANCELLED, 4 … COMPLETE, 1 COMPLETE, 2 COMPLETE, 3 COMPLETE, 4 … OPEN, 1 OPEN, 2 OPEN, 3 OPEN, 4 … ( status, customer_id ) 0:CANCELLED 0, 1 0, 2 0, 3 0, 4… 1:COMPLETE, 1, 1 1, 2 1, 3 1, 4… 2: OPEN 2, 1 2, 2 2, 3 2, 4…

  36. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | ( status, customer_id ) CANCELLED, 1 CANCELLED, 2 CANCELLED, 3 CANCELLED, 4 … COMPLETE, 1 COMPLETE, 2 COMPLETE, 3 COMPLETE, 4 … OPEN, 1 OPEN, 2 OPEN, 3 OPEN, 4 … ( status, customer_id ) 0:CANCELLED 0, 1 0, 2 0, 3 0, 4… 1:COMPLETE 1, 1 1, 2 1, 3 1, 4… 2: OPEN 2, 1 2, 2 2, 3 2, 4… compress 1

  37. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | ( customer_id, status ) 0: 1 0, CANCELLED 0, COMPLETE 0, OPEN … 1: 2 1, CANCELLED 1, COMPLETE 1, OPEN … 2: 3 2, CANCELLED 2, COMPLETE 2, OPEN …

  38. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | ( status, customer_id ) 0:CANCELLED 0, 2141fd1c-8292-4726-b3fe-a980a2c8a430 0, 57f802ea-cff1-4d2d-b78b-ef964f5b2dd5 0, e79d5a65-5552-4eca-a945-6ad2dbb9359b 0, 5dbdc286-2f22-4ef1-a497-3c70d334c94b… 1:COMPLETE 1, 2141fd1c-8292-4726-b3fe-a980a2c8a430 1, 57f802ea-cff1-4d2d-b78b-ef964f5b2dd5 1, e79d5a65-5552-4eca-a945-6ad2dbb9359b 1, 5dbdc286-2f22-4ef1-a497-3c70d334c94b… 2: OPEN 2, 2141fd1c-8292-4726-b3fe-a980a2c8a430 2, 57f802ea-cff1-4d2d-b78b-ef964f5b2dd5 2, e79d5a65-5552-4eca-a945-6ad2dbb9359b 2, 5dbdc286-2f22-4ef1-a497-3c70d334c94b…

  39. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | ( customer_id, status ) 0: 2141fd1c-8292-4726-b3fe-a980a2c8a430 0, CANCELLED 0, COMPLETE 0, OPEN … 1: 57f802ea-cff1-4d2d-b78b-ef964f5b2dd5 1, CANCELLED 1, COMPLETE 1, OPEN … 2: e79d5a65-5552-4eca-a945-6ad2dbb9359b 2, CANCELLED 2, COMPLETE 2, OPEN …

  40. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon Compresible columns first

  41. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | DEMO

  42. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | • Keep # indexes small • ( = col, > col, select_order_by_cols ) • Compresiblecols 1st • Automatic Indexing solve 1 & 2 for you! Summary

  43. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | Richard Foote's Blog https://richardfoote.wordpress.com/ Further Reading

  44. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | asktom.oracle.com #MakeDataGreatAgain Ryan McGuire / Gratisography