Lesson 3: Physical Storage, File Organization, and Indexing

Logical design answers what data should exist. Physical design answers how that data is stored and accessed efficiently.

A database may have correct tables and still perform poorly if the physical design is weak. This is where a DBA starts thinking about:

• data pages and blocks
• file organization
• storage allocation
• access paths
• indexes

At a simple level, when the DBMS stores table data, it places rows into storage structures. Reading the needed rows may involve scanning many pages or using shortcuts such as indexes.

A full table scan is sometimes acceptable, especially for small tables. But for large operational tables like payments, admissions, or transactions, repeated full scans can overload the system.

Physical design is a trade-off:

• more indexes may improve reads
• but extra indexes usually slow inserts, updates, and deletes
• wide rows may reduce joins
• but also increase storage and I/O cost

The DBA's job is not to "add indexes everywhere." The DBA studies workload patterns first.

An index is a structure that helps the DBMS locate rows faster for certain search conditions.

A common analogy is a book index. Instead of reading every page to find a topic, you check the index and jump closer to the target.

Indexes are especially useful for:

• equality filters such as WHERE student_id = 101
• range filters such as WHERE payment_date >= '2026-06-01'
• join columns
• columns used in sorting or grouping

But indexes are not magic. They work best when:

• the column is selective enough
• the query actually uses that column in a searchable way
• the workload justifies the maintenance cost

Example:

SELECT *
FROM payments
WHERE student_id = 101;

An index on student_id is often useful here.

But if you write:

WHERE UPPER(student_name) = 'ANA CRUZ'

the DBMS may not use a basic index on student_name efficiently, depending on the system and index type.

DBAs also consider composite indexes, where multiple columns are indexed together. These can be powerful, but the column order matters.