Lesson 1
What are Oracle Indexes
There is more to a database than simply storing data. Databases are useful because people can retrieve the data that is stored. The faster databases can retrieve the data, the more useful the database becomes. Indexes are structures that improve the performance of data retrieval. Indexes also serve other purposes, such as guaranteeing that data entered is unique, but their primary purpose is to retrieve meaningful data from the database more quickly.
This module covers the use and management of indexes. The use of indexes can significantly improve the performance of your database. Understanding the hows and whys of indexes will improve your abilities as a database administrator.
In this module, you will learn:
- 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
Definition of Index
An index is an optional data structure that you can create on one or more columns of a table. Indexes can increase the performance of data retrieval.
When processing a request, the database can use available indexes to locate the requested rows efficiently. Indexes are useful when applications often query a specific row or range of rows. Indexes are logically and physically independent of the data and you can drop and create indexes with no effect on the physical tables or other indexes. All applications continue to function after you drop an index.
Indexes are schema objects that are logically and physically independent of the data in the objects with which they are associated. Thus, an index can be dropped or created without physically affecting the table for the index. The absence or presence of an index does not require a change in the wording of any SQL statement. An index is a fast access path to a single row of data and affects only the speed of execution. Given a data value that has been indexed, the index points directly to the location of the rows containing that value. The database automatically maintains and uses indexes after they are created. The database also automatically reflects changes to data, such as adding, updating, and deleting rows, in all relevant indexes with no additional actions required by users. Retrieval performance of indexed data remains almost constant, even as rows are inserted. However, the presence of many indexes on a table degrades data manipulation language performance because the database must also update the indexes.
Indexes are schema objects that are logically and physically independent of the data in the objects with which they are associated. Thus, an index can be dropped or created without physically affecting the table for the index. The absence or presence of an index does not require a change in the wording of any SQL statement. An index is a fast access path to a single row of data and affects only the speed of execution. Given a data value that has been indexed, the index points directly to the location of the rows containing that value. The database automatically maintains and uses indexes after they are created. The database also automatically reflects changes to data, such as adding, updating, and deleting rows, in all relevant indexes with no additional actions required by users. Retrieval performance of indexed data remains almost constant, even as rows are inserted. However, the presence of many indexes on a table degrades data manipulation language performance because the database must also update the indexes.