How bitmap indexes and B-tree indexes are selected in use _oracle

Now we know how the optimizer reacts to these technologies, clearly stating the best applications of the bitmap index and the B-tree index.
In the GENDER column, place a bitmap index, create another bitmap index on the SAL column, and then execute some queries. On these columns, the query is rerun with the B-tree index.
From the Test_normal table, inquire about the salary of male employees as follows:
1000
1500
2000
2500
3000
3500
4000
4500
So:
Sql> SELECT * from Test_normal
2 where Sal in (1000,1500,2000,2500,3000,3500,4000,4500,5000) and gender= ' M ';
444 rows have been selected.

Execution plan
----------------------------------------------------------
Plan Hash value:4115571900
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
--------------------------------------------------------------------------------------------------
| 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 | | | |
--------------------------------------------------------------------------------------------------
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 ')

Statistical information
----------------------------------------------------------
0 Recursive calls
0 db Block gets
6280 consistent gets
0 physical Reads
0 Redo Size
25451 Bytes sent via sql*net to client
839 bytes received via sql*net from client
Sql*net roundtrips To/from Client
0 Sorts (memory)
0 Sorts (disk)
444 rows processed
Sql>
This is a typical data warehouse query, not to be performed on OLTP (on-line Transaction processing, online transaction processing system) systems. The following is the result of the bitmap index:
Queries that b-tree the index:
Sql> SELECT * from Test_normal
2 where Sal in (1000,1500,2000,2500,3000,3500,4000,4500,5000) and gender= ' M ';

444 rows have been selected.

Execution plan
----------------------------------------------------------
Plan Hash value:654360527
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
-------------------------------------------------------------------------------------------------
| 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 |
-------------------------------------------------------------------------------------------------
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 ')

Statistical information
----------------------------------------------------------
0 Recursive calls
0 db Block gets
6854 consistent gets
0 physical Reads
0 Redo Size
25451 Bytes sent via sql*net to client
839 bytes received via sql*net from client
Sql*net roundtrips To/from Client
0 Sorts (memory)
0 Sorts (disk)
444 rows processed
Sql>
For the B-tree index, the optimizer selects a full table scan and, in the case of an bitmap index, uses the index. Performance can be inferred from IO.
Generally, bitmap indexes are most appropriate for DSS, regardless of cardinality, for the following reasons:

For bitmap indexes, the optimizer may be efficiently low on queries that contain and, or or XOR. (Oracle supports dynamic b-tree to bitmap conversions, but not very efficient.)
For bitmap indexes, the optimizer responds to queries when the query or count is null. The null value is also bitmap indexed (this differs from the B-tree index).

More importantly, the bitmap index of the DSS system supports the Ad hoc query, while the B-tree index does not. More specifically, if you have a table with 50 columns, and users frequently query for 10 of them--or all 10 columns, or a column--it can be difficult to create a B-tree index. If you create 10 bitmap indexes on all of these columns, all queries will be responded to by those indexes, whether they are queries on 10 columns, 4, 6 columns, or just one column. The And_equal optimizer hints to provide this functionality for B-tree indexes, but not more than 5 indexes. The bitmap index has no such limit.

By contrast, the B-tree index is well-suited for OLTP applications, where user queries are more conventional (and can be adjusted before deployment), and are relatively infrequent and are not very frequent during the peak hours of a flight. Because OLTP systems are often updated and deleted, in this case, the bitmap index can cause a serious lock problem.

The data here is very clear. Two index destinations are the same: return results as quickly as possible. But choosing which one to use depends entirely on the type of application, not the base level.