Upgrade to Pro
— share decks privately, control downloads, hide ads and more …
Speaker Deck
Features
Speaker Deck
PRO
Sign in
Sign up for free
Search
Search
Which Indexes Should I Create?
Search
Chris
July 01, 2020
Technology
0
130
Which Indexes Should I Create?
An overview of how to decide which indexes to create on your database tables
Chris
July 01, 2020
Tweet
Share
More Decks by Chris
See All by Chris
Create reusable SQL expressions with SQL macros
chrissaxon
0
59
All About Insert
chrissaxon
0
150
Generating days between two dates
chrissaxon
0
170
Converting rows to columns and back again
chrissaxon
0
170
Finding the Longest Common Substring & Gestalt Pattern Matching with SQL & PL/SQL
chrissaxon
0
510
DBA Masterclass Application Tuning
chrissaxon
0
3.1k
A Preview of Oracle Database 20c PLSQL Enhancements
chrissaxon
0
210
Why Is the Optimizer Estimating the Wrong Number of Rows?
chrissaxon
0
79
How to Read an Execution Plan
chrissaxon
0
120
Other Decks in Technology
See All in Technology
ブラックフライデーで購入したPixel9で、Gemini Nanoを動かしてみた
marchin1989
1
520
複雑性の高いオブジェクト編集に向き合う: プラガブルなReactフォーム設計
righttouch
PRO
0
110
マイクロサービスにおける容易なトランザクション管理に向けて
scalar
0
110
KubeCon NA 2024 Recap / Running WebAssembly (Wasm) Workloads Side-by-Side with Container Workloads
z63d
1
240
新機能VPCリソースエンドポイント機能検証から得られた考察
duelist2020jp
0
210
Wvlet: A New Flow-Style Query Language For Functional Data Modeling and Interactive Data Analysis - Trino Summit 2024
xerial
1
110
祝!Iceberg祭開幕!re:Invent 2024データレイク関連アップデート10分総ざらい
kniino
2
240
OpenAIの蒸留機能(Model Distillation)を使用して運用中のLLMのコストを削減する取り組み
pharma_x_tech
4
540
Postman と API セキュリティ / Postman and API Security
yokawasa
0
200
Snykで始めるセキュリティ担当者とSREと開発者が楽になる脆弱性対応 / Getting started with Snyk Vulnerability Response
yamaguchitk333
2
180
サイバー攻撃を想定したセキュリティガイドライン 策定とASM及びCNAPPの活用方法
syoshie
3
1.2k
小学3年生夏休みの自由研究「夏休みに Copilot で遊んでみた」
taichinakamura
0
140
Featured
See All Featured
Statistics for Hackers
jakevdp
796
220k
Faster Mobile Websites
deanohume
305
30k
[Rails World 2023 - Day 1 Closing Keynote] - The Magic of Rails
eileencodes
33
1.9k
We Have a Design System, Now What?
morganepeng
51
7.3k
10 Git Anti Patterns You Should be Aware of
lemiorhan
PRO
656
59k
Making Projects Easy
brettharned
116
5.9k
YesSQL, Process and Tooling at Scale
rocio
169
14k
A better future with KSS
kneath
238
17k
Cheating the UX When There Is Nothing More to Optimize - PixelPioneers
stephaniewalter
280
13k
Fireside Chat
paigeccino
34
3.1k
Building an army of robots
kneath
302
44k
What’s in a name? Adding method to the madness
productmarketing
PRO
22
3.2k
Transcript
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
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.
Copyright © 2020, Oracle and/or its affiliates. All rights reserved.
| Welcome to Ask TOM Office Hours!
Copyright © 2020, Oracle and/or its affiliates. All rights reserved.
| DEMO
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
How does the database search indexes?
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 …
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; …
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';
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';
blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon Leading columns in where
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
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
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
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
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
Copyright © 2020, Oracle and/or its affiliates. All rights reserved.
| Why not create them all? Storage is cheap!
Copyright © 2020, Oracle and/or its affiliates. All rights reserved.
| PROD SBY DR STAGE DEV DEV DEV DEV
Copyright © 2020, Oracle and/or its affiliates. All rights reserved.
| Storage is still cheap!
Copyright © 2020, Oracle and/or its affiliates. All rights reserved.
| PROD 10Tb
Copyright © 2020, Oracle and/or its affiliates. All rights reserved.
| PROD 10Tb 128Gb >>
Copyright © 2020, Oracle and/or its affiliates. All rights reserved.
| How can the data stay cached?
Copyright © 2020, Oracle and/or its affiliates. All rights reserved.
| TRANSACTIONS
Copyright © 2020, Oracle and/or its affiliates. All rights reserved.
| TRANSACTIONS customer_id = … status = 'OPEN' customer_id = … trans_date > …
Copyright © 2020, Oracle and/or its affiliates. All rights reserved.
| TRANSACTIONS customer_id = … status = 'OPEN' customer_id = … trans_date > …
Copyright © 2020, Oracle and/or its affiliates. All rights reserved.
| TRANSACTIONS customer_id = … status = 'OPEN' customer_id = … trans_date > …
Copyright © 2020, Oracle and/or its affiliates. All rights reserved.
| The tables & indexes fit in
Copyright © 2020, Oracle and/or its affiliates. All rights reserved.
| customer_id = … Where
Copyright © 2020, Oracle and/or its affiliates. All rights reserved.
| customer_id = … customer_id, order_datetime customer_id, order_status Where Indexes
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 = …
blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon Create few indexes
Copyright © 2020, Oracle and/or its affiliates. All rights reserved.
| customer_id = … status = …
Copyright © 2020, Oracle and/or its affiliates. All rights reserved.
|
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…
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…
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
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 …
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…
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 …
blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon Compresible columns first
Copyright © 2020, Oracle and/or its affiliates. All rights reserved.
| DEMO
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
Copyright © 2020, Oracle and/or its affiliates. All rights reserved.
| Richard Foote's Blog https://richardfoote.wordpress.com/ Further Reading
Copyright © 2020, Oracle and/or its affiliates. All rights reserved.
| asktom.oracle.com #MakeDataGreatAgain Ryan McGuire / Gratisography