Stack Overflow for Teams — Collaborate and share knowledge with a private group. Create a free Team What is Teams? Collectives on Stack Overflow. Learn more. Ask Question.
Asked 12 years, 2 months ago. Active 3 years, 1 month ago. Viewed 38k times. Add a comment. Active Oldest Votes. Indexes are optional structures associated with tables and clusters that allow SQL statements to execute more quickly against a table. Just as the index in this manual helps you locate information faster than if there were no index, an Oracle Database index provides a faster access path to table data.
You can use indexes without rewriting any queries. Your results are the same, but you see them more quickly. Oracle Database provides several indexing schemes that provide complementary performance functionality. These are:. 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. Oracle Database Concepts for conceptual information about indexes and indexing, including descriptions of the various indexing schemes offered by Oracle. Oracle Database Data Cartridge Developer's Guide for information about defining domain-specific operators and indexing schemes and integrating them into the Oracle Database server. 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 database also swaps sort information to and from temporary segments that are only allocated during the index creation in the users temporary tablespace. Use the following guidelines for determining when to create an index:.
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 scan, the lower the percentage; the more clustered the row data, the higher the percentage. Small tables do not require indexes. If a query is taking too long, then the table might have grown from small to large. Some columns are strong candidates for indexing.
Columns with one or more of the following characteristics are candidates for indexing:. The column contains many nulls, but queries often select all rows having a value. In this case, use the following phrase:. The size of a single index entry cannot exceed roughly one-half minus some overhead of the available space in the data block. In general, specify the most frequently used columns first. 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 just col1 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. 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.
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. Estimating the size of an index before creating one can facilitate better disk space planning and management. You can use the combined estimated size of indexes, along with estimates for tables, the undo tablespace, and redo log files, to determine the amount of disk space that is required to hold an intended database.
From these estimates, you can make correct hardware purchases and other decisions. Use the estimated size of an individual index to better manage the disk space that the index uses.
For example, assume that you estimate the maximum size of an index before creating it. If you then set the storage parameters when you create the index, fewer extents are allocated for the table data segment, and all of the index data is stored in a relatively contiguous section of disk space. Indexes can be created in any tablespace. An index can be created in the same or different tablespace as the table it indexes.
If you use the same tablespace for a table and its index, it can be more convenient to perform database maintenance such as tablespace or file backup or to ensure application availability. All the related data is always online together. Using different tablespaces on different disks for a table and its index produces better performance than storing the table and index in the same tablespace.
Disk contention is reduced. But, if you use different tablespaces for a table and its index and one tablespace is offline containing either data or index , then the statements referencing that table are not guaranteed to work. 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. Oracle Database Concepts for more information about parallel execution.
Oracle Database Data Warehousing Guide for information about utilizing parallel execution in a data warehousing environment. However, for larger indexes the performance improvement can be significant, especially when you are also parallelizing the index creation. Improper sizing or increased growth can produce index fragmentation. To eliminate or reduce fragmentation, you can rebuild or coalesce the index. When we use the tablespace keyword, followed by a tablespace name, we are telling Oracle which tablespace to recreate the rebuilt index in.
By default Oracle will create the rebuilt index in the same tablespace. The alter index command allows you to rename an index using the rename to keyword as seen in this example:. All of the data dictionary entries will be changed by Oracle to reflect the new name we will discuss indexes and the data dictionary in a moment.
Sometimes what we create we must destroy. When it? The drop index command is pretty straight forward as seen in this example:. Feel free to ask questions on our Oracle forum. Verify experience!
0コメント