Oracle: bitmap index and deadlock-1

Oracle: bitmap index and deadlock-1 B-tree index are more suitable for indexing the OLTP environment of Dynamic tables, while bitmap index is more suitable for using complex query data warehouse environments in Large static tables. Bitmap index features: 3. bitmap indexes allow null key values in B * Tree indexes because null values are not recorded, full table scan is used for queries based on is null, if the bitmap index column is null, the index can be used. www.2cto.com 4. bitmap index efficient access to table records when count (XX) is used, you can directly access the index to get statistics quickly. when performing and, or in (x, y,...) based on the bitmap index Column ,..) during query, the bitmap of the index is used for or operation. data can be filtered out before data is accessed. 5. bitmap indexes only need to perform one index for batch DML operations. Because Bitmap reflects data conditions, batch operations can update indexes much faster than B * Tree indexes for one row. 6. the Bitmap index lock mechanism does not lock DML operations of other sessions for B * Tree indexes. bitmap indexes use bitmap to reflect data. Different sessions update the same bitmap segment with the same key value, insert, update, and del. Ete locks each other. The syntax for creating an index is simple. You can add the keyword bitmap before the index in the syntax for creating a common index. For example, www.2cto.com create bitmap index <index_name> on <table_name (column_name)>, my first thought was whether they used Bitmap indexes. Because Bitmap indexes lock more than just one row but a group of data, the possibility of deadlock is greatly increased. I opened a ORA-60 Trace file, looked at the SQL statements and locks, and further determined my doubts, probably the problem caused by bitmap indexing. So I asked Mr. Wang, the developer, if they used Bitmap indexes on the table. Mr. Wang thought about it and said yes. I said Bitmap indexes are generally used in OLAP systems. Bitmap indexes cannot be used in environments with extremely high change frequencies. Normal indexes must be used. Otherwise, in the case of high concurrency, there will be a lot of deadlocks, the foreground will report a ORA-60 error. Applicable scenarios of www.2cto.com bitmap: 1. bitmap indexes are supported by Oracle Database 7.3, 8i, 9i Enterprise Edition, and Personal Edition. The Standard Edition does not support Bitmap indexes. 2. the rule-based optimizer cannot use Bitmap indexes. 3. suitable for column queries with a large number of duplicate values 4. for 8i and 9i versions, it is not applicable to single-row insertion and is applicable to batch insertion of data. Because the same key value is used during single-row insertion, bitmap blocks in one index block are generated for every eight rows inserted, even if the value is the same. during batch insertion, only one bitmap segment is generated for the same key value. 5. because concurrent DML operations lock a large number of data rows in the entire bitmap segment, bitmap indexes are mainly used for OLAP applications, and can also be used in OLTP tables mainly for read operations. there are three ways to discuss data insertion under www.2cto.com:. insert one row at a time, insert multiple rows, and commit at a time; B. commit each insert row. c. batch insert mode, which is submitted at a time. For the first method, observe the changes of the bitmap index. a. assume that eight rows of data with the same key value are inserted. If the data is inserted in each row and then submitted once, 8 bitmap B is generated. the method for submitting each row of data is similar to the above, but it is a little different. Copy the original bitmap, generate a new bitmap, and mark the original bitmap as deleted c. the third method is to insert data in batches. The bitmap is generated only once with the same key value. Therefore, the bitmap index is best inserted in batches. In this way, only one bitmap is generated for each key value. in the single row data insertion mode, each key value generates a bitmap for each eight rows of data. 10 Gb is much simpler. in the preceding three methods, the bitmap is generated with the same key value and has only one bitmap. The previous bitmap will not be deleted each time it is submitted, instead, the bitmap of the corresponding key value is directly modified.