SQL Performance Tuning

Chapter 8. Tables

Performance depends on time. Time is physical. To see how table access depends on time, we must look at the physical storage of tables. The physical storage is on disk or in memory. You can influence physical storage when you CREATE TABLE and ALTER TABLE, and you can be influenced by physical storage when you SELECT, INSERT, UPDATE, and DELETE. These issues relate to the proper design of tables (the “logical” level), but to comprehend the issues you need to know how DBMSs use disks and memory (the “physical” level).

This chapter addresses the storage hierarchy, which is the way rows are grouped: in pages, extents, files, and tablespaces. That will naturally lead to a discussion of partitions, fragmentation, and the most important ...