What is index and why it is used??
1. Oracle includes numerous data structures to improve the speed of Oracle SQL queries. Taking advantage of the low cost of disk storage, Oracle includes many new indexing algorithms that dramatically increase the speed with which Oracle queries are serviced. This article explores the internals of Oracle indexing; reviews the standard b-tree index, bitmap indexes, function-based indexes, and index-only tables (IOTs); and demonstrates how these indexes may dramatically increase the speed of Oracle SQL queries.
Oracle uses indexes to avoid the need for large-table, full-table scans and disk sorts, which are required when the SQL optimizer cannot find an efficient way to service the SQL query. I begin our look at Oracle indexing with a review of standard Oracle b-tree index methodologies.
2. Oracle indexes : Proper database indexing is a crucial factor for your database performance.
Most Oracle databases have hundreds or even thousands of indexes. This large number of indexes and their complexity make index tuning and monitoring a difficult task for the DBA.
As time goes, even originally efficient indexes may become inefficient due to various index distortions caused by data changes in the indexed tables.
The question is: How to manage Oracle indexes and what different options are available to use them?
Indexes are logically and physically independent of the data in the associated table. The DBA can create or drop an index at anytime without affecting the base tables or other indexes. If the DBA drops an index, all applications continue to work.
However, access to previously indexed data might be slower.Indexes, being independent structures, require storage space.
Oracle automatically maintains and uses indexes after they are created. Oracle automatically reflects changes to data, such as adding new rows, updating rows, or deleting rows, in all relevant indexes with no additional action by users.