How does indexes work in oracle

To increase the performance of such queries, you might create a composite index putting the most selective column first; that is, the column with the most values:.

Composite indexes speed up queries that use the leading portion of the index. The database can use indexes more effectively when it has statistical information about the tables involved in the queries. When you drop an index, all extents of the index's segment are returned to the containing tablespace and become available for other objects in the tablespace.

For example, the following statement drops a specific named index:. When using indexes in an application, you might need to request that the DBA grant privileges or make changes to initialization parameters. To create a new index, you must own, or have the INDEX object privilege for, the corresponding table. You can create an index for a table to improve the performance of queries issued against the corresponding table.

You can also create an index for a cluster. You can create a composite index on multiple columns up to a maximum of 32 columns. 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, time-series, audio, or video data. Oracle 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. One of the best article I've read on indexes. Appreciate your work. Please keep posting such good articles. I don't think I have ever read a better explanation of indexes before. So simple to understand. It took you 5 years to understand indexes I wish I had read this 6 years ago :. We really need more simple to understand articles like this so that there are more proficient DBAs in the world and we're not just guessing when it comes to things like creating indexes, tuning, etc.

Common, real-life examples help a ton and stick in the brain better. Keep up the good work! I have gone through your note on Index. I am having few question understanding the index creation pattern at the block level. How exactly and what exactly? And then branch blocks are categorized and created under the ind1 segment and then root block? Before that how exactly these branch blocks categorized what I mean by categorization is grouping of all blocks from alphabets A to E under one branch block and then from E to H under another branch block - how exactly is this grouping done is it following any specific algorithm?

And then how exactly does this root block created with in this IND1 segments? Now again when looking for a row, how exactly optimizer find the root block and traverse to the leaf block? Your information or advice is very helpful. Might be I'm missing some thing so that I am unable to get to know answers for these questions. Kindly pull some of your valuable time to let me understand the indexes in detail. There are a lot of questions in your post, and I'm not sure I understand all of them, but I'll try:.

Quote: now if I am creating an index 'ind1' on 'tab1'. Quote: how exactly and what exactly the blocks under the ind1 segment consists of. When you create an index on a pre-populated table, Oracle performs a full scan of the table to retrieve indexed column values and ROWIDs from every row.

So far, we have not created any index blocks. The sorted list of index values and ROWIDs is then broken into block-size chunks and placed in index leaf blocks. I don't know how Oracle allocates these leaf blocks to branch blocks. But considering the entire list might not fit into memory, I could take an educated guess As each leaf block is created, its address and high-value are placed in a branch block.

When the branch block fills up, it places the address and high-value of the branch block in a grand-parent level branch block and allocates a new branch block.

This process repeats until the grand-parent level branch block fills and then it creates a great-grand-parent level branch block, etc. When you are done, all of the leaf blocks except probably the last one will be full, and all of the branch blocks at a given level will be full except probably the last one. As a result, INSERTs after an index rebuild can cause a great deal of index block splitting to make room see main article. So Oracle doesn't decide where the start and end-points of an index block will be, it just keeps adding rows until the block is full The parent of that branch block contains addresses of a bunch of contiguous branch blocks, plus the high-value of the LAST branch block it points to.

And so on up to the root. Quote: And then how exactly does this root block created with in this IND1 segments? The root block is created when the index is first created and I'm going from memory here never moves. With normal branch blocks, when they fill up they split in two and the address of the new block is added to the parent. If there is no room in that parent, it splits in two and the address of the new block is added to its parent.

The addresses of these two new blocks are then placed in the empty root. In this way, the root never moves. How does the optimizer find the root block? Its address is in the data dictionary, and since it never moves, the data dictionary never needs to update it.

Indexes - simplified, dimystified! Never seen better expalination than this. Good work. Thanks a lot. Keep it up. I'm trying to run your code samples in order to better understand your article. However, it seems to me that none of the tables in your code exhibits exist in my database. Are these sample predefined Oracle tables? Is there any script allowing to create them? No, they were just tables to which I happened to have access on the database I was using at the time. You could recreate the scenarios yourself using tables in the sample schemas with similar index structure.

It's amazing how something written in is still so valuble to novices like myself. We keep coming back here :. Wow, you know not only one heck of a lot about Oracle I have 6 years of Oracle experience, and so far I thought there is quite a lot I know, but now that I read your article, I know that I know so little , you also write and explain just wonderfully!

If an Index is created on column that contain numbers from 1 to with number 5 missing. Let us assume each block contains 10 rows so there will be 1 root block level 0 in the next level 10 blocks level 1 and leaf nodes level 2.

Since indexes are stored in a sorted fashion we can say that the first leaf block at level 2 say block 'A' contains 1 to 11 numbers since 5 is missing. If we insert a new record with number 5 then it should be inserted into block 'A'. How does the B-tree split and restructure now?? Now A1 contains numbers 1 to 10 and A2 contains only number 11 this will lead to wastage of the block as the next numbers are in the second block at level 2. So an simple new insert causes 10 inserts and 10 deletes in the level 2 blocks??

Using your example, an index leaf block A is full. The root node points us to the left branch node, because it contains all values up to This branch node then points us to the left most leaf node, because the left most lead node contains all values up to Usually this is a very fast and efficient way to get to your data, however as you can see this can be a very expensive process, it took us 4 IO's to get just one record out of the table 3 index IO's and 1 table IO.

Hence, indexes are not always the best way to get at table data. You will really need to make a study of Oracle Performance Tuning to understand when indexes are good and when they are bad. Since this book is about being a DBA and managing your database, we will leave that discussion for another day. Oracle can use other kinds of indexes, such as index organized tables, function based indexes and indexed clusters. These are slightly more advanced topics and best saved for another book.

Also, Oracle offers advanced functionality with indexes such as partitioning and key compression. In this book, we are just trying to get you through the basics first. Once you are through the basics and comfortable with them, then you can move onto these index options later. As a DBA we need to manage our indexes.

This includes knowing who owns the index, what tablespace the index is in and what columns the index is made of. The following data dictionary views are used for these purposes:. For example, here is a simple query to show its tables and freelists:. In this way, the database scans a set of data smaller than the employees table, which contains more columns than are included in the query, and avoids sorting the data. 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:. A row containing all nulls must not appear in the query result set. For this result to be guaranteed, at least one column in the index must have either:.

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:. 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. The database commonly uses an index range scan to access selective data.

The selectivity is the percentage of rows in the table that the query selects, with 0 meaning no rows and 1 meaning all rows. A predicate becomes more selective as the value approaches 0 and less selective or more unselective as the value approaches 1. For example, a user queries employees whose last names begin with A. For example, two employees are named Austin, so two rowids are associated with the key Austin.

An index range scan can be bounded on both sides, as in a query for departments with IDs between 10 and 40, or bounded on only one side, as in a query for IDs over 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.