Planning for Robustness- Tables and Constraints
After you have decided that you need to create an index, it is prudent to make a few foundational decisions that will affect maintainability and availability. Oracle provides a wide assortment of indexing features and options. As a DBA or a developer, you need to be aware of the various features and how to use them. If you choose the wrong type of index or use a feature incorrectly, there may be serious, detrimental performance implications. Later we will discuss invisible indexes, which can be an easy way to hide an index from use before dropping, which can help with testing the right indexes. It is still recommended to take into consideration these listed manageability features before you create an index:
• Type of index
• Initial space required and growth
• Temporary tablespace usage for creation
• Tablespace placement
• Naming conventions
• Columns to include
• Single column or multicolumn (composite) indexes
• Special features, such as PARALLEL, NOLOGGING, COMPRESSION
• Uniqueness
• Functions that are used
• Impact on performance of SELECT statements
• Impact on performance of INSERT, UPDATE, and DELETE statements These topics are discussed in the next sections in this chapter.
Determining Which Type of Index to Use
Oracle provides a wide range of index types and features. The correct use of indexes results in a well-performing and scalable database application. Conversely, if you incorrectly or unwisely implement a feature, there may be detrimental performance implications. Table 8-1 summarizes the various Oracle index types available. At first glance, this is a long list and may be somewhat overwhelming to somebody new to Oracle. However, deciding which index type to use is not as daunting as it might initially seem. For most applications, you should simply use the default B-tree index type.
Table 8–1. Oracle Index Type and Usage Descriptions
In a deployment of Oracle on Exadata and with Autonomous Database, there are options to use automatic indexing and to use and implement indexing. How the indexes are chosen and why indexes are working better are part of information you can pull from the database. So, it is not a black box to tune which index to use; it can provide details so that you can put indexes into place automatically or review and implement the indexes when ready. Even with these deployments, it is helpful to understand the performance changes and index types.
You probably noticed that several of the index types listed are just variations on the B-tree index. A reverse-key index, for example, is merely a B-tree index optimized for evenly spreading I/O when the index value is sequentially generated and inserted with similar values. This chapter focuses on the most commonly used indexes and features, and index organized tables (IOT) were already covered in Chapter 7.