Indexes and Optimizing Techniques for Oracle Database XE
(Page 1 of 2 )
Looking for a way to use indexes for your Oracle databases more efficiently? This three-part article series should help. It is excerpted from chapter 38 of the book Beginning PHP and Oracle: From Novice to Professional, written by W. Jason Gilmore and Bob Bryla (Apress; ISBN: 1590597702).
In Chapter 30, we presented table constraints such asPRIMARY KEYandCHECK. In that same chapter, we introduced unique indexes as a way to enforce aPRIMARY KEYconstraint. In addition to using indexes to enforce constraints, you can use indexes to boost the performance of queries significantly by reducing the amount of time needed to retrieve rows from a table instead of reading every row in the table to find the row or rows you are looking for. However, too many indexes on a table can be just as bad as not enough.
In this chapter, we delve more deeply into how to use indexes most effectively, how to manage indexes, and how to monitor index usage. Finally, we show how you can use the Oracle Database XE GUI to see the structure of the indexes in the database and create domain indexes, another type of Oracle index.
Understanding Oracle Index Types
Also in Chapter 30, we introduced two types of indexes: B-tree and bitmap. They both accomplish a common goal: reducing the amount of time required to retrieve rows from a table. However, they are constructed differently, and you choose one or the other based on the existing and expected type and distribution of the data in the column or columns to be indexed. Unless your tables are very small, your queries will benefit from indexed columns. Traversing an index to find a particular row or many rows using the conditions in the WHERE clause will typically take less time than reading every row of the table itself.
Indexes are both logically and physically independent of the rows in the indexed tables. The indexes themselves can be dropped and added without affecting the table data or the queries you use against the tables (except for affecting the performance of the query). Oracle automatically maintains entries in an index as rows in the indexed table are added, modified, or deleted. When you drop a table, Oracle drops all associated indexes as well.
Note We discuss another type of index, a domain index, later in this chapter in the section “Using Oracle Text.”
In the following sections, we give you a bit more detail about B-tree and bitmap indexes and how they are constructed. B-tree indexes have several subtypes. We identify and explain each of the subtypes and when to use them.
