To scan the index, the database moves backward or forward through the leaf blocks. For example, a scan for IDs between 10 and 40 locates the first index leaf block that contains the lowest key value that is 10 or greater. The scan then proceeds horizontally through the linked list of leaf nodes until it locates a value greater than In contrast to an index range scan, an index unique scan must have either 0 or 1 rowid associated with an index key.
The database performs a unique scan when a predicate references all of the columns in a UNIQUE index key using an equality operator. An index unique scan stops processing as soon as it finds the first record because no second record is possible.
In this case, the database can use an index unique scan to locate the rowid for the employee whose ID is 5. An index skip scan uses logical subindexes of a composite index.
The database "skips" through a single index as if it were searching separate indexes. Skip scanning is beneficial if there are few distinct values in the leading column of a composite index and many distinct values in the nonleading key of the index. The database may choose an index skip scan when the leading column of the composite index is not specified in a query predicate.
For example, assume that you run the following query for a customer in the sh. Example shows a portion of the index entries. In a skip scan, the number of logical subindexes is determined by the number of distinct values in the leading column. In Example , the leading column has two possible values. The database logically splits the index into one subindex with the key F and a second subindex with the key M. When searching for the record for the customer whose email is Abbey company.
Conceptually, the database processes the query as follows:. The index clustering factor measures row order in relation to an indexed value such as employee last name.
The more order that exists in row storage for this value, the lower the clustering factor. The index entries point to random table blocks, so the database may have to read and reread the same blocks over and over again to retrieve the data pointed to by the index. The index keys in a range tend to point to the same data block, so the database does not have to read and reread the same blocks over and over.
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. Table depicts the rows in the two data blocks the ellipses indicate data that is not shown.
Rows are stored in the blocks in order of last name shown in bold. For example, the bottom row in data block 1 describes Abel, the next row up describes Ande, and so on alphabetically until the top row in block 1 for Steven King.
The bottom row in block 2 describes Kochar, the next row up describes Kumar, and so on alphabetically until the last row in the block for Zlotkey. Assume that an index exists on the last name column. Each name entry corresponds to a rowid. Conceptually, the index entries would look as follows:. Assume that a separate index exists on the employee ID column. Conceptually, the index entries might look as follows, with employee IDs distributed in almost random locations throughout the two blocks:.
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. For example, if the index key is 20 , and if the two bytes stored for this key in hexadecimal are C1,15 in a standard B-tree index, then a reverse key index stores the bytes as 15,C1.
Reversing the key solves the problem of contention for leaf blocks in the right side of a B-tree index. This problem can be especially acute in an Oracle Real Application Clusters Oracle RAC database in which multiple instances repeatedly modify the same block.
For example, in an orders table the primary keys for orders are sequential. One instance in the cluster adds order 20, while another adds 21, with each instance writing its key to the same leaf block on the right-hand side of the index. In a reverse key index, the reversal of the byte order distributes inserts across all leaf keys in the index.
For example, keys such as 20 and 21 that would have been adjacent in a standard key index are now stored far apart in separate blocks. Because the data in the index is not sorted by column key when it is stored, the reverse key arrangement eliminates the ability to run an index range scanning query in some cases.
For example, if a user issues a query for order IDs greater than 20, then the database cannot start with the block containing this ID and proceed horizontally through the leaf blocks. In an ascending index , Oracle Database stores data in ascending order. By default, character data is ordered by the binary values contained in each byte of the value, numeric data from smallest to largest number, and date from earliest to latest value.
For an example of an ascending index, consider the following SQL statement:. Oracle Database sorts the hr. In this case, the index stores data on a specified column or columns in descending order. If the index in Figure on the employees. The default search through a descending index is from highest to lowest value.
Descending indexes are useful when a query sorts some columns ascending and others descending. If a user queries hr. Oracle Database Performance Tuning Guide to learn more about ascending and descending index searches. 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. Key compression can greatly reduce the space consumed by the index.
In general, index keys have two pieces, a grouping piece and a unique piece. Key compression breaks the index key into a prefix entry , which is the grouping piece, and a suffix entry , which is the unique or nearly unique piece. The database achieves compression by sharing the prefix entries among the suffix entries in an index block.
By default, the prefix of a unique index consists of all key columns excluding the last one, whereas the prefix of a nonunique index consists of all key columns. For example, suppose that you create a composite index on the oe.
An index block may have entries as shown in Example If this index were created with default key compression, then duplicate key prefixes such as online , 0 and online , 2 would be compressed. Conceptually, the database achieves compression as shown in the following example:.
Suffix entries form the compressed version of index rows. Each suffix entry references a prefix entry, which is stored in the same index block as the suffix entry.
Alternatively, you could specify a prefix length when creating a compressed index. For the values in Example , the index would factor out duplicate occurrences of online as follows:. The index stores a specific prefix once per leaf block at most. Only keys in the leaf blocks of a B-tree index are compressed.
In the branch blocks the key suffix can be truncated, but the key is not compressed. Oracle Database Administrator's Guide to learn how to use compressed indexes. In a bitmap index , the database stores a bitmap for each index key. In a conventional B-tree index, one index entry points to a single row. In a bitmap index, each index key stores pointers to multiple rows.
Bitmap indexes are primarily designed for data warehousing or environments in which queries reference many columns in an ad hoc fashion. 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. The indexed table is either read-only or not subject to significant modification by DML statements.
For a data warehouse example, the sh. Suppose that queries for the number of customers of a particular gender are common. In this case, the customers. Each bit in the bitmap corresponds to a possible rowid. If the bit is set, then the row with the corresponding rowid contains the key value.
A mapping function converts the bit position to an actual rowid, so the bitmap index provides the same functionality as a B-tree index although it uses a different internal representation. If the indexed column in a single row is updated, then the database locks the index key entry for example, M or F and not the individual bit mapped to the updated row. Because a key points to many rows, DML on indexed data typically locks all of these rows.
For this reason, bitmap indexes are not appropriate for many OLTP applications. Oracle Database Performance Tuning Guide to learn how to use bitmap indexes for performance. Oracle Database Data Warehousing Guide to learn how to use bitmap indexes in a data warehouse. Example shows a query of the sh. Some columns in this table are candidates for a bitmap index. A bitmap index is probably not useful for the other columns.
Instead, a unique B-tree index on these columns would likely provide the most efficient representation and retrieval. It consists of two separate bitmaps, one for each gender. A mapping function converts each bit in the bitmap to a rowid of the customers table.
Each bit value depends on the values of the corresponding row in the table. For example, the bitmap for the M value contains a 1 as its first bit because the gender is M in the first row of the customers table.
An analyst investigating demographic trends of the customers may ask, "How many of our female customers are single or divorced? Bitmap indexes can process this query efficiently by counting the number of 1 values in the resulting bitmap, as illustrated in Table To identify the customers who satisfy the criteria, Oracle Database can use the resulting bitmap to access the table.
Rows that satisfy some, but not all, conditions are filtered out before the table itself is accessed. This technique improves response time, often dramatically. For each value in a table column, the index stores the rowid of the corresponding row in the indexed table. In contrast, a standard bitmap index is created on a single table. A bitmap join index is an efficient means of reducing the volume of data that must be joined by performing restrictions in advance.
For an example of when a bitmap join index would be useful, assume that users often query the number of employees with a particular job type. A typical query might look as follows:. The preceding query would typically use an index on jobs. To retrieve the data from the index itself rather than from a scan of the tables, you could create a bitmap join index as follows:. As illustrated in Figure , the index key is jobs.
A query of the number of accountants can use the index to avoid accessing the employees and jobs tables because the index itself contains the requested information. In a data warehouse, the join condition is an equijoin it uses the equality operator between the primary key columns of the dimension tables and the foreign key columns in the fact table.
Bitmap join indexes are sometimes much more efficient in storage than materialized join views, an alternative for materializing joins in advance. Oracle Database uses a B-tree index structure to store bitmaps for each indexed key. For example, if jobs. The individual bitmaps are stored in the leaf blocks. Assume that the jobs. A bitmap index entry for this index has the following components:.
In this case, the session requires exclusive access to the index key entry for the old value Shipping Clerk and the new value Stock Clerk.
The data for a bitmap index is stored in one segment. Oracle Database stores each bitmap in one or more pieces. Each piece occupies part of a single data block. You can create indexes on functions and expressions that involve one or more columns in the table being indexed. A function-based index computes the value of a function or expression involving one or more columns and stores it in the index.
A function-based index can be either a B-tree or a bitmap index. For example, a function could add the values in two columns. Oracle Database Administrator's Guide to learn how to create function-based indexes. Oracle Database Performance Tuning Guide for more information about using function-based indexes.
Function-based indexes are efficient for evaluating statements that contain functions in their WHERE clauses. The database only uses the function-based index when the function is included in a query.
The database can use the preceding index when processing queries such as Example partial sample output included. Example Query Containing an Arithmetic Expression.
You create the following function-based index on the hr. A function-based index is also useful for indexing only specific rows in a table. To index only the A rows, you could write a function that returns a null value for any rows other than the A rows. You could create the index as follows:. Oracle Database Globalization Support Guide for information about linguistic indexes. The optimizer can use an index range scan on a function-based index for queries with expressions in WHERE clause.
The range scan access path is especially beneficial when the predicate WHERE clause has low selectivity.
A virtual column is useful for speeding access to data derived from expressions. The optimizer performs expression matching by parsing the expression in a SQL statement and then comparing the expression trees of the statement and the function-based index. This comparison is case-insensitive and ignores blank spaces. Oracle Database Performance Tuning Guide for more information about gathering statistics.
Like this: Like Loading Adding a Datafile to Temp Tablespace. Leave a Reply Cancel reply Enter your comment here Fill in your details below or click an icon to log in:. Email required Address never made public. Name required. Follow Following. Sign me up. Already have a WordPress. Log in now. Post was not sent - check your email addresses! The safest method is to use a monitoring period which spans the whole lifecycle of your application, including any OLTP and batch operations.
If you are using a version prior to Oracle From Oracle When a foreign key is unindexed, DML on the parent primary key results in a share row exclusive table lock or share-subexclusive table lock, SSX on the child table, preventing DML from other transactions against the child table. If the DML affects several rows in the parent table, the lock on the child table is obtained and released immediately for each row in turn.
When a foreign key is indexed, DML on the parent primary key results in a row share table lock or subshare table lock, SS on the child table. This type of lock prevents other transactions from issuing whole table locks on the child table, but does not block DML on either the parent or the child table. Only the rows relating to the parent primary key are locked in the child table.
0コメント