How do I select the bitmap index and ES1en ES2en index in use

Now that we know how the optimizer responds to these techniques, we clearly show the best use of the bitmap index and B-ES3en indexes, respectively.
An bitmap index is appropriately placed in the GENDER column, another bitmap index is created on the SAL column, and then 1 query is executed. On these columns, the query is re-executed using the ES8en-ES9en index.
From table TEST_NORMAL, the salary of male employees is inquired as follows:
1000
1500
2000
2500
3000
3500
4000
4500
Therefore:
SQL > select * from test_normal
2 where sal in (1000,1500,2000,2500,3000,3500,4000,4500,5000) and GENDER='M';
Line 444 has been selected.

The 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')

statistics
----------------------------------------------------------
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
31 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
444 rows processed
SQL >
This is a typical data warehouse query that is not performed on the OLTP (On-ES103en Transaction Processing, online transaction processing system) system. Here are the results of the bitmap index:
The query of B-tree index:
SQL > select * from test_normal
2 where sal in (1000,1500,2000,2500,3000,3500,4000,4500,5000) and GENDER='M';

Line 444 has been selected.

The 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')

statistics
----------------------------------------------------------
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
31 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
444 rows processed
SQL >
For the ES180en-tree index, the optimizer chooses a full table scan, while in the case of the bitmap index, the index is used. Performance can be inferred from IO.
1 Generally, the bitmap index works best for DSS, regardless of the cardinality, for the following reasons:

For the bitmap index, the optimizer may be less efficient than queries containing AND, OR, or XOR. Oracle supports dynamic B-ES195en to bitmap conversion, but it is not very efficient.
For the bitmap index, the optimizer responds to the query when null is queried or counted. The null value is also indexed by the bitmap index (this is different from the B-ES203en index).

More importantly, the bitmap index of the DSS system supports the ad hoc query, while the ES210en-ES211en index does not. More specifically, if you have 1 table with 50 columns and the user frequently queries 10 of them -- or all combinations of 10 columns, or 1 column -- it will be difficult to create an ES212en-ES213en index. If you create 10 bitmap indexes on all of these columns, all queries will be responded to by those indexes, whether on 10 columns, 4, 6 columns, or just 1 column. The AND_EQUAL optimizer prompts to provide this functionality for the ES217en-ES218en index, but not more than five indexes. The bitmap index does not have this limitation.

In contrast, the ES222en-ES223en index is a good fit for OLTP applications, where system user queries are more routine (they can be adjusted prior to deployment) and less frequent than ad hoc queries, which are executed during peak flight hours. Because the OLTP system is constantly updated and deleted, the bitmap index can cause a serious locking problem in this case.

The data here is pretty obvious. Both indexes have the same goal: return results as quickly as possible. But the choice of which to use depends entirely on the type of application, not the level of cardinality.