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

Today, take a look at the bitmap index in oracle. You will learn from this article, what is a bitmap index? Under what circumstances should bitmap indexes be used? The process of retrieving data in bitmap index, and the disadvantages of bitmap index? One: what is a bitmap index? Bitmap indexing has been introduced since oracle version 7. 3. Bitmap indexing is currently supported in both oracle Enterprise Edition and personal Edition, but not in the Standard Edition. A bitmap index is a structure in which pointers to multiple rows are stored with an index key entry, unlike the B* tree structure, in which there is a correspondence between the index key and the rows in the table. In a bitmap index, there may be only a few index entries, each index entry points to multiple rows, while in a traditional B* tree, an index entry points to one row, so what is a bitmap index? (borrowing examples from the Internet) there is a table called table, which consists of three columns, namely name, gender and marital status, in which only male and female are male and female, and marital status is divided into married, unmarried and divorced. The table has a total of 100w records. Now there is such a query: select * from table where Gender=' male 'and Marital=' unmarried

1) when no index is used, the database can only scan all records row by row, and then determine whether the record meets the query criteria. 2) for gender, the range of values available for B-tree index is only 'male' and 'female', and men and women may each stand 50% of the data of the table, so it is not necessary to add B-tree index to take out half of the data. On the contrary, if a field has a wide range of values, such as the ID number, it is more appropriate to use the B-tree index. In fact, when the fetched row data occupies most of the data in the table (more than 20%), even if the B-tree index is added, databases such as oracle and mysql will not use the B-tree index and are likely to scan all rows one row at a time. Next, let's talk about the principle of bitmap indexing: if the bitmap index is established on the gender column, for the gender column, for the rowid of each row (rowid can be understood as the physical position of each row), the bitmap index forms two vectors, the male vector is 10100. Each bit of the vector indicates whether the row is male, if so, bit 1, no 0, similarly, female vector bit 01011. (it can be understood that two vectors are generated in the gender column for each row of data: the male vector is male and the female vector is female: the median value of male vector is male: 1, the value is not male and 0, and the median value of female vector is female: 1, value is not female: 0)

If a bitmap index is established on the marital status column, for the marital status column, the bitmap index generates three vectors: married 11000., unmarried 00100. Divorce is 00010.

Second: the process of retrieving data by oracle bitmap index: when we use the query statement "select * from table where Gender=' male 'and Marital=" When we first take out the male vector 10100..., then take out the unmarried vector 00100..., and do the and operation on the two vectors, and then generate a new vector 00100. We can find that the result after the and of rowid=3 is 1, which means that this row of data of the rowid=3 of the table is the result of the query we need (the result of the query is as follows: "the result of and" is 1), and then find the data we need according to rowid.

Three: under what circumstances should bitmap indexes be used? Bitmap index is suitable for columns with only a few fixed values, such as gender, marital status, administrative region, and so on, while the type of ID card number is not suitable for bitmap indexing. If the different cardinality of the column queried by the user is very small, to index these columns with relatively small cardinality, you need to establish a bitmap index. So what does it mean that the difference base is very small? It can be considered that the number of different items in a rowset divided by the number of rows should be a very small number (close to 0). For example, a column (gender) may have values M, F, null. If there are 20000 pieces of data in a table, then 3Universe 20000 to 0.00015, then this is a case where the different cardinality is very small, similarly, if there are 100000 different values, compared with 10000000 results, the ratio is also very small, it can also be considered as a case of very small cardinality, bitmap index can be established; 4: the limitation or drawback of bitmap index? Bitmap indexes work well in read-intensive environments, but are extremely unsuitable for write-intensive environments, because a bitmap index key entry (which can be understood as the previous male, female, unmarried, married, etc.) points to multiple lines. If a session modifies the data of an indexed column, in most cases, all rows that the index entry only wants will be locked. Oracle cannot lock a single bit in a bitmap index entry, but locks the entire bitmap index entry. If other session modifications need to update the same bitmap index entry, it will be "locked out", which greatly affects concurrency, because each update may lock hundreds of rows, and their bitmap columns are not allowed to be updated concurrently. For example, there is a field job that records the positions of each employee, such as dba, java, php, and so on. Suppose we have a bitmap index on this job column. If rowid=100 employee occupation is php,rowid=120 employee occupation is php;, if session 1 uses update to update an employee's position (job), such as update table set table.job='dba' where rowid=100;, but does not have commit, and session 2 also uses update to update another employee's position, session 2 cannot update at this time, session 2 needs to wait for session 1 commit. Reason: session 1 updates the position of the employee in rowid=100. If the employee used to be php and is now changed to dba, then all rows of job=php and job=dba will be locked before commit, so when session 2 tries to update job=dba, it can only wait for the lock, and only unlock it after commit. This greatly affects concurrency. Conclusion: bitmap index is designed for data warehouse (that is, query environment), bitmap index is not suitable for OLTP system, bitmap index is not suitable for dml frequent environment, bitmap index is suitable for DSS system, bitmap index is not suitable for frequently modified system, the disadvantage is that concurrency is seriously affected, because when update index column values, all data rows pointed to by new and old values are locked. So use bitmap indexing with caution.

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.