SQL Performance Tuning

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 relational design issue: normalization. Because the SQL Standard only concerns itself with "rows" and "tables" and not with their physical mapping, this chapter is not about SQL:1999. However, it is about a de facto standard, because all DBMSs operate similarly.

Unfortunately, there is no standard terminology. For example, what most people call a "page" is a "data block" to Oracle users, and what most people call "partitioning" is "fragmentation" to Informix users, and the definition of "extent" is another thing that's far from fixed. We'll just have to ask you to look at the definitions of these terms in this chapter and in the glossary (Appendix B), and please avoid assuming that our choice of definition is the one that you're accustomed to from your experience of a particular DBMS.