How to improve the use of Oracle bitmap indexes

Bitmap indexing is an anomaly in the Oralce database index, which has outstanding performance in some special occasions. In general, the effect of a bitmap index is directly related to the cardinality of the column. For this reason, when it comes to how to improve the use of bitmap indexes, it is often related to the cardinality of this column. This must be a clear understanding of the cardinality of this column.

When to change the use of bitmap index

Cardinality is the number of distinct values that a column may have. For example, in an information table for citizens of the People's Republic of China, a billion of records are included. However, in these records, there are several special columns that contain a limited number of duplicate values. As a field of sex, there are only two possible values for men and women; if there are only 56 distinct values in the National column, there are only 34 provinces and autonomous regions in China.

Sometimes users may need to query the relevant data based on these columns. If the company engaged in activities, may need to confirm whether the activities involved in religious taboos, such as may involve the Hui taboo, then you need to inquire about employee information systems, to see if the company has Hui employees. If the company may be in 38 women's Day, will give women employees a certain amount of benefits, for this also need to pull out the list of female employees in the database, this time is based on the field of sex to query data. Wait a minute. If the user queries the cardinality of the column is very small, only a limited number of fixed values, such as gender, ethnicity, administrative regions and so on. You need to establish a bitmap index when you want to index these cardinality values to smaller columns. If you have to say a specific standard, then the author thinks that if the cardinality value in the entire table record 2% or the field content of the duplicate value of more than 100 times, then through the bitmap index can play a good effect.

In most cases, a base value is used to determine whether a bitmap index is required. But there is a special case where the cardinality of this column is very large, which means that the value in this column is not repeatable. But as long as you meet certain conditions, then create a bitmap index on this field, you can also play a good effect. In general, if a field is often used in a where query condition statement, and the operator is a logical operational symbol of and or or, the effect is much better than recommending other indexes.

Set the column that needs to establish the bitmap index to the fixed length data type.

In Oracle databases, data types are large, including fixed-length data types and variable-length data types. In the case of character data, there is a fixed-length string type (char) and a variable-length string type (VARCHAR2). Both data types are string data types, and their differences are mainly in the control of string lengths. Char stores a fixed-length string. If the actual stored string does not meet its specified maximum value, the database system automatically adds a space behind it. VARCHAR2 is primarily used to store variable-length character data types. Unlike storing a fixed-length char string type, if the data stored in this column does not meet the specified maximum value, the database system does not add a space at the end and is stored in the actual string. If you look at the volume of a database, it's definitely a very variable-length data type.

However, if you look at the effect of a bitmap index, it is a good idea to set the column that establishes the bitmap index to a fixed-length data type. Because bitmap indexes use fixed-length data types, they are superior in performance to variable-length data types. That is, if you want to create a bitmap index on a column of a character type, it is a good idea to set the data type of this column to char (even if it actually stores a different length) instead of setting an additional NVCHAR2. Because the loss of space is still worthwhile relative to the performance improvement.

In addition, we can use some other means to ensure that the storage memory length of the column consistent. Take the Chinese administrative region as an example, most of the administrative district is 3 words, such as Zhejiang province, but the length of a relatively long 8 characters, such as Xinjiang Uygur Autonomous regions. If there are more records, the cumulative result will be a relatively large waste. In this case, the database management in the design, you can use some compromise approach. Use abbreviations to represent each province's name. Another way is to use numbers to represent a province at the time of storage, and then to create a corresponding relationship between the numbers and the provinces in another table. The author suggested that the latter approach be adopted.

Anyway, if you want to create a bitmap index for a column, it's best for the database administrator to try to make the field a fixed-length data type, sometimes even sacrificing a bit of storage space trouble.

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/

Third, the use of bitmap index restrictions.

Although the bitmap index can significantly improve the query performance of the database when it is in a small base column or when using logical operators, it still has a lot of limitations in its use. Database administrators need to understand these limitations, so that the database design will not be a bamboo basket water empty.

If you set a field to a bitmap index, it is best to use a fixed-length data type. At the same time this length has the biggest limit. In the latest Oracle database version, the maximum length of this bitmap index cannot exceed 30. Do not know later version will not relax this length limit. A bitmap index cannot be declared as a unique index, such as a bitmap index that does not contain considered column data and cannot be used for any type of integrity check, which invalidates a bitmap index if the ALTER TABLE statement is executed and a column containing a bitmap index is modified.

One of the most significant limitations is that the Rule-based optimizer does not consider bitmap indexes. Oracle database system before executing a SQL, first analyze the execution plan of the statement, then follow the execution plan. The execution plan for parsing statements is performed by the optimizer. Oracle's optimizer has two optimization methods, i.e. rule-based optimization and cost-based optimization. rule-based optimization means that when the optimizer analyzes SQL statements, it follows some rules that are predetermined within Oracle. The cost-based optimization approach is to see the execution cost of the statement. The cost here is mainly CPU and memory. When the optimizer determines whether to use this method, the main reference is the statistics of tables and indexes. Statistics give information about the size of the table, the number of rows, the length of each line, and so on. The database system then uses this information to determine whether this optimization is required. In addition, the optimizer is derived from these two optimization patterns.

In short, the database manager needs to be clear, if you want to use a bitmap index, it is best to select the database optimizer as a cost-based optimizer model. The bitmap index is ignored if the database is based on a rule-based optimizer pattern. The bitmap index established by the database administrator will be useless at this point. Most database administrators may not be aware of this and must be a lesson.

Merge multiple bitmap indexes in the same query.

Usually, after merging multiple bitmap indexes in the same query, the query performance of the database can be improved significantly. In other words, such as in the Employee Information table has sex, marital status, title and other fields. These fields are lower cardinality fields that you can use to create a bitmap index. If the user is now in the query, according to the above three fields to query the employee's information, such as the need to find sex for women, marital status for married, the title of managers above the staff information, for them to hold a family party activities. This three bitmap index field is required in the query condition. When the database executes the query plan, if the WHERE clause contains the bitmap index fields, the selector uses 3 separate bitmap indexes in turn. Each bitmap record pointer that indicates which rows in the table contain known values in the bitmap. With these important information, the database performs a bitmap and operation and returns which rows will be returned from all four bitmaps. These values are then converted to ROWID values, and the query continues to complete the remaining processing work (querying for additional information based on the ROWID value).

In other words, if you use multiple bitmap indexes to query in a query conditional statement, the effect of the query is 1+1〉2. For this purpose in the application design, you can put some bitmap index fields as query conditions are placed in the query window, in an explicit way to allow users to select the query criteria. This is helpful for improving the query performance of your application.

30 bitmap indexes can be supported in a single table in an Oracle database. Of course, the actual work is often not used so much. This is usually only possible if the base value is smaller than a column with this bitmap index. Conversely, if you use a bitmap index on a column with a large cardinality value, it will have the opposite effect. In a table, the value of a small cardinality column is not very large. Therefore, database administrators need to pay more attention to the smaller values of these cardinality columns. If the user has the habit of querying the columns with small cardinality values, then the database administrator is not able to have any stingy, to immediately set these columns as bitmap indexes. And in the query design, it is best to be able to merge multiple bitmap indexes in the same query to improve the performance of the query.