Shulou(Shulou.com)06/01 Report--

1. The outline of bitmap: 1. Define what is a bitmap index: an index represented by a bitmap. Oracle establishes a bitmap for each key value of the column at the bottom of the selection degree. Each bit in the bitmap may correspond to multiple columns. The bitmap bit equals 1 indicates that a specific row contains the key value represented by this bitmap. two。 Query, because the index is a bitmap, so most of the time bitmaps in these indexes can be operated on-(and and or), which is significantly faster than b-tree (in some cases). Because the bitmap index can store null, it can be counted directly through the bitmap index (definitely accurate). The points mentioned later are directly related to the way bitmaps are calculated. 3. Advantages of bitmaps (mainly for dw): reduce the corresponding time for ad hoc queries compared with other types of indexes, really save index data space, even on very poor hardware, there may be dramatic performance improvements and efficient parallel DML and LOAD operations. Indexes are generated more efficiently, first of all, without sorting, and secondly, with less space (index space). You can count directly through the bitmap index.

4. The disadvantage of bitmap index (other data) is not to be said to be defect.

It is not suitable to select a column with a low degree of selection if there are more frequent operations such as insert,update, which may lead to poor performance, because updating the index uses row locks (which may lock multiple rows) rather than exclusive locks. It may overflow and it is difficult for the index data block to drop the entire index value, which leads to inefficiency. Second, mainly understand the following bitmap schematic

It should be noted that this is only a schematic diagram, in fact, the number of bits of each bitmap is not exactly equal to the number of records, but will be decomposed according to the situation, otherwise, the bitmap is too large for the majority of data. Third, the bitmap principle parses the bitmap storage result, relatively speaking, is a little more complicated. Bitmap does not store the rowid,rowid in the header of each bitmap, but stores the revelation location and the end location of the rowid. ORACLE calculates the corresponding ROWID through its own internal calculation. Each bit in the bitmap records whether there is a value. For example, the records of the table are stored as follows:

The corresponding bitmap of row-value male female female male is stored as follows:

The revelation position and end position of rowid the revelation position and end position of rowid male female 10010 0110 can be seen from this, the storage space is greatly saved, and the benefit is that the scanning BLOCK is also greatly reduced.

If you look for data whose gender is male, ORACLE will only search for the column MALE, then the record of 1, and return it.

If you make a query such as OR,AND against the BITMAP field itself, then ORACLE will make a judgment inside the BITMAP index, find out what matches the result, then calculate the ROWID, and finally give the corresponding VALUE, as shown below:

Bitmap join index bitmap join index, which is characterized by storing the JOIN results of multiple tables in an index, and then storing them in the form of BITMAP. This is a significant improvement in the efficiency of multi-table join like DW. Do the test with three tables of join as follows. The original SQL looks like this: test@DB > select wt_cust.company_name,wt_cust.gmt_create

2 from wt_cust,wt_CUST_EXT, wt_CUST_BOOK 3 where wt_cust.id=wt_CUST_EXT.Cust_Id 4 and wt_CUST_BOOK.Cust_Id=wt_cust.id

58 rows selected.

Elapsed: 00:00:00.01

Execution Plan-

-| Id | Operation | Name | Rows | Bytes | Cost (% CPU) |-- -| 0 | SELECT STATEMENT | | 54 | 2484 | 179 (0) | | 1 | NESTED LOOPS | | | 54 | 2484 | 179 (0) | | 2 | NESTED LOOPS | 7257 | 7257 | 3 | INDEX FULL SCAN | wt_CUST_BOOK_UK | 177 | 1062 | | 1 (0) | | 4 | TABLE ACCESS BY INDEX ROWID | wt_CUST | 1 | 35 | 2 (0) | | * 5 | INDEX UNIQUE SCAN | wt_CUST_PK | 1 | | 1 (0) | | * 6 | INDEX RANGE SCAN | | wt_CUST_EXT_CID_IND | 1 | 5 | 0 (0) |-|

A 3-meter join is very inefficient. This can be avoided if we create a BITMAP JOIN INDEX: test@DB > CREATE BITMAP INDEX cust_wt_test 2 ON wt_cust (wt_cust.company_name) 3 FROM wt_cust,wt_CUST_EXT, wt_CUST_BOOK 4 WHERE wt_cust.id=wt_CUST_EXT.Cust_Id 5 and wt_CUST_BOOK.Cust_Id=wt_cust.id 6 tablespace test_ind

Index created.

Elapsed: 00 select wt_cust.company_name,wt_cust.gmt_create 0015 00.08 Let's take a look at the execution plan of SQL: xx@DB > select wt_cust.company_name,wt_cust.gmt_create 2 from wt_cust,wt_CUST_EXT, wt_CUST_BOOK 3 where wt_cust.id=wt_CUST_EXT.Cust_Id 4 and wt_CUST_BOOK.Cust_Id=wt_cust.id

58 rows selected.

Elapsed: 00:00:00.00

Execution Plan-

-| Id | Operation | Name | Rows | Bytes | Cost (% CPU) |-| 0 | SELECT STATEMENT | | 1834K | 61m | 219K (1) | | 1 | TABLE ACCESS BY INDEX ROWID | wt_CUST | 1834K | 61m | 219K (1) | | 2 | BITMAP CONVERSION TO ROWIDS | 3 | BITMAP INDEX FULL SCAN | | CUST_WT_TEST |--

Please pay attention to the red part. Logical reading is greatly reduced!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.