ADB Lecture 2

Trying not to kill myself pt. 2

What are the key concepts of Disk Storage?

[What does it look like?]

Blocks (Pages), Records (Rows), Blocking Factor, Disk Access Time, Buffering

How is the Blocking Factor calculated?

True or False: Larger blocking factor means fewer disk accesses and better performance

True

What does Disk Access Time consist of?

Seek Time (Moving disk head), Rotational Delay (Waiting for disk to rotate), Transfer Time (Reading data)

What is the formula of Disk Access Time?

Total Access Time = Seek + Rotation + Transfer

How does Buffering help with disk speed?

The DBMS keeps frequently used blocks in RAM, and Reduces disk I/O.

What are the four ways records are arranged in files?

Heap file, Sequential file, Hashed file organisation, Index file organisation

What are the types of Indexing Structures?

B- tree, B+ tree, Clustered and Con-Clustered indexes

What is Hashing? (Hint: locations)

Hashing computes the storage location immediately.

What are the Collision Solutions?

• Chaining: Store a linked list in the bucket.

• Open Addressing: Search for another empty location. (linear probing, quadratic probing)

What are the main features of Indexing and Hashing?

Indexing: O(log n), supports ranged queries, ordered, used for ranged queries

Hashing: O(1), doesn’t support ranges queries, Unordered, used for exact matches

What is the core difference between Clustered and Non-Clustered indexes?

• Clustered index controls the physical order of data on disk.

• Non-clustered index a separate structure pointing to data.

How many Clustered indexes can you have in a table?

only ONE

How many Non-clustered indexes can you have in a table?

You can have many

How do you choose indexes?

1. Index columns used in WHERE clauses.

2. Index JOIN columns.

3. Index ORDER BY columns.

4. Use clustered indexes for range queries.

5. Use non-clustered indexes for exact matches.

6. Avoid indexing very small tables.

7. Avoid too many indexes.

8. Choose highly selectivity (unique) columns.

9. Use composite indexes for multi-column searches.