Which Indexes Should I Create?

Slide 1

Slide 1 text

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

Slide 2

Slide 2 text

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.

Slide 3

Slide 3 text

Slide 4

Slide 4 text

Slide 5

Slide 5 text

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

Slide 6

Slide 6 text

How does the database search indexes?

Slide 7

Slide 7 text

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 …

Slide 8

Slide 8 text

Slide 9

Slide 9 text

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';

Slide 10

Slide 10 text

Slide 11

Slide 11 text

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

Slide 12

Slide 12 text

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

Slide 13

Slide 13 text

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

Slide 14

Slide 14 text

Slide 15

Slide 15 text

Slide 16

Slide 16 text

Slide 17

Slide 17 text

Slide 18

Slide 18 text

Slide 19

Slide 19 text

Slide 20

Slide 20 text

Slide 21

Slide 21 text

Slide 22

Slide 22 text

Slide 23

Slide 23 text

Slide 24

Slide 24 text

Slide 25

Slide 25 text

Slide 26

Slide 26 text

Slide 27

Slide 27 text

Slide 28

Slide 28 text

Slide 29

Slide 29 text

Slide 30

Slide 30 text

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 = …

Slide 31

Slide 31 text

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

Slide 32

Slide 32 text

Slide 33

Slide 33 text

Slide 34

Slide 34 text

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…

Slide 35

Slide 35 text

Slide 36

Slide 36 text

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

Slide 37

Slide 37 text

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 …

Slide 38

Slide 38 text

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…

Slide 39

Slide 39 text

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 …

Slide 40

Slide 40 text

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

Slide 41

Slide 41 text

Slide 42

Slide 42 text

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

Slide 43

Slide 43 text

Slide 44

Slide 44 text