Home / Oracle DBA / Managing Indexes

Managing Indexes

Managing Indexes

About Indexes
Indexes are optional structures associated with tables and clusters that allow SQL queries to execute more quickly against a table. Oracle Database provides several indexing schemes that provide complementary performance functionality. These are:
B-tree indexes: the default and the most common
B-tree cluster indexes: defined specifically for cluster
Hash cluster indexes: defined specifically for a hash cluster
Global and local indexes: relate to partitioned tables and indexes
Reverse key indexes: most useful for Oracle Real Application Clusters applications
Bitmap indexes: compact; work best for columns with a small set of values
Function-based indexes: contain the pre computed value of a function/expression
Domain indexes: specific to an application or cartridge.
Indexes are logically and physically independent of the data in the associated table. Being independent structures, they require storage space. You can create or drop an index without affecting the base tables, database applications, or other indexes. The database automatically maintains indexes when you insert, update, and delete rows of the associated table. If you drop an index, all applications continue to work. However, access to previously indexed data might be slower.
Guidelines for Managing Indexes
Create Indexes After Inserting Table Data
Data is often inserted or loaded into a table using either the SQL*Loader or an import utility. It is more efficient to create an index for a table after inserting or loading the data. If you create one or more indexes before loading data, the database then must update every index as each row is inserted. Creating an index on a table that already has data requires sort space. Some sort space comes from memory allocated for the index creator. The amount for each user is determined by the initialization parameter SORT_AREA_SIZE. The database also swaps sort information to and from temporary segments that are only allocated during the index creation in the users temporary table space. Under certain conditions, data can be loaded into a table with SQL*Loader direct-path load and an index can be created as data is loaded.
Index the Correct Tables and Columns
Use the following guidelines for determining when to create an index:
Create an index if you frequently want to retrieve less than 15% of the rows in a large table. The percentage varies greatly according to the relative speed of a table scan and how the distribution of the row data in relation to the index key. The faster the table scans, the lower the percentage; the more clustered the row data, the higher the percentage.
To improve performance on joins of multiple tables, index columns used for joins.
Small tables do not require indexes. If a query is taking too long, then the table might have grown from small to large.
Columns That Are Suitable for Indexing
Some columns are strong candidates for indexing. Columns with one or more of the following characteristics are candidates for indexing:
Values are relatively unique in the column.
There is a wide range of values (good for regular indexes).
There is a small range of values (good for bitmap indexes).
The column contains many nulls, but queries often select all rows having a value.
Columns That Are Not Suitable for Indexing
Columns with the following characteristics are less suitable for indexing:
There are many nulls in the column and you do not search on the not null values.
LONG and LONG RAW columns cannot be indexed.
Virtual Columns
You can create unique or non-unique indexes on virtual columns.
Order Index Columns for Performance
The order of columns in the CREATE INDEX statement can affect query performance.
In general, specify the most frequently used columns first.
Note: Primary and unique keys automatically have indexes, but you might want to create an index on a foreign key.
If you create a single index across columns to speed up queries that access, for example, col1, col2, and col3; then queries that access just col1, or that access justcol1 and col2, are also speeded up. But a query that accessed just col2, just col3,or just col2 and col3 does not use the index.
Limit the Number of Indexes for Each Table
A table can have any number of indexes. However, the more indexes there are, the more overhead is incurred as the table is modified. Specifically, when rows are inserted or deleted, all indexes on the table must be updated as well. Also, when a column is updated, all indexes that contain the column must be updated. Thus, there is a trade-off between the speed of retrieving data from a table and the speed of updating the table. For example, if a table is primarily read-only, having more indexes can be useful; but if a table is heavily updated, having fewer indexes could be preferable.
Drop Indexes That Are No Longer Required
Consider dropping an index if:
It does not speed up queries. The table could be very small, or there could be many rows in the table but very few index entries.
The queries in your applications do not use the index.
The index must be dropped before being rebuilt.
Specify the Table space for Each Index
Indexes can be created in any table space. An index can be created in the same or different table space as the table it indexes. If you use the same table space for a table and its index, it can be more convenient to perform database maintenance (such as table space or file backup) or to ensure application availability. All the related data is always online together. Using different table spaces (on different disks) for a table and its index produces better performance than storing the table and index in the same table space. Disk contention is reduced. But, if you use different table spaces for a table and its index and one table space is offline (containing either data or index), then the statements referencing that table are not guaranteed to work.
Consider Parallelizing Index Creation
You can parallelize index creation, much the same as you can parallelize table creation. Because multiple processes work together to create the index, the database can create the index more quickly than if a single server process created the index sequentially. When creating an index in parallel, storage parameters are used separately by each query server process. Therefore, an index created with an INITIAL value of 5M and a parallel degree of 12 consumes at least 60M of storage during index creation.
Consider Creating Indexes with NOLOGGING
You can create an index and generate minimal redo log records by specifying NO LOGGING in the CREATE INDEX statement. Creating an index with NO LOGGING has the following benefits:
Space is saved in the redo log files.
The time it takes to create the index is decreased.
Performance improves for parallel creation of large indexes.
In general, the relative performance improvement is greater for larger indexes created without LOGGING than for smaller ones. Creating small indexes without LOGGING has little effect on the time it takes to create an index. However, for larger indexes the performance improvement can be significant, especially when you are also parallelizing the index creation.
Note: Because indexes created using NO LOGGING are not archived, perform a backup after you create the index.
Understand When to Use Unusable or Invisible Indexes
Use unusable or invisible indexes when you want to improve the performance of bulk loads, test the effects of removing an index before dropping it, or otherwise suspend the use of an index by the optimizer.
Unusable indexes
An unusable index is ignored by the optimizer and is not maintained by DML. One reason to make an index unusable is to improve bulk load performance. (Bulk loads go more quickly if the database does not need to maintain indexes when inserting rows.)Instead of dropping the index and later re-creating it, which requires you to recall the exact parameters of the CREATE INDEX statement, you can make the index unusable, and then rebuild it.
You can create an index in the unusable state, or you can mark an existing index or index partition unusable. In some cases the database may mark an index unusable, such as when a failure occurs while building the index. When one partition of a partitioned index is marked unusable, the other partitions of the index remain valid. An unusable index or index partition must be rebuilt, or dropped and re-created, before it can be used. Truncating a table makes an unusable index valid. Beginning with Oracle Database 11g Release 2, when you make an existing index unusable, its index segment is dropped.
The functionality of unusable indexes depends on the setting of the SKIP_UNUSABLE_INDEXES initialization parameter. When SKIP_UNUSABLE_INDEXES is TRUE (the default), then:
DML statements against the table proceed, but unusable indexes are not maintained.
DML statements terminate with an error if there are any unusable indexes that are used to enforce the UNIQUE constraint.
For non-partitioned indexes, the optimizer does not consider any unusable indexes when creating an access plan for SELECT statements. The only exception is when an index is explicitly specified with the INDEX() hint.
For a partitioned index where one or more of the partitions are unusable, the optimizer does not consider the index if it cannot determine at query compilation time if any of the index partitions can be pruned. This is true for both partitioned and non-partitioned tables. The only exception is when an index is explicitly specified with the INDEX () hint. When SKIP_UNUSABLE_INDEXES is FALSE, then:
If any unusable indexes or index partitions are present, any DML statements that would cause those indexes or index partitions to be updated are terminated with an error.
For SELECT statements, if an unusable index or unusable index partition is present but the optimizer does not choose to use it for the access plan, the statement proceeds. However, if the optimizer does choose to use the unusable index or unusable index partition, the statement terminates with an error.
Invisible Indexes
Beginning with Oracle Database 11g Release 1, you can create invisible indexes or make an existing index invisible. An invisible index is ignored by the optimizer unless you explicitly set the OPTIMIZER_USE_INVISIBLE_INDEXES initialization parameter to TRUE at the session or system level. Unlike unusable indexes, an invisible index is maintained during DML statements. Although you can make a partitioned index invisible, you cannot make an individual index partition invisible while leaving the other partitions visible. Using invisible indexes, you can do the following:
Test the removal of an index before dropping it.
Use temporary index structures for certain operations or modules of an application without affecting the overall application.
Repairing Corrupted Data
Options for Repairing Data Block Corruption
Oracle Database provides different methods for detecting and correcting data block corruption. One method of correction is to drop and re-create an object after the corruption is detected. However, this is not always possible or desirable. If data block corruption is limited to a subset of rows, then another option is to rebuild the table by selecting all data except for the corrupt rows. Another way to manage data block corruption is to use the DBMS_REPAIR package. You can use DBMS_REPAIR to detect and repair corrupt blocks in tables and indexes. You can continue to use objects while you attempt to rebuild or repair them.
Note: Any corruption that involves the loss of data requires analysis and understanding of how that data fits into the over all database system. Depending on the nature of the repair, you might lose data, and logical inconsistencies can be introduced. You must determine whether the repair approach provided by this package is the appropriate tool for each specific corruption problem.
DBMS_REPAIR Procedures
The following table lists the procedures included in the DBMS_REPAIR package.
Limitations and Restrictions
DBMS_REPAIR procedures have the following limitations:
Tables with LOB data types, nested tables, and v arrays are supported, but the out of line columns are ignored.
Clusters are supported in the SKIP_CORRUPT_BLOCKS and REBUILD_FREELISTS procedures, but not in the CHECK_OBJECT procedure.
Index-organized tables and LOB indexes are not supported.
The DUMP_ORPHAN_KEYS procedure does not operate on bitmap indexes or function-based indexes.
The DUMP_ORPHAN_KEYS procedure processes keys that are no more than 3,950bytes long.
Using the DBMS_REPAIR Package
The following approach is recommended when considering DBMS_REPAIR for addressing data block corruption:
Task 1: Detect and Report Corruptions
The first task is the detection and reporting of corruptions. Reporting not only indicates what is wrong with a block, but also identifies the associated repair directive. There are several ways to detect corruptions.
The CHECK_OBJECT procedure checks and reports block corruptions for a specified object. Similar to the ANALYZE…VALIDATE STRUCTURE statement for indexes and tables, block checking is performed for index and data blocks. Not only does CHECK_OBJECT report corruptions, but it also identifies any fixes that would occur if FIX_CORRUPT_BLOCKS is subsequently run on the object. This information is made available by populating a repair table, which must first be created by the ADMIN_TABLES procedure. After you run the CHECK_OBJECT procedure, a simple query on the repair table shows the corruptions and repair directives for the object. With this information, you can assess how best to address the reported problems.
DB_VERIFY: Performing an Offline Database Check
Use DB_VERIFY as an offline diagnostic utility when you encounter data corruption.
ANALYZE: Reporting Corruption
The ANALYZE TABLE…VALIDATE STRUCTURE statement validates the structure of the analyzed object. If the database encounters corruption in the structure of the object, then an error message is returned. In this case, drop and re-create the object. You can use the CASCADE clause of the ANALYZE TABLE statement to check the structure of the table and all of its indexes in one operation. Because this operation can consume significant resources, there is a FAST option that performs a lightweight check.
DB_BLOCK_CHECKING Initialization Parameter
You can enable database block checking by setting the DB_BLOCK_CHECKING initialization parameter to TRUE. This checks data and index blocks for internal consistency whenever they are modified. DB_BLOCK_CHECKING is a dynamic parameter, modifiable by the ALTER SYSTEM SET statement. Block checking is always enabled for the system table space.
Task 2: Evaluate the Costs and Benefits of Using DBMS_REPAIR
Before using DBMS_REPAIR you must weigh the benefits of its use in relation to the liabilities. You should also examine other options available for addressing corrupt objects. Begin by answering the following questions:
What is the extent of the corruption?
To determine if there are corruptions and repair actions, execute the CHECK_OBJECT procedure and query the repair table.
What other options are available for addressing block corruptions? Consider the following:
If the data is available from another source, then drop, re-create, and repopulate the object.
Issue the CREATE TABLE…AS SELECT statement from the corrupt table to create a new one.
Ignore the corruption by excluding corrupt rows from SELECT statements.
Perform media recovery.
What logical corruptions or side effects are introduced when you use DBMS_REPAIR to make an object usable? Can these be addressed? What is the effort required to do so?
It is possible that you do not have access to rows in blocks marked corrupt. However, a block can be marked corrupt even if there are rows that you can validly access. It is also possible that referential integrity constraints are broken when blocks are marked corrupt. If this occurs, then disable and re enable the constraint; any inconsistencies are reported. After fixing all problems, you should be able tore enable the constraint.
Logical corruption can occur when there are triggers defined on the table. For example, if rows are reinserted, should insert triggers be fired or not? You can address these issues only if you understand triggers and their use in your installation. If indexes and tables are not synchronized, then execute the DUMP_ORPHAN_KEYS procedure to obtain information from the keys that might be useful in rebuilding corrupted data. Then issue the ALTER INDEX…REBUILD ONLINE statement to synchronize the table with its indexes.
If repair involves loss of data, can this data be retrieved?
You can retrieve data from the index when a data block is marked corrupt. The DUMP_ORPHAN_KEYS procedure can help you retrieve this information.
Task 3: Make Objects Usable
DBMS_REPAIR makes the object usable by ignoring corruptions during table and index scans.
Corruption Repair: Using the FIX_CORRUPT_BLOCKS and SKIP_CORRUPT_BLOCKS Procedures
You can make a corrupt object usable by establishing an environment that skips corruptions that remain outside the scope of DBMS_REPAIR capabilities. If corruptions involve a loss of data, such as a bad row in a data block, all such blocks are marked corrupt by the FIX_CORRUPT_BLOCKS procedure. Then you can run the SKIP_CORRUPT_BLOCKS procedure, which skips blocks that are marked as corrupt. When the SKIP_FLAG parameter in the procedure is set, table and index scans skip all blocks marked corrupt. This applies to both media and software corrupt blocks.
Implications when Skipping Corrupt Blocks
If an index and table are not synchronized, then a SET TRANSACTION READ ONLY transaction can be inconsistent in situations where one query probes only the index, and a subsequent query probes both the index and the table. If the table block is marked corrupt, then the two queries return different results, thereby breaking the rules of a read-only transaction. One way to approach this is not to skip corruptions in a SET TRANSACTION READ ONLY transaction. A similar issue occurs when selecting rows that are chained. A query of the same row may or may not access the corruption, producing different results.
Task 4: Repair Corruptions and Rebuild Lost Data
After making an object usable, perform the following repair activities.
Recover Data Using the DUMP_ORPHAN_KEYS Procedures
The DUMP_ORPHAN_KEYS procedure reports on index entries that point to rows incorrupt data blocks. All such index entries are inserted into an orphan key table that stores the key and row id of the corruption. After the index entry information has been retrieved, you can rebuild the index using the ALTER INDEX…REBUILD ONLINE statement.
Fix Segment Bitmaps Using the SEGMENT_FIX_STATUS Procedure
Use this procedure if free space in segments is being managed by using bitmaps (SEGMENT SPACE MANAGEMENT AUTO).This procedure recalculates the state of a bitmap entry based on the current contents of the corresponding block. Alternatively, you can specify that a bitmap entry be set to a specific value. Usually the state is recalculated correctly and there is no need to force a setting.
View More:
Shared Server Architecture
Overview of Background Processes
Server Result Cache
Subscribe To Newsletter
Be the first to get latest updates and exclusive content straight to your email inbox.
Stay Updated
Give it a try, you can unsubscribe anytime.

Check Also

Oracle 10g to 11g Enhancements (Difference between Oracle 10g and 11g)

Difference between Oracle 10g and 11g 1- Enhanced Automatic Memory Management System Oracle 9i automated …

Leave a Reply

Your email address will not be published. Required fields are marked *

Subscribe To Newsletter
Be the first to get latest updates and exclusive content straight to your email inbox.
Stay Updated
Give it a try, you can unsubscribe anytime.

Your information will never be shared
Sign-up for exclusive content. Be the first to hear about DBA Centre Blogs.