How to Select bitmap indexes and B-tree indexes

Now we know how the optimizer reacts to these technologies and clearly shows the best applications of bitmap indexes and B-tree indexes.

Now we know how the optimizer reacts to these technologies and clearly shows the best applications of bitmap indexes and B-tree indexes.

Now we know how the optimizer reacts to these technologies and clearly shows the best applications of bitmap indexes and B-tree indexes.
Place a bitmap index in the GENDER column, create another bitmap index in the SAL column, and then execute some queries. In these columns, use the B-tree index to re-execute the query.
From the TEST_NORMAL table, query the following male employees:
1000
1500
2000
2500
3000
3500
4000
4500
Therefore:
SQL> select * from test_normal
2 where sal in (5000,) and GENDER = 'M ';
Row 444 has been selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 4115571900
Bytes --------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
Bytes --------------------------------------------------------------------------------------------------
| 0 | select statement | 1 | 39 | 1 (0) | 00:00:01 |
| * 1 | table access by index rowid | TEST_NORMAL | 1 | 39 | 1 (0) | 00:00:01 |
| 2 | bitmap conversion to rowids |
| * 3 | bitmap index single value | NORMAL_GENDER_BMX |
Bytes --------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id ):
---------------------------------------------------
1-filter ("SAL" = 1000 OR "SAL" = 1500 OR "SAL" = 2000 OR "SAL" = 2500 OR "SAL" = 3000
OR
"SAL" = 3500 OR "SAL" = 4000 OR "SAL" = 4500 OR "SAL" = 5000)
3-access ("GENDER" = 'M ')

Statistics
----------------------------------------------------------
0 recursive cballs
0 db block gets
6280 consistent gets
0 physical reads
0 redo size
25451 bytes sent via SQL * Net to client
839 bytes encoded ed via SQL * Net from client
31 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
444 rows processed
SQL>
This is a typical data warehouse query and should not be executed On the OLTP (On-Line Transaction Processing, online Transaction Processing system) system. The following is the result of the bitmap index:
B-tree index query:
SQL> select * from test_normal
2 where sal in (5000,) and GENDER = 'M ';

Row 444 has been selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 654360527
Bytes -------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
Bytes -------------------------------------------------------------------------------------------------
| 0 | select statement | 1 | 39 | 2 (0) | 00:00:01 |
| * 1 | table access by index rowid | TEST_NORMAL | 1 | 39 | 2 (0) | 00:00:01 |
| * 2 | index range scan | NORMAL_GENDER_IDX | 1 | 2 (0) | 00:00:01 |
Bytes -------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id ):
---------------------------------------------------
1-filter ("SAL" = 1000 OR "SAL" = 1500 OR "SAL" = 2000 OR "SAL" = 2500 OR "SAL" = 3000
OR
"SAL" = 3500 OR "SAL" = 4000 OR "SAL" = 4500 OR "SAL" = 5000)
2-access ("GENDER" = 'M ')

Statistics
----------------------------------------------------------
0 recursive cballs
0 db block gets
6854 consistent gets
0 physical reads
0 redo size
25451 bytes sent via SQL * Net to client
839 bytes encoded ed via SQL * Net from client
31 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
444 rows processed
SQL>
For B-tree indexes, the optimizer selects full table scan, while bitmap indexes use indexes. I/O can be used to deduce the performance.
Generally, bitmap indexes are most suitable for DSS, regardless of the base number. The reasons are as follows:

For bitmap indexes, the Optimizer may be efficient AND inefficient at queries containing AND, OR, or xor. (Oracle supports dynamic B-tree to bitmap conversion, but the efficiency is not very high.
For bitmap indexes, when the query or count is null, the optimizer will respond to the query. The null value is also indexed by the bitmap index (which is different from the B-tree index ).

More importantly, the bitmap index of the DSS system supports ad hoc queries, while the B-tree index does not. More specifically, if you have a table with 50 columns and you frequently Query 10 of them-or a combination of all 10 columns, or a column -- it is very difficult to create a B-tree index. If you create 10 bitmap indexes on all these columns, all the queries will be responded to by these indexes, whether in 10 columns or 4 or 6 columns, or only one column. The AND_EQUAL optimizer prompts that this function is provided for B-tree indexes, but cannot exceed 5 indexes. Bitmap indexes do not have this restriction.

In contrast, the B-tree index is very suitable for OLTP applications. Such system user queries are more common (can be adjusted before deployment), which is not frequent compared with ad hoc queries, run during peak hours. Because OLTP systems are often updated and deleted, bitmap indexes can cause a serious lock problem in this case.

The data here is obvious. The two indexes have the same target: return results as quickly as possible. However, selecting which one to use depends on the application type rather than the base level.