B*Tree indexes are by far the most common and well-understood indexing structures in the Oracle database. They are an excellent general-purpose indexing mechanism. They provide very scalable access times, returning data from a 1000-row index in about the same amount of time as a 100,000-row index structure.
When to index and what columns to index are things you need to pay attention to in your design. An index does not always mean faster access; in fact, you will find that indexes will decrease performance in many cases if Oracle uses them. It is purely a function of how large of a percentage of the table you will need to access via the index and how the data happens to be laid out. If you can use the index to answer the question, accessing a large percentage of the rows makes sense, since you are avoiding the extra scattered I/O to read the table. If you use the index to access the table, you will need to ensure you are processing a small percentage of the total table.
You should consider the design and implementation of indexes during the design of your application, not as an afterthought (as I so often see). With careful planning and due consideration of how you are going to access the data, the indexes you need will be apparent in most all cases.
Bitmap Indexes
Bitmap indexes are currently available with the Oracle Enterprise and Personal Editions, but not the Standard Edition. Bitmap indexes are designed for data warehousing/ad hoc query environments where the full set of queries that may be asked of the data is not totally known at system implementation time. They are specifically not designed for OLTP systems or systems where data is frequently updated by many concurrent sessions.
Bitmap indexes are structures that store pointers to many rows with a single index key entry, as compared to a BTree structure where there is parity between the index keys and the rows in a table. In a bitmap index, there will be a very small number of index entries, each of which points to many rows. In a conventional BTree, one index entry points to a single row.
Let’s say we are creating a bitmap index on the JOB column in the EMP table as follows: $ sqlplus eoda/foo@PDB1 SQL> create BITMAP index job_idx on emp(job); Index created.
Oracle will store something like what is shown in Table 11-6 in the index. Table 11-6. A Representation of How Oracle Would Store the JOB-IDX Bitmap Index
Table 11-6 shows that rows 8, 10, and 13 have the value ANALYST, whereas rows 4, 6, and 7 have the value MANAGER. It also shows us that no rows are null (bitmap indexes store null entries; the lack of a null entry in the index implies there are no null rows). If we wanted to count the rows that have the value MANAGER, the bitmap index would do this very rapidly. If we wanted to find all the rows such that the JOB was CLERK or MANAGER, we could simply combine their bitmaps from the index, as shown in Table 11-7.
Table 11-7. Representation of a Bitwise OR
Table 11-7 rapidly shows us that rows 1, 4, 6, 7, 11, 12, and 14 satisfy our criteria. The bitmap Oracle stores with each key value is set up so that each position represents a rowid in the underlying table, if we need to actually retrieve the row for further processing. Queries such as the following
SQL> select count(*) from emp where job = ‘CLERK’ or job = ‘MANAGER’; will be answered directly from the bitmap index. A query such as this SQL> select * from emp where job = ‘CLERK’ or job = ‘MANAGER’;
on the other hand, will need to get to the table. Here, Oracle will apply a function to turn the fact that the i’th bit is on in a bitmap, into a rowid that can be used to access the table.
Leave a Reply