Lesson 10
Using Oracle indexes Conclusion
This module discussed why indexes play an important role in the efficient operation of your database.
In addition, indexes provide powerful features that enable you to increase the spead of your database.
In this module, you learned:
- Why indexes are used
- The basic types of indexes
- How to create an index
- Storage considerations for indexes
- How to alter an index
- How and why to rebuild an index
- How to find out about indexes from the data dictionary
- How to drop an index
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 are locating information in a book, it is usually much faster to examine the
index first, find the topic of interest, and identify associated page numbers. Using 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.
Database index
Similar to a book index, a database index stores 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 the table to determine if the row contains the desired information.
Why do we need indexes?
While it is possible to build a database application without indexes, without them you are guaranteeing poor performance. Indexes allow for excellent scalability even with very large data sets.
Question: So if indexes are so important to database performance, why not place them on all tables and column combinations?
Answer: indexes are not free and they consume disk space and system resources.
As column values are modified, any corresponding indexes must also be updated. In this way, indexes use storage, I/O, CPU, and memory resources.
A poor choice of indexes leads to wasted disk usage and excessive consumption of system resources. This results in a decrease in database performance.
For these reasons, when you design and build an Oracle database application, expert consideration must be given to your indexing strategy. As an application architect, you must understand the physical
properties of an index, what types of indexes are available, and strategies for choosing which table and column combinations to index. A correct indexing methodology is central to achieving maximum performance for your database.
Improving Performance with Indexes
How exactly does an index improve query performance? To understand how an index works, consider the following simple example. Suppose you create a table to hold customer information, like so:
create table cust
(cust_id number
,last_name varchar2(30)
,first_name varchar2(30));
Assuming your business grows quickly, after a short time, millions of customers have been created. You run daily reports against this table and notice that performance has progressively decreased when issuing queries like this:
select cust_id, last_name, first_name
from cust
where last_name = 'GOULD';
Glossary
In this module, you learned the following glossary terms:
- Contention: A source of possible performance degradation caused by two or more users contending for the same resources.
- Query Optimizer: A piece of software that selects the optimal way to retrieve data from a selection of possible retrieval paths.
The next module is about managing access to database objects.
Altering Indexes - Quiz