This book is chock-full of important information about Oracle database indexing. The comprehensive approach the authors take can best be described as holistic; they leave no stone unturned. One only needs to look at the expanded table of contents to appreciate the depth and breadth of the coverage.
Chapter 1 begins by introducing Oracle indexing, with the goal of improving database performance by properly utilizing indexing. Performance is perhaps best measured by the turnaround time of retrieving data from the database. While a complex indexing scheme may also keep the database server busier, typically, the user’s input desktop is on a different server and will therefore negligibly experience any performance disturbances by inputting data to the database. The choice of the index (B-tree or other specialized types) and determining which columns to index (primary, foreign, unique, or other suitable columns) directly affect performance.
Chapter 2 is a wonderful presentation of B-trees, and more importantly, of how Oracle uses B-trees as indices for the database. Three scenarios are presented: “all data lies in the index blocks,” “all information is not contained in the index,” and “only the table blocks are accessed.” Estimating the size of the index and establishing a naming standard are ancillary steps for a healthy index. The chapter then has a nice overview of creating a B-tree index, reporting on indexes, displaying index code for debugging, and dropping an index. This is further developed with the notion of constraints.
In both the information retrieval and database indexing communities there are those who argue that bitmap indexes are the most efficient manner of indexing. For databases, the truth depends on whether the database is a relational database or a hierarchical database, whether the data is frequently updated or static for data warehousing purposes, and whether the queries submitted are for characteristics of a particular row or for all rows containing a certain property. Chapter 3 explores bitmap indexes, from creation to performance implications, and understanding the join operation in such systems.
Index-organized tables are discussed in chapter 4. The chapter begins with their structure and advantages. The creation of an index-organized table is followed by its compression. Building secondary indexes, rebuilding organized tables, and reporting on organized tables complete the discussion. Specialized indexes (chapter 5) include invisible, function-based, key-compressed, reverse key, and application domain indexes.
Partitioned indexes are essential for maintaining large indexes. The functionalities provided include adding, truncating, moving, splitting, exchanging, dropping, and merging a partition. Locally partitioned indexes are differentiated from globally partitioned ones. Sometimes, it is necessary to set partitioned indexes as unusable and rebuild them for maximum efficiency. All of these topics are covered in chapter 6, which concludes with index implications for interval partitioning.
The last four chapters are higher-level discussions for tuning performance and striving for maximum efficiency. Chapter 7 deals with tuning index usage, optimizing access paths, avoiding indexes, forcing the optimizer to use an index, and, in contemporary terms, parallelizing index access. Chapter 8 instructs readers on how to maintain indexes, gather statistics, and work with unusable indexes. It also includes the debate over index rebuilding; coalescing and shrinking indexes to reduce fragmentation; and the overall enhancement of index creation efficiency. The SQL tuning advisor is explored in chapter 9, and the SQL access advisor is discussed in chapter 10.
The book is by authors who have vast experience with the subject matter. It will be of great interest to database administrators and application developers responsible for database performance.
