Oracle Bitmap Index Techniques
And again, good evening!
We are launching the second stream of our new course, “Relational DBMS” , which we slightly 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 indices. 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.
The 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 the 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:
Oracle uses a specialized optimization method called bitmap indexing to work with this query. In this method, each Row-ID list (RID for short) is formed separately using bitmaps, and a special merge 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!
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:
Troubleshooting Oracle Oracle Bitmap Indexes
The most common problems with implementing bitmap indexes include the following:
We are waiting for questions and comments here or visit us at the new open lesson .
We are launching the second stream of our new course, “Relational DBMS” , which we slightly 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 indices. 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.
The 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 the 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; Oracle uses a specialized optimization method called bitmap indexing to work with this query. In this method, each Row-ID list (RID for short) is formed separately using bitmaps, and a special merge 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 for low power bitmap indexes are very fast;
- 8 - 100 different key values - With an increase in the number of different values, performance will decrease proportionally;
- 100 - 10000 different values - With more than 100 different values, the bitmap indexes become huge and 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:
- The table column has low power — for the BLACK manual, consider a bitmap for any index with less than 100 different values:
select region, count(*) from sales group by region; - 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;
- Multiple Columns - Your SQL queries refer to several low-power values in their where clauses. Oracle's SQL optimizer based on cost (CBO for short) will scream if you have bitmap indexes.
Troubleshooting Oracle 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 creation:
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 visit us at the new open lesson .
Source: https://habr.com/ru/post/433090/
All Articles