Index Enhancements   «Prev  Next»

Lesson 11

Oracle Index Enhancements Conclusion

One of the good things about Oracle is that it is based on the solid foundation of Oracle9 and Oracle8. Most of the improvements to the basic indexing capabilities of Oracle are not brand-new features, but refinements of existing capabilities.
Nonetheless, these refinements can result in significant performance improvements in some situations.
In this module, you learned about enhancements to Oracle’s indexes, including how to:
  1. Describe the types of indexes that can be created in Oracle
  2. Create a descending index
  3. Describe the bitmap index improvements of Oracle
  4. Create a reverse index
  5. Describe two methods of creating statistics
  6. Describe the new Oracle feature of domain indexes
  7. Use the ONLINE parameter for indexes
  8. Define function-based indexing
  9. Create a function-based index

Indexes have the following properties:

  1. Usability:Indexes are usable (default) or unusable. An unusable index is not maintained by DML operations and is ignored by the optimizer. An unusable index can improve the performance of bulk loads. Instead of dropping an index and later re-creating it, you can make the index unusable and then rebuild it. Unusable indexes and index partitions do not consume space. When you make a usable index unusable, the database drops its index segment.
  2. Visibility: Indexes are visible (default) or invisible. An invisible index is maintained by DML operations and is not used by default by the optimizer. Making an index invisible is an alternative to making it unusable or dropping it. Invisible indexes are especially useful for testing the removal of an index before dropping it or using indexes temporarily without affecting the overall application.

Glossary

The following term was defined in this module:
  1. B* tree: An index made up of a number of levels of leaf nodes. Each leaf node contains a range of values and pointers to a more detailed leaf node. The lowest leaf node points to a page of index values and the ROWIDs of the rows associated with them.
  2. Bitwise comparison: A situation where the Oracle database compares two values bit by bit, instead of character by character (also known as byte by byte).
In the next module, you will learn about index-organized tables.

Oracle Index - Quiz

Click the Quiz link below to test your understanding of the concepts presented in this module.

Oracle Index - Quiz