It extends your indexing capabilities beyond indexing on a column. A function-based index increases the variety of ways in which you can access data. Function-based indexes also support linguistic sorts based on collation keys, efficient linguistic collation of SQL statements, and case-insensitive sorts. Like other indexes, function-based indexes improve query performance. For example, if you need to access a computationally complex expression often, then you can store it in an index.
Then when you need to access the expression, it is already computed. You can find a detailed description of the advantages of function-based indexes in "Advantages of Function-Based Indexes". Function-based indexes have all of the same properties as indexes on columns.
However, unlike indexes on columns which can be used by both cost-based and rule-based optimization, function-based indexes can be used by only by cost-based optimization. Other restrictions on function-based indexes are described in "Restrictions for Function-Based Indexes". For more information on function-based indexes, see Oracle9i Database Concepts. For information on creating function-based indexes, see Oracle9i Database Administrator's Guide. The optimizer can estimate how many rows are selected by expressions more accurately if the expressions are materialized in a function-based index.
Oracle sorts columns with the DESC keyword in descending order. Such indexes are treated as function-based indexes. Descending indexes cannot be bitmapped or reverse, and cannot be used in bitmapped optimizations. To get the pre-Oracle 8. A query could use this index to quickly find cities that are more than miles from the equator:.
Another index stores the temperature delta and the maximum temperature. The result of the delta is sorted in descending order. The rowid uniquely identifies a row. Primary key constraint may optionally be defined. Sequential full table scan returns all rows in some order. A full index scan or fast full index scan returns all rows in some order. Can be stored in a table cluster with other tables. Can contain virtual columns only relational heap tables are supported.
Figure illustrates the structure of an index-organized departments table. The leaf blocks contain the rows of the table, ordered sequentially by primary key. For example, the first value in the first leaf block shows a department ID of 20 , department name of Marketing , manager ID of , and location ID of An index-organized table stores all data in the same structure and does not need to store the rowid.
As shown in Figure , leaf block 1 in an index-organized table might contain entries as follows, ordered by primary key:. A scan of the index-organized table rows in primary key order reads the blocks in the following sequence:. To contrast data access in a heap-organized table to an index-organized table, suppose block 1 of a heap-organized departments table segment contains rows as follows:. A B-tree index leaf block for this heap-organized table contains the following entries, where the first value is the primary key and the second is the rowid :.
A scan of the table rows in primary key order reads the table segment blocks in the following sequence:. When creating an index-organized table, you can specify a separate segment as a row overflow area. In index-organized tables, B-tree index entries can be large because they contain an entire row, so a separate segment to contain the entries is useful.
In contrast, B-tree entries are usually small because they consist of the key and rowid. If a row overflow area is specified, then the database can divide a row in an index-organized table into the following parts:. This part contains column values for all the primary key columns, a physical rowid that points to the overflow part of the row, and optionally a few of the non-key columns. This part is stored in the index segment. This part contains column values for the remaining non-key columns.
This part is stored in the overflow storage area segment. A secondary index is an index on an index-organized table. In a sense, it is an index on an index. The secondary index is an independent schema object and is stored separately from the index-organized table. As explained in "Rowid Data Types" , Oracle Database uses row identifiers called logical rowids for index-organized tables. A logical rowid is a baseencoded representation of the table primary key. The logical rowid length depends on the primary key length.
Rows in index leaf blocks can move within or between blocks because of insertions. Rows in index-organized tables do not migrate as heap-organized rows do see "Chained and Migrated Rows". Because rows in index-organized tables do not have permanent physical addresses, the database uses logical rowids based on primary key. For example, assume that the departments table is index-organized. The table stores rows as follows, with the last value as the location ID:.
Secondary indexes provide fast and efficient access to index-organized tables using columns that are neither the primary key nor a prefix of the primary key. For example, a query of the names of departments whose ID is greater than could use the secondary index to speed data access.
Oracle Database Administrator's Guide to learn how to create secondary indexes on an index-organized table. Secondary indexes use the logical rowids to locate table rows. A logical rowid includes a physical guess , which is the physical rowid of the index entry when it was first made. Oracle Database can use physical guesses to probe directly into the leaf block of the index-organized table, bypassing the primary key search.
When the physical location of a row changes, the logical rowid remains valid even if it contains a physical guess that is stale. For index-organized tables, access by a secondary index varies, depending on the use and accuracy of physical guesses:. Without physical guesses, access involves two index scans: a scan of the secondary index followed by a scan of the primary key index.
A secondary index on an index-organized table can be a bitmap index. As explained in "Bitmap Indexes" , a bitmap index stores a bitmap for each index key. When bitmap indexes exist on an index-organized table, all the bitmap indexes use a heap-organized mapping table. The mapping table stores the logical rowids of the index-organized table. Each mapping table row stores one logical rowid for the corresponding index-organized table row. The database accesses a bitmap index using a search key.
If the database finds the key, then the bitmap entry is converted to a physical rowid. With heap-organized tables, the database uses the physical rowid to access the base table. With index-organized tables, the database uses the physical rowid to access the mapping table, which in turn yields a logical rowid that the database uses to access the index-organized table. This chapter contains the following sections: Overview of Indexes Overview of Index-Organized Tables Overview of Indexes An index is an optional structure, associated with a table or table cluster , that can sometimes speed data access.
To speed access, the manager could create an index that sequentially lists every employee ID with its folder location: ID Box 3, position 1 bottom ID Box 7, position 8 ID Box 1, position In general, consider creating an index on a column in any of the following situations: The indexed columns are queried frequently and return a small percentage of the total number of rows in the table.
See Also: Chapter 5, "Data Integrity". Index Characteristics Indexes are schema objects that are logically and physically independent of the data in the objects with which they are associated.
Note: If you drop an index, then applications still work. However, access of previously indexed data can be slower. Keys and Columns A key is a set of columns or expressions on which you can build an index. Note: Primary and unique keys automatically have indexes, but you might want to create an index on a foreign key.
Composite Indexes A composite index , also called a concatenated index , is an index on multiple columns in a table. Note: In some cases, such as when the leading column has very low cardinality, the database may use a skip scan of this index see "Index Skip Scan".
Unique and Nonunique Indexes Indexes can be unique or nonunique. Types of Indexes Oracle Database provides several indexing schemes, which provide complementary performance functionality. The indexes can be categorized as follows: B-tree indexes These indexes are the standard index type.
B-tree indexes have the following subtypes: Index-organized tables An index-organized table differs from a heap-organized because the data is itself the index.
Reverse key indexes In this type of index, the bytes of the index key are reversed, for example, is stored as Descending indexes This type of index stores data on a particular column or columns in descending order. B-tree cluster indexes This type of index is used to index a table cluster key. Bitmap and bitmap join indexes In a bitmap index, an index entry uses a bitmap to point to multiple rows.
Function-based indexes This type of index includes columns that are either transformed by a function, such as the UPPER function, or included in an expression. Application domain indexes This type of index is created by a user for data in an application-specific domain. B-Tree Indexes B-trees, short for balanced trees , are the most common type of database index.
Branch Blocks and Leaf Blocks A B-tree index has two types of blocks: branch blocks for searching and leaf blocks that store values. Note: Indexes in columns with character data are based on the binary values of the characters in the database character set. Index Scans In an index scan , the database retrieves a row by traversing the index, using the indexed column values specified by the statement. Full Index Scan In a full index scan , the database reads the entire index in order.
For example, the full scan could read the index entries as follows: 50,Atkinson,,rowid 60,Austin,,rowid 70,Baer,,rowid 80,Abel,,rowid 80,Ande,,rowid ,Austin,,rowid.
Fast Full Index Scan A fast full index scan is a full index scan in which the database accesses the data in the index itself without accessing the table, and the database reads the index blocks in no particular order.
Fast full index scans are an alternative to a full table scan when both of the following conditions are met: The index must contain all columns needed for the query. If the last name and salary are a composite key in an index, then a fast full index scan can read the index entries to obtain the requested information: Baida,,rowid Zlotkey,,rowid Austin,,rowid Baer,,rowid Atkinson,,rowid Austin,,rowid.
Index Range Scan An index range scan is an ordered scan of an index that has the following characteristics: One or more leading columns of an index are specified in conditions. Index Unique Scan In contrast to an index range scan, an index unique scan must have either 0 or 1 rowid associated with an index key.
Index Skip Scan An index skip scan uses logical subindexes of a composite index. Index Clustering Factor The index clustering factor measures row order in relation to an indexed value such as employee last name. The clustering factor is relevant for index scans because it can show: Whether the database will use an index for large range scans The degree of table organization in relation to the index key Whether you should consider using an index-organized table, partitioning, or table cluster if rows must be ordered by the index key For example, assume that the employees table fits into two data blocks.
Reverse Key Indexes A reverse key index is a type of B-tree index that physically reverses the bytes of each index key while keeping the column order. Ascending and Descending Indexes In an ascending index , Oracle Database stores data in ascending order.
Key Compression Oracle Database can use key compression to compress portions of the primary key column values in a B-tree index or an index-organized table. Note: If a key is not defined to have a unique piece, then the database provides one by appending a rowid to the grouping piece. Bitmap Indexes In a bitmap index , the database stores a bitmap for each index key. Situations that may call for a bitmap index include: The indexed columns have low cardinality , that is, the number of distinct values is small compared to the number of table rows.
Bitmap Indexes on a Single Table Example shows a query of the sh. Note: Bitmap indexes can include keys that consist entirely of null values, unlike B-tree indexes.
Bitmap Join Indexes A bitmap join index is a bitmap index for the join of two or more tables. Bitmap Storage Structure Oracle Database uses a B-tree index structure to store bitmaps for each indexed key. The same job title appears in multiple entries because the rowid range differs.
See Also: "User Segments". Function-Based Indexes You can create indexes on functions and expressions that involve one or more columns in the table being indexed. Application Domain Indexes An application domain index is a customized index specific to an application.
Oracle Database provides extensible indexing to do the following: Accommodate indexes on customized, complex data types such as documents, spatial data, images, and video clips see "Unstructured Data" Make use of specialized indexing techniques You can encapsulate application-specific index management routines as an indextype schema object and define a domain index on table columns or attributes of an object type.
Index Storage Oracle Database stores index data in an index segment. Overview of Index-Organized Tables An index-organized table is a table stored in a variation of a B-tree index structure.
Index-Organized Table Characteristics The database system performs all operations on index-organized tables by manipulating the B-tree index structure. Primary key uniquely identifies a row. Primary key constraint must be defined. A composite index key cannot exceed roughly one-half minus some overhead of the available space in the data block.
In this example, an index is created for a single column, to speed up queries that test that column:. In this example, the index applies to two columns, to speed up queries that test either the first column or both columns:. An index on the ENAME column itself would not speed up this operation, and it might be slow to call the function for each result row. A function-based index precomputes the result of the function for each column value, speeding up queries that use the function for searching or sorting:.
Domain indexes are appropriate for special-purpose applications implemented using data cartridges. The domain index helps to manipulate complex data, such as spatial, audio, or video data. Oracle Database supplies a number of specialized data cartridges to help manage these kinds of complex data. So, if you need to create a search engine, or a geographic information system, you can do much of the work simply by creating the right kind of index.
A function-based index is an index built on an expression. It extends your indexing capabilities beyond indexing on a column. A function-based index increases the variety of ways in which you can access data. Function-based indexes also support linguistic sorts based on collation keys, efficient linguistic collation of SQL statements, and case-insensitive sorts. Like other indexes, function-based indexes improve query performance. For example, if you need to access a computationally complex expression often, then you can store it in an index.
Then when you need to access the expression, it is already computed. You can find a detailed description of the advantages of function-based indexes in "Advantages of Function-Based Indexes".
Function-based indexes have all of the same properties as indexes on columns. Save my name, email, and website in this browser for the next time I comment. Toggle navigation. By Richard Niemiec on April 20, In version 7, this number was reduced to about 7 percent on average, and in versions 8i and 9i, the number was closer to 4 percent.
0コメント