Oracle Bitmap Index Techniques

And again, good evening!

We are launching the second stream of our new course, “Relational DBMS” , which we just finished following the results of the first run: additional classes in MySQL and Postgres clusters turned out to be in demand by docker and also various “file improvements”. So wait for open lessons (in which part of old topics have been carried out) and interesting materials. Today we delve into the techniques of Oracle.

Go.

Oracle Bitmap-indices are very different from standard B-tree indexes. In bitmap structures, a two-dimensional array is created with a column for each row in the indexed table. Each column represents a separate value in a bitmap-index. This two-dimensional array shows each index value multiplied by the number of rows in this table.

Oracle unpacks bitmaps (at the speed of retrieving the string) into the RAM data buffer for fast scanning for matching values. These matching values ​​are passed to Oracle as a Row-ID list, and the Row-ID values ​​can directly access the required information.



A particular advantage of bitmap indexing is manifested when one table includes several bitmap indexes. The power of each column may be low. Creating multiple bitmap indexes provides a very powerful approach for quickly responding to complex SQL queries.



Using the bitmap-join methodology, Oracle reduces response time to less than a second when working with several columns with a small number of elements.

Also note the important notes about the maximum values ​​of the Oracle bitmap-index .

For example, imagine that there is a database of cars with a large number of low-power columns: car_color, car_make, car_model, and car_year. Each column contains less than 100 different values, and the b-tree index would be completely useless in such a database of 20 million cars.

However, merging these indexes into a query can provide a high response time much faster than the traditional method of reading each of the 20 million rows in the base table. For example, suppose we want to find the old blue Toyota Corolla, produced in 1981:

select
   license_plat_nbr
from
   vehicle
where
   color = "blue"
and
   make = "toyota"
and
   year = 1981;

To work with this query, Oracle uses a specialized optimization method called bitmap indexing. In this method, each Row-ID list (RID for short) is formed separately using bitmaps, and a special merging procedure is used to compare RID lists and search for overlapping values.

As the number of different values ​​grows, the size of the bitmap increases exponentially. So an index of 100 values ​​can work 1000 times faster than a bitmap index of 1000 different column values.

It is worth remembering that bitmap-indexes are only suitable for static tables and materialized views, which are updated at night and reassembled after batch loading of rows. If several DMLs per second occur in your table, BE CAREFUL when implementing bitmap indexes!

• 1 - 7 different key values ​​- Requests to low capacity bitmap indexes are very fast;
• 8 - 100 different key values ​​- With an increase in the number of different values, performance is proportionally reduced;
• 100 - 10,000 different values ​​- With more than 100 different values, the bitmap indexes become huge and the SQL performance drops rapidly;
• More than 10,000 different key values ​​— at this stage, performance is ten times lower than with an index with 100 different values.

Oracle Bitmap-indices are a very powerful feature of Oracle, but there are also pitfalls!

You will want to use the bitmap index in the following cases:

1. The table column has low capacity — for the BLACK manual, consider a bitmap for any index with less than 100 different values:
   
   

   select region, count(*) from sales group by region;

2. LOW DML tables - use insert / update / delete should be low. Updating bitmap indexes requires a lot of resources, so they are better suited for read-only tables and batch-updated tables every night;
3. Multiple columns - your SQL queries refer to several fields with low cardinality in the Where clause. Having your bitmap-indexes will facilitate the work of the Oracle optimizer, which makes an estimate based on cost (in short - CBO (Cost-Based Optimizer)).

Troubleshooting Oracle Bitmap Indexes

The most common problems with implementing bitmap indexes include the following:

• Small table - CBO may require a full scan of the table if it is too small!
• Bad stats - Make sure you analyze the bitmap with dbms_stats immediately after creating:

CREATE BITMAP INDEX 
emp_bitmap_idx
ON index_demo (gender);
exec dbms_stats.gather_index_stats(OWNNAME=>'SCOTT', INDNAME=>'EMP_BITMAP_IDX');

• Testing with a hint - to use your new bitmap-index, use the hint INDEX Oracle:

select /*+ index(emp emp_bitmap_idx) */ 
   count(*)
from 
   emp, dept
where 
   emp.deptno = dept.deptno;

We are waiting for questions and comments here or come to us for a new open lesson .