Expert indexing en oracle

www.it-ebooks.info

For your convenience Apress has placed some of the front matter material after the index. Please use the Bookmarks and Contents at a Glance links to access them.

www.it-ebooks.info

Contents at a Glance
 Contents.................................................................................................................... v  About the Authors.................................................................................................. xiii  About the Technical Reviewer ............................................................................... xiv  Acknowledgments .................................................................................................. xv  Chapter 1: OracleIndexes.........................................................................................1  Chapter 2: B-tree Indexes .......................................................................................19  Chapter 3: Bitmap Indexes .....................................................................................49  Chapter 4: Index-Organized Tables ........................................................................69  Chapter 5:Specialized Indexes ..............................................................................85  Chapter 6: Partitioned Indexes .............................................................................115  Chapter 7: Tuning Index Usage .............................................................................141  Chapter 8: Maintaining Indexes............................................................................171  Chapter 9: SQL Tuning Advisor .............................................................................205  Chapter 10: SQL Access Advisor...........................................................................233  Index .....................................................................................................................249

ivwww.it-ebooks.info

CHAPTER 1 ■■■

Oracle Indexes
An index is an optionally created database object used primarily to increase query performance. The purpose of a database index is similar to an index in the back of a book. A book index associates a topic with a page number. When you’re locating information in a book, it’s usually much faster to examine the index first, find the topic ofinterest, and identify associated page numbers. With this information, you can navigate directly to specific page numbers in the book. If the topic only appears on a few pages within the book, then the number of pages to read is minimal. In this manner, the usefulness of the index decreases with an increase in the number of times a topic appears in a book. Similar to a book index, a database indexstores the column value of interest along with its row identifier (ROWID). The ROWID contains the physical location of the table row on disk that stores the column value. With the ROWID in hand, Oracle can efficiently retrieve table data with a minimum of disk reads. In this way, indexes function like a shortcut to the table data. If there is no available index, then Oracle reads each row in thetable to determine if the row contains the desired information.

 Note In addition to improving performance, Oracle uses indexes to help enforce enabled primary key and unique key constraints. Additionally, Oracle can better manage table locking scenarios when indexes are placed on foreign key columns.

While it’s possible to build a database application devoid of indexes, without them you’realmost guaranteeing poor performance. Indexes allow for excellent scalability even with very large data sets. So if indexes are so important to database performance, why not place them on all tables and column combinations? The answer is short: indexes are not free. They consume disk space and system resources. As column values are modified, any corresponding indexes must also be updated. In this...