There are a few different types of indexes. As you might imagine, the same category or type of index cannot really meet all of the needs. The very first one is B-tree which is balanced tree. These are the most common types of indexes that exist inside of Oracle. It's essentially an ordered list of values that are divided into ranges. If I am looking for the letter A, for example, if I'm looking for a last name, Adams, it's going to go through the rows of data that are indexed only, looking for where A is. Then within the A, it will have organized A-D somewhere, and then it will be going to A-D-A, and then it will go over to A-D-A-M, and then over to A-D-A-M-S. This organized structure, it will also exist for B's and C's and every piece of information that is inside of that table. It only organizes data in a balanced tree for rows that are there. It's not going to organize this for any element or any piece of data that doesn't exist. It's not going to attempt to sort the B's and the C's and the D's and the E's and so on if the data for those names isn't there. Balanced tree is the first kind or type of index. There's also bitmap index. Bitmap indexes are designed for data warehousing environments which essentially means that one row in an index points to many rows inside of the index itself. Let me explain what I'm talking about. If I have a summary of data, and what that means is I'm going to say, what is a total for a given quarter? If I say quarter 1, you know that that represents from January through March. If I want a total for a given quarter, that is one piece of data that I might be looking for but the first quarter represents many rows of data. A bitmap index would point to quarter 1 but then when quarter 1 is searched, it would point to many rows of data that encompass the first quarter. In this case, January to March. In a bitmap index, a single row in an index points to multiple rows that make up the valid search of that index which is different from a balanced tree index, where one item in the index points to one row and exactly one row inside of the table structure. Then we have a third type of index called the application domain. The application domain index is specific to an application and is customized to store complex data types such as documents and video clips. Application domain indices are very different than the first two options. They're not like bitmaps and they're certainly not like balanced trees. They're meant for perhaps, geolocation applications to store spatial data which is very different than storing just text data and such. Application domain indexes. Then there are function-based indexes. These are useful when you use functions in the where clause. You might use a function, for example, to compute a total of some kind. Perhaps a total is as simple as quantity times the price. You might be searching for a total as in quantity times the price. This total, if you will, utilizes a function to compute the value if your index is using a function. For the purposes of finding a value, then you would use a function-based index. Finally, we have IOTs or index-organized table. I like to call them IOTs. These are a variation of the balance tree indexes, but as opposed to balanced tree indexes, these will store rows where they fit. What that means is if you have rows inside of the index that are organized by A, B, C, and D for example and a new piece of data comes in that has a letter B in it, It will basically make space for the letter B right in between the letters A and B. It will place the data exactly where it belongs. This goes true or holds true for numbers as well. If you have items that are listed as 100 and 200 and 300 in an index-organized table. If I get an item that's numbered 143, it will place it right in between 100 and 200. That's not the same as balanced tree. Balanced tree will say, if 143 came after 300, it will place it down here. That's the difference between IOTs, index-organized tables, and B-tree. IOTs will place things in the grouping where they belong so that searchability is improved. Those are five different kinds of indexes. Balanced tree, bitmap, application domain, function-based, and index-organized tables.
