Advanced Database Systems: Filter Query Solutions & Cost Analysis, Slides of Database Management Systems (DBMS)

Download Advanced Database Systems: Filter Query Solutions & Cost Analysis and more Slides Database Management Systems (DBMS) in PDF only on Docsity!

Advanced Database Systems

Notes 04: Operators for Data Access

Problem

• Relation: Employee (ID, Name, Dept, …)
• 10 M tuples
• (Filter) Query:

SELECT *

FROM Employee WHERE Name = “Bob”

Solution

• Storage:
  • Employee relation sorted on Name attribute
• Query plan:
  • Binary search

Solution

• Cost:
  • Size of a block: 1024 bytes
  • Number of records per block: 1024 / 100 = 10
  • Total number of blocks: 10 M / 10 = 1 M
  • Blocks accessed by binary search: 20
  • Total time: 20 ms x 20 = 400 ms

Indexes

• Data structures that efficiently evaluate a class

of filter predicates over a relation

• Class of filter predicates:
  • Single or multi-attributes (index-key attributes)
  • Range and/or equality predicates
• (Usually) independent of physical storage of

relation:

• Multiple indexes per relation

Indexes

• Disk resident
  • Large to fit in memory
  • Persistent
• Updated when indexed relation updated
  • Relation updates costlier
  • Query cheaper

Roadmap

• Motivation
• Single-Attribute Indexes: Overview
• Order-based Indexes
  • B-Trees
• Hash-based Indexes
  • Extensible Hashing
  • Linear Hashing
• Multi-Attribute Indexes (Chapter 14 GMUW,

May cover in future)

Single Attribute Index: General Construction

b (^1)

b 2

b i

b n

a (^1)

a 2

a i

a n

A B

Exceptions

• Sparse Indexes
  • Require specific physical layout of relation
  • Example: Relation sorted on indexed attribute
  • More efficient

14

Single Attribute Index: General Construction

b (^1)

b 2

b i

b n

a (^1)

a 2

a i

a n

a (^1)

a 2

a i

a n

A B

A = val

A > low A < high

Textbook: Dense Index

Roadmap

• Motivation
• Single-Attribute Indexes: Overview
• Order-based Indexes
  • B-Trees
• Hash-based Indexes
  • Extensible Hashing
  • Linear Hashing
• Multi-Attribute Indexes (Next class)

B-Trees

• Adaptation of search tree data structure
  • 2-3 trees
• Supports range predicates (and equality)

Use Binary Search Tree Directly?

• Store records of type

<key, left-ptr, right-ptr, data-ptr>

• Remember position of root
• Question: will this work?
  • Yes
  • But we can do better!

Use Binary Search Tree Directly?

• Number of keys: 1 M
• Number of levels: log (2^20) = 20
• Total cost index lookup: 20 random disk I/O
  • 20 x 20 ms = 400 ms

20

B-Tree: less than 3 random disk I/O