What types of indexes in what situation it is better to use in Oracle DBMS?

Question

Oracle

What types of indexes are there in Oracle 11g and how do they work and when is it preferable to use which?

Answer 1

In oracle there are b-tree indexes, bitmap indexes, and more specific ones (fulltext search, mine, an index on structured xml, and maybe something else).
b-tree is a balanced tree, usually no more than 5 levels high, with a logarithmic access rate. Such an index is good for looking up a specific row in a large table, or fetching a small amount of data with good selectivity (not too many rows in the table with the value you are looking for) from the table. Scope - OLTP load and DWH is also useful.
bitmap-index is a table that has the desired values ​​in the column headers, taken from the indexed columns of the indexed table, and each row represents a bitmask: if there is a value in the indexed table, then 1, otherwise 0. Each row in the bitmap table corresponds to one row in the indexed table. Such an index is used primarily in DWH to search for conditions like (t.col1 = 'A' or t.col2 = 'B') and t.col3 = 'C'. From such a condition, it is easy to assemble a bitmask and walk through the bitmap index.
You can also notice that if we searched the b-tree for an index with the or condition, we would have to search twice. At the same time, b-tree allows you to search by condition with the inequality t.col1 > 123 and t.col2 < 234, because the tree is sorted.