Bitmap join Index

2.1 use Bitmap indexes after clear requirements
When creating a bitmap connection index, it is the connection between two or more tables, and the results of the connection are stored in the index itself; through the early connection and storage results, when querying, you can obtain data by scanning the index (avoiding two or more full table scans). Of course, you must be clear before creating such an index; observe the following personnel information table (th04) and Personnel Unit relationship information table (tbbsj)
Personnel info table (th04)
Row ID name gender idcard homeaddr jobno Birthdate
1 789524 Zhang Weinan 429005198911261805 Crystal Cave 1 1300440 05-aug-79
2 564895 Li Si male 429005198911296178 Crystal Cave 2 1000209 14-aug-79
...
....
Personnel Unit relationship information table (tbbsj)
Row ID (unit ID) idcard (Personnel ID card) dname (unit name) joindate (Entry unit time) srzw (position) gzzt (working status) tsgx (special contribution)
1 785652 429005198911261805 Space Construction Co., Ltd. 21:51:33 assistant general manager no
2 5689556 429005198911296178 flywheel Industrial Co., Ltd. 21:51:42 General Manager Xiao Mi Liang
..
.....
The data volume of known personnel information is 1 million, and the data volume of Personnel Unit link information is 1106642. When a common index is created, the following information is obtained: 429005198911261805 of the Personnel Unit information of the person idcard:
SQL> select T1. *, T2. * From th04 T1, tbbsj T2
2 Where t1.idcard = t2.idcard and t1.idcard = '000000 ';
Bytes ----------------------------------------------------------------------------------------------
| ID | operation | Name | rows | bytes | cost (% CPU) | time | pstart | pstop |
Bytes ----------------------------------------------------------------------------------------------
| 0 | SELECT statement | 1 | 253 | 10959 (1) | 00:02:12 |
| * 1 | hash join | 1 | 253 | 10959 (1) | 00:02:12 |
| 2 | partition range all | 1 | 65 | 2661 (1) | 00:00:32 | 1 | 6 |
| * 3 | table access full | th04 | 1 | 65 | 2661 (1) | 00:00:32 | 1 | 6 |
| * 4 | table access full | tbbsj | 1 | 188 | 8297 (1) | 00:01:40 |
Bytes ----------------------------------------------------------------------------------------------
Predicate information (identified by Operation ID ):
---------------------------------------------------
1-access ("T1". "idcard" = "T2". "idcard ")
3-filter ("T1". "idcard" = '000000 ')
4-filter ("T2". "idcard" = '000000 ')
Statistics
----------------------------------------------------------
1 recursive cballs
0 dB block gets
40165 consistent gets
40137 physical reads
The analysis results show that the full table scan is performed for the two large tables to obtain the organization information with an idcard of 429005198911261805, because the requirements for obtaining the organization relationship information are clear, create a bitmap connection index for it:
SQL> Create bitmap index ind_th04uniontbbsj on th04 (t1.idcard)
2 from th04 T1, tbbsj T2
3 where t1.idcard = t2.idcard
4 tablespace tbs03
5 * local;
After creating a bitmap connection index, query again:
SQL> select T1. *, T2. * From th04 T1, tbbsj T2
2 * Where t1.idcard = t2.idcard and t1.idcard = '000000 ';
Bytes -----------------------------------------------------------------------------------------------------------------
| ID | operation | Name | rows | bytes | cost (% CPU) | time | pstart | pstop |
Bytes -----------------------------------------------------------------------------------------------------------------
| 0 | SELECT statement | 1 | 253 | 5 (0) | 00:00:01 |
| 1 | nested loops | 1 | 253 | 5 (0) | 00:00:01 |
| 2 | table access by index rowid | tbbsj | 1 | 188 | 3 (0) | 00:00:01 |
| * 3 | index unique scan | pk_idcard | 1 | 2 (0) | 00:00:01 |
| 4 | table access by global index rowid | th04 | 1 | 65 | 2 (0) | 00:00:01 | rowid |
| * 5 | index unique scan | cs_idcard | 1 | 1 (0) | 00:00:01 |
Bytes -----------------------------------------------------------------------------------------------------------------
Predicate information (identified by Operation ID ):
---------------------------------------------------
3-access ("T2". "idcard" = '000000 ')
5-access ("T1". "idcard" = '000000 ')
Statistics
----------------------------------------------------------
0 recursive cballs
0 dB block gets
3 consistent gets
0 physical reads
For bitmap join indexes, it stores the rowid in the dominant and associated tables and the column values in the dominant columns in the index. When querying data, you can scan the index to improve the query efficiency:
Personnel info table (th04) rowid Personnel Unit relationship info table (tbbsj) rowid idcard
Aaasdeaagaab6w6aay aaasfxaaiaaamcqaae 440621197102274116
Aaasdeaagaab6z5aaf aaasfxaaiaaamcgaax 440621197311244423
Aaasdeaagaab6zcaal aaasfxaaiaaamcpaaw 440621197405202427

2.2 considerations for creating a bitmap connection index:
2.2.1 when creating a bitmap join index, the correlated condition column in The WHERE clause must be a primary key or a unique constraint (an error will be reported if the condition is not met: Missing primary key or unique constraint on dimension );
2.2.2 when specifying an index column, if both tables have the same column, you must use the table name. specifies the column name or Alias. specifies the method of the column name (an error is reported if the condition is not met: ORA-00918: column ambiguously defined );
2.2.3 creating Bitmap indexes is equally inefficient in DML operations. analyze the structure and data DML operation rate carefully before creating the indexes;
2.2.4 when updating table data, the dominant table and associated table will also be locked;