Database System Concept 16-bitmap indexing and query processing

One, bitmap index
Bitmap indexing (bitmap indices) is a simple query designed for multiple keys. The premise of applying a bitmap index is that records must be numbered sequentially, starting with 0. Given the number n, it must be easy to find the corresponding record, if the record is stored in a contiguous block, you can convert the number N to block number + block offset representation to quickly locate the record position.

Structure of the bitmap index

A bitmap index corresponds to a record with a bit, which is why the record needs to be numbered. Instructor_info table For example, the gender value of male and female two, income grade is divided into 5 levels, there are 5 kinds of values. When a bitmap index is created for a gender attribute, it is established for male and female, and for male bitmaps, if the gender of a record is male, then the corresponding bit on the bitmap is set 1,female, and the revenue level bitmap also uses the same practice.
The advantages of bitmap indexing are reflected in queries based on multiple keys, such as querying where gender= ' f ' and income_level= ' L2 ', simply by intersecting the L2 of F's bitmap and bitmap.
In addition, in the analysis of data often need to count the number of records that meet certain conditions, the use of bitmap can also be easily implemented, just to count the number of bits in the intersection value of 1.
Deleting a record causes a gap in the data sequence, but moving the data to eliminate the gap overhead is expensive, so introduce a new presence bitmap (existence bitmap), where the gap corresponds to 1. The new data is appended to the trailer, which does not affect the order of the existing records.

Second, query processing

In the process of extracting data from a database, the operations of query processing include: parsing and translating, optimizing, evaluating and executing.

Measurement of query cost
The cost of the query is measured using the number of block transfers and the number of disk seeks to search for disks. Suppose that the disk subsystem transmits a block of data that requires TT seconds, the search data requires Ts seconds, then the transfer of B block and the S-disk search operation will consume b*tt+s*ts seconds. Now the typical value of the disk is Tt = 0.1 milliseconds, ts = 4 milliseconds, assuming that the disk block size is 4KB, the transfer rate is 40mb/seconds.
by separating the read operation from the write operation, the write operation takes approximately twice times as much time as the read operation, since the disk system reads the data again to verify that the write was successful.

Learning materials: Database System concepts, by Abraham Silberschatz, Henry F.korth, S.sudarshan

Database System Concept 16-bitmap indexing and query processing