Four Oracle tricks to improve the usage of Bitmap indexes

Bitmap indexes are heterogeneous in oralce database indexes, which have outstanding performance in some special cases. In general, the bitmap index effect is directly related to the column base. Therefore, when talking about how to improve the usage of Bitmap indexes, it is often related to the column base. Therefore, you must have a clear understanding of the column base.

1 When Will Bitmap indexes be used?

A base number is the number of distinct values that a column may have. For example, in an information table of citizens of the People's Republic of China, hundreds of millions of records are contained. However, there are several special columns in these records, which contain several finite repeated values. For example, the gender field only has two possible values: Male and female. For example, there are only 56 distinct values in the National column. For example, in the column of origin, china has only 34 provinces, cities, and autonomous regions.

Sometimes you may need to query related data based on these columns. For example, when a company is engaged in activities, it may need to check whether there are any religious taboos involved in the activities. If there are Hui Nationality taboos involved, it is necessary to query the employee information system, check whether there are employees of Hui nationality in the company. For example, the company may provide some benefits to female employees on Women's Day, so we also need to pull out the list of female employees in the database, in this case, the data is queried based on the gender field. And so on. If the base of the column queried by the user is very small, there are only a few fixed values, such as gender, ethnicity, and administrative region. To create an index for these columns with relatively small base values, you need to create a bitmap index. If a specific standard is required, the author believes that if the base value is less than 2% of the entire table record or the field content is repeated for more than 100 times, the bitmap index can achieve good results.

In most cases, the base value is used to determine whether Bitmap indexes are required. However, there is another special case where the base value of this column may be very large, that is, the value repeatability in this column is not very high. However, as long as certain conditions are met, creating a bitmap index on this field can also play a good effect. In general, if a field is often used in the where query Condition Statement and the operator used is the and or logical operator number, the effect is much better than other indexes.

2. Set the columns for which a bitmap index is to be created to a fixed-length data type.

In Oracle databases, data types include fixed-length data types and variable-length data types. For example, for string type data, there are fixed-length string types (char) and variable-length string types (varchar2 ). The two data types are both string data types, and their differences mainly lie in the control of the string length. Char stores fixed-length strings. If the actually stored string does not reach the maximum value, the database system will automatically add a space at the end. Varchar2 is mainly used to store variable-length character data types. For example, if the types of char strings with fixed lengths are different, if the data stored in this column does not reach the specified maximum value, the database system will not add spaces at the end to store the data as the actual string. If we look at the size of the database, it is certainly better to be a variable-length data type.

However, if we look at the bitmap index effect, we 'd better set the column that creates the bitmap index to a fixed-length data type. Because Bitmap indexes use a fixed-length data type, it is more superior in performance than variable-length data types. That is to say, if you want to create a bitmap index on a character-type column, it is best to set the Data Type of this column to Char (even if the actual storage length is different), instead of setting additional nvchar2. This space loss is still worth the performance improvement.

In addition, we can also use other methods to ensure that the memory length in the column is consistent. Take the administrative region of China as an example. Most administrative regions have three characters, for example, Zhejiang province. However, the length of the Administrative Region is 8 characters long, for example, Xinjiang Uygur Autonomous Region. If there are many records, the accumulation will indeed cause a great waste. In this case, database management can adopt some compromise processing methods during design. For example, abbreviated names are used to represent the names of provinces. Another way is to use numbers to represent provinces during storage, and then create a correspondence between numbers and provinces in another table. I suggest using the following method.

In short, if you want to create a bitmap index for a column, it is best for the database administrator to try to make the field adopt a fixed-length data type, sometimes even if you sacrifice a little storage space.

3. Usage Restrictions of Bitmap indexes

Although Bitmap indexes can significantly improve the database query performance in columns with a small base or when using logical operators, they still have many restrictions. Database administrators need to understand these restrictions so that they will not leave a basket empty during database design.

If you set a field as a bitmap index, you 'd better use a fixed-length data type. At the same time, this length has the maximum limit. In the latest Oracle database version, the maximum length of the bitmap index cannot exceed 30. I don't know if the length limit will be relaxed in future versions. If a bitmap index cannot be declared as a unique index, if a bitmap index does not contain column data, it cannot be used for any type of integrity check; if you execute the alter table statement and modify a column that contains a bitmap index, this will invalidate the bitmap index.

The most significant limitation is that the rule-based optimizer does not consider Bitmap indexes. Before executing an SQL statement, the Oracle database system must analyze the statement execution plan and then execute the statement according to the execution plan. The optimizer is used to analyze the execution plan of statements. There are two Optimization Methods for Oracle optimizer: Rule-Based Optimization and cost-based optimization. Rule-based optimization means that when the optimizer analyzes SQL statements, it complies with Oracle's predefined rules. The cost-based optimization method is to check the statement execution cost. The cost here mainly refers to CPU and memory. When determining whether this method is used, the optimizer mainly refers to the statistical information of tables and indexes. The statistical information shows the table size, the number of rows, and the length of each row. Then, the database system determines whether this optimization method is required based on the information. There are also optimizers derived from these two optimization modes.

In short, the database administrator needs to make it clear that if Bitmap indexes are used, it is best to select the database optimizer as the cost-based optimizer mode. If the database adopts the rule-based optimizer mode, bitmap indexes are ignored. In this case, the bitmap index created by the database administrator is useless. Most database administrators may not be clear about this and must be taken as an example.

4. Merge multiple Bitmap indexes in a single query

After merging multiple Bitmap indexes in a single query, the query performance of the database can be significantly improved. That is to say, the employee information table contains fields such as gender, marital status, and title. These fields are fields with low base numbers and can be used to create Bitmap indexes. If you want to query the employee information based on the preceding three fields, if you need to find information about employees whose gender is female, whose marital status is married, and whose professional title is manager or above, hold a family party for them. In this case, the three bitmap index fields need to be used in the query conditions. When the database executes the query plan, if the WHERE clause contains these bitmap index fields, the optimizer will use three separate Bitmap indexes in sequence. Each bitmap record pointer is used to indicate which rows in the table contain known values in the bitmap. With this important information, the database executes a bitmap and operation and returns the rows from all the four bitmap. Then convert these values to rowid values, and the query continues to complete the remaining processing (query other information based on the rowid value ).

That is to say, if multiple Bitmap indexes are used in a query Condition Statement, the query effect is 1 + 1> 2. Therefore, in the application design, you can place some bitmap index fields as query conditions in the query window, allowing users to select query conditions explicitly. This is of great help to improve the query performance of applications.

A table in the Oracle database supports up to 30 Bitmap indexes. Of course, this is often not used in actual work. Generally, this bitmap index can be used only on columns with relatively small base values. On the contrary, if Bitmap indexes are used on some columns with relatively large base values, the results will be the opposite. In a table, a small number of values is not large. Therefore, database administrators need to pay more attention to the small values of these base columns. If you have the habit of querying these columns with small base values, the database administrator cannot be stingy and immediately set these columns as Bitmap indexes. In addition, when designing a query, it is best to merge multiple Bitmap indexes in the same query to improve the query performance.

Tip: http://www.uplook.cn/kbase-Index-show-view6700.html