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:
- Describe the types of indexes that can be created in Oracle
- Create a descending index
- Describe the bitmap index improvements of Oracle
- Create a reverse index
- Describe two methods of creating statistics
- Describe the new Oracle feature of domain indexes
- Use the
ONLINE parameter for indexes
- Define function-based indexing
- Create a function-based index
Between Oracle 10g and Oracle 19c, there have likely been several enhancements and developments in the area of indexing, reflecting Oracle's ongoing commitment to database performance and efficiency. While a comprehensive list of all changes would require a detailed review of Oracle's version-specific release notes and documentation, some key enhancements in the indexing process across these versions could include:
- Advanced Index Compression:
Starting with Oracle 11g and further enhanced in later versions, Oracle introduced advanced index compression options. This feature allows more efficient storage of indexes by reducing their size, which can improve query performance and decrease storage costs.
- Invisible Indexes:
Oracle 11g introduced the concept of invisible indexes. These indexes are not used by the optimizer by default, allowing for testing the impact of dropping an index without actually removing it. This feature can be particularly useful in performance tuning and index management.
- Function-Based Global Partitioned Indexes:
Enhancements in partitioning and indexing were made, such as function-based global partitioned indexes introduced in Oracle 11g. This feature provides more flexibility in index partitioning, especially useful for complex query environments.
- Online Index Operations:
Oracle has continuously improved online index operations. This includes more capabilities for online index rebuilding and reorganization, reducing the need for downtime and improving availability during maintenance operations.
- Index Enhancements for JSON Data:
With the increasing use of JSON data in Oracle databases, particularly in Oracle 12c and later, there were enhancements in indexing JSON data, allowing for more efficient querying of JSON structures.
- Automatic Indexing in Oracle 19c:
One of the significant advancements in Oracle 19c is the introduction of automatic indexing. This feature uses machine learning algorithms to automatically create, rebuild, and drop indexes based on the changing data access patterns. This can significantly reduce the manual effort involved in index tuning.
- Improvements in Bitmap Indexes:
There have been ongoing improvements in the performance and capabilities of bitmap indexes, which are particularly useful in data warehousing scenarios.
- Enhanced Domain Indexes:
Enhancements in domain-specific indexing capabilities, which are useful for specialized data types and application-specific needs.
It's important to note that each version of Oracle brings a plethora of changes, and the impact of these changes can vary depending on the specific use case and database configuration. For the most accurate and detailed information, consulting Oracle's official release notes and documentation for each version would provide the best insights.
The following term was defined in this module:
- 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.
- 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.
