Trees Indexes (Part II) Lecture # 08 Database Systems Andy Pavlo - PowerPoint PPT Presentation

Trees Indexes (Part II) Lecture # 08 Database Systems Andy Pavlo AP AP Computer Science 15-445/15-645 Carnegie Mellon Univ. Fall 2018

2 ADM IN ISTRIVIA Project #1 is due Wednesday Sept 26 th @ 11:59pm Homework #2 is due Friday Sept 28 th @ 11:59pm Project #2 will be released on Wednesday Sept 26 th . First checkpoint is due Monday Oct 8 th . CMU 15-445/645 (Fall 2018)

3 TO DAY'S AGEN DA Additional Index Usage Skip Lists Radix Trees Inverted Indexes CMU 15-445/645 (Fall 2018)

4 IM PLICIT IN DEXES Most DBMSs automatically create an index to enforce integrity constraints. → Primary Keys → Unique Constraints → Foreign Keys (?) CREATE TABLE foo ( CREATE UNIQUE INDEX foo_pkey ON foo (id); id SERIAL PRIMARY KEY , val1 INT NOT NULL , val2 VARCHAR (32) UNIQUE CREATE UNIQUE INDEX foo_val2_key ); ON foo (val2); CMU 15-445/645 (Fall 2018)

4 IM PLICIT IN DEXES Most DBMSs automatically create an index to enforce integrity constraints. → Primary Keys → Unique Constraints CREATE INDEX foo_val1_key → Foreign Keys (?) ON foo (val1); CREATE TABLE foo ( CREATE TABLE bar ( id SERIAL PRIMARY KEY , id INT REFERENCES foo (val1), val1 INT NOT NULL , val VARCHAR (32) val2 VARCHAR (32) UNIQUE ); ); CMU 15-445/645 (Fall 2018)

4 IM PLICIT IN DEXES Most DBMSs automatically create an index to enforce integrity constraints. → Primary Keys → Unique Constraints CREATE INDEX foo_val1_key → Foreign Keys (?) ON foo (val1); X CREATE TABLE foo ( CREATE TABLE bar ( id SERIAL PRIMARY KEY , id INT REFERENCES foo (val1), val1 INT NOT NULL , val VARCHAR (32) val2 VARCHAR (32) UNIQUE ); ); CMU 15-445/645 (Fall 2018)

4 IM PLICIT IN DEXES Most DBMSs automatically create an index to enforce integrity constraints. → Primary Keys → Unique Constraints → Foreign Keys (?) CREATE TABLE foo ( CREATE TABLE bar ( id SERIAL PRIMARY KEY , id INT REFERENCES foo (val1), val1 INT NOT NULL UNIQUE , val VARCHAR (32) val2 VARCHAR (32) UNIQUE ); ); CMU 15-445/645 (Fall 2018)

5 PARTIAL IN DEXES Create an index on a subset of the CREATE INDEX idx_foo entire table. This potentially reduces ON foo (a, b) WHERE c = 'WuTang'; its size and the amount of overhead to maintain it. One common use case is to partition indexes by date ranges. → Create a separate index per month, year. CMU 15-445/645 (Fall 2018)

5 PARTIAL IN DEXES Create an index on a subset of the CREATE INDEX idx_foo entire table. This potentially reduces ON foo (a, b) WHERE c = 'WuTang'; its size and the amount of overhead to maintain it. SELECT b FROM foo WHERE a = 123 One common use case is to partition AND c = 'WuTang'; indexes by date ranges. → Create a separate index per month, year. CMU 15-445/645 (Fall 2018)

6 COVERIN G IN DEXES If all of the fields needed to process CREATE INDEX idx_foo the query are available in an index, ON foo (a, b); then the DBMS does not need to SELECT b FROM foo retrieve the tuple. WHERE a = 123; This reduces contention on the DBMS's buffer pool resources. CMU 15-445/645 (Fall 2018)

7 IN DEX IN CLUDE CO LUM N S Embed additional columns in indexes CREATE INDEX idx_foo to support index-only queries. ON foo (a, b) INCLUDE (c); Not part of the search key. CMU 15-445/645 (Fall 2018)

7 IN DEX IN CLUDE CO LUM N S Embed additional columns in indexes CREATE INDEX idx_foo to support index-only queries. ON foo (a, b) INCLUDE (c); Not part of the search key. SELECT b FROM foo WHERE a = 123 AND c = 'WuTang'; CMU 15-445/645 (Fall 2018)

7 IN DEX IN CLUDE CO LUM N S Embed additional columns in indexes CREATE INDEX idx_foo to support index-only queries. ON foo (a, b) INCLUDE (c); Not part of the search key. SELECT b FROM foo WHERE a = 123 AND c = 'WuTang'; CMU 15-445/645 (Fall 2018)

8 FUN CTIO N AL/ EXPRESSIO N IN DEXES The index does not need to store keys SELECT * FROM users in the same way that they appear in WHERE EXTRACT (dow ⮱ FROM login) = 2; their base table. CMU 15-445/645 (Fall 2018)

8 FUN CTIO N AL/ EXPRESSIO N IN DEXES The index does not need to store keys SELECT * FROM users in the same way that they appear in WHERE EXTRACT (dow ⮱ FROM login) = 2; their base table. CREATE INDEX idx_user_login ON users (login); CMU 15-445/645 (Fall 2018)

8 FUN CTIO N AL/ EXPRESSIO N IN DEXES The index does not need to store keys SELECT * FROM users in the same way that they appear in WHERE EXTRACT (dow ⮱ FROM login) = 2; their base table. X CREATE INDEX idx_user_login You can use expressions when ON users (login); declaring an index. CMU 15-445/645 (Fall 2018)

8 FUN CTIO N AL/ EXPRESSIO N IN DEXES The index does not need to store keys SELECT * FROM users in the same way that they appear in WHERE EXTRACT (dow ⮱ FROM login) = 2; their base table. X CREATE INDEX idx_user_login You can use expressions when ON users (login); declaring an index. CREATE INDEX idx_user_login ON users ( EXTRACT (dow FROM login)); CMU 15-445/645 (Fall 2018)

8 FUN CTIO N AL/ EXPRESSIO N IN DEXES The index does not need to store keys SELECT * FROM users in the same way that they appear in WHERE EXTRACT (dow ⮱ FROM login) = 2; their base table. X CREATE INDEX idx_user_login You can use expressions when ON users (login); declaring an index. CREATE INDEX idx_user_login ON users ( EXTRACT (dow FROM login)); CMU 15-445/645 (Fall 2018)

8 FUN CTIO N AL/ EXPRESSIO N IN DEXES The index does not need to store keys SELECT * FROM users in the same way that they appear in WHERE EXTRACT (dow ⮱ FROM login) = 2; their base table. X CREATE INDEX idx_user_login You can use expressions when ON users (login); declaring an index. CREATE INDEX idx_user_login ON users ( EXTRACT (dow FROM login)); CREATE INDEX idx_user_login ON foo (login) WHERE EXTRACT (dow FROM login) = 2; CMU 15-445/645 (Fall 2018)

9 O BSERVATIO N The easiest way to implement a dynamic order- preserving index is to use a sorted linked list. All operations have to linear search. → Average Cost: O(N) CMU 15-445/645 (Fall 2018)

9 O BSERVATIO N The easiest way to implement a dynamic order- preserving index is to use a sorted linked list. All operations have to linear search. → Average Cost: O(N) K1 K2 K3 K4 K5 K6 K7 CMU 15-445/645 (Fall 2018)

9 O BSERVATIO N The easiest way to implement a dynamic order- preserving index is to use a sorted linked list. All operations have to linear search. → Average Cost: O(N) K1 K2 K3 K4 K5 K6 K7 CMU 15-445/645 (Fall 2018)

10 SKIP LISTS Multiple levels of linked lists with extra pointers that skip over intermediate nodes. Maintains keys in sorted order without requiring global rebalancing. CMU 15-445/645 (Fall 2018)

11 SKIP LISTS A collection of lists at different levels → Lowest level is a sorted, singly linked list of all keys → 2nd level links every other key → 3rd level links every fourth key → In general, a level has half the keys of one below it To insert a new key, flip a coin to decide how many levels to add the new key into. Provides approximate O(log n) search times. CMU 15-445/645 (Fall 2018)

12 SKIP LISTS: EXAM PLE Levels End ∞ P=N/4 ∞ K2 K4 P=N/2 ∞ K1 K2 K3 K4 K6 P=N V1 V2 V3 V4 V6 CMU 15-445/645 (Fall 2018)

12 SKIP LISTS: EXAM PLE Levels End ∞ P=N/4 ∞ K2 K4 P=N/2 ∞ K1 K2 K3 K4 K6 P=N V1 V2 V3 V4 V6 CMU 15-445/645 (Fall 2018)

12 SKIP LISTS: EXAM PLE Levels End ∞ P=N/4 ∞ K2 K4 P=N/2 ∞ K1 K2 K3 K4 K6 P=N V1 V2 V3 V4 V6 CMU 15-445/645 (Fall 2018)

12 SKIP LISTS: EXAM PLE Levels End ∞ P=N/4 ∞ K2 K4 P=N/2 ∞ K1 K2 K3 K4 K6 P=N V1 V2 V3 V4 V6 CMU 15-445/645 (Fall 2018)

12 SKIP LISTS: EXAM PLE Levels End ∞ P=N/4 ∞ K2 K4 P=N/2 ∞ K1 K2 K3 K4 K6 P=N V1 V2 V3 V4 V6 CMU 15-445/645 (Fall 2018)

12 SKIP LISTS: EXAM PLE Levels End ∞ P=N/4 ∞ K2 K4 P=N/2 ∞ K1 K2 K3 K4 K6 P=N V1 V2 V3 V4 V6 CMU 15-445/645 (Fall 2018)

13 SKIP LISTS: IN SERT Insert K5 Levels End ∞ P=N/4 ∞ K2 K4 P=N/2 ∞ K1 K2 K3 K4 K6 P=N V1 V2 V3 V4 V6 CMU 15-445/645 (Fall 2018)

13 SKIP LISTS: IN SERT Insert K5 Levels End ∞ K5 P=N/4 ∞ K2 K4 K5 P=N/2 ∞ K1 K2 K3 K4 K5 K6 P=N V1 V2 V3 V4 V5 V6 CMU 15-445/645 (Fall 2018)

13 SKIP LISTS: IN SERT Insert K5 Levels End ∞ K5 P=N/4 ∞ K2 K4 K5 P=N/2 ∞ K1 K2 K3 K4 K5 K6 P=N V1 V2 V3 V4 V5 V6 CMU 15-445/645 (Fall 2018)

13 SKIP LISTS: IN SERT Insert K5 Levels End ∞ K5 P=N/4 ∞ K2 K4 K5 P=N/2 ∞ K1 K2 K3 K4 K5 K6 P=N V1 V2 V3 V4 V5 V6 CMU 15-445/645 (Fall 2018)

13 SKIP LISTS: IN SERT Insert K5 Levels End ∞ K5 P=N/4 ∞ K2 K4 K5 P=N/2 ∞ K1 K2 K3 K4 K5 K6 P=N V1 V2 V3 V4 V5 V6 CMU 15-445/645 (Fall 2018)

13 SKIP LISTS: IN SERT Insert K5 Levels End ∞ K5 P=N/4 ∞ K2 K4 K5 P=N/2 ∞ K1 K2 K3 K4 K5 K6 P=N V1 V2 V3 V4 V5 V6 CMU 15-445/645 (Fall 2018)

14 SKIP LISTS: SEARCH Find K3 Levels End ∞ K5 P=N/4 ∞ K2 K4 K5 P=N/2 ∞ K1 K2 K3 K4 K5 K6 P=N V1 V2 V3 V4 V5 V6 CMU 15-445/645 (Fall 2018)

14 SKIP LISTS: SEARCH Find K3 Levels End K3<K5 ∞ K5 P=N/4 ∞ K2 K4 K5 P=N/2 ∞ K1 K2 K3 K4 K5 K6 P=N V1 V2 V3 V4 V5 V6 CMU 15-445/645 (Fall 2018)