Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to understand oracle composite index". The explanation content in this article is simple and clear, easy to learn and understand. Please follow the ideas of Xiaobian slowly and deeply to study and learn "how to understand oracle composite index" together!

First, composite indexes are better than single-field indexes in most cases. Take SB_ZSXX(declaration_collection information table) of tax system as an example, this table is the largest transaction table of tax system. If the taxpayer identification number, tax authority code, month three fields query, each field in the table optional or binding are not strong, such as a taxpayer identification number has a lot of tax records, a tax authority code and the same month records more, so the three fields together,"a taxpayer identification number + a tax authority code + a month" records are much less. Therefore, composite indexes are much more efficient than single-field indexes. Many systems rely on new composite indexes to greatly improve efficiency.

However, composite index is more complicated than single field index. Composite index has two important principles: prefix and optional. Compound indexing can backfire if misused in a confused way.

To illustrate, examples are as follows:

Suppose an index is built on the three fields (ename,job,mgr) of the employee table (emp), for example, the index name is idx_1.The three fields are employee name, job and manager number respectively. Then, write the following query statement, and continue to permute and combine the query conditions and order, for example:

SQL code

select * from emp where ename = 'a' and job = 'b' and mgr = 3 ;

select * from emp where job = 'b' and ename = 'a' and mgr = 3 ;

select * from emp where mgr = 3 and ename = 'a' and job = 'b' ;

select * from emp where mgr = 3 and job = 'b' and ename = 'a' ;

select * from emp where job = 'b' and mgr = 3 and ename = 'a' ;

.....

Answer the question: Under various combinations of conditions, is the index (idx_1) just built used or not? That is to say, is the access to emp table full table scan or index (idx_1) access?

The answer is: as long as there is name ='a 'condition in the above statement, you can use the index (ind_1) instead of the full table scan (this is the prefix of the composite index).

Principle and Design Suggestion of Composite Index

1. The first suggestion for composite indexing: Prefixing

Let's start with an example. Suppose province, city and county store data in three fields and establish a composite index. Remember: Oracle indexes, including composite indexes, are sorted. For example, the composite index is sorted by province, city, and county on the database index tree:

provincial, city and county

Beijing Dongcheng

Beijing Xicheng

Beijing Haidian District

... ...

Harbin, Heilongjiang Province, Daoli District

Harbin, Heilongjiang Province, Daowai District

Harbin, Heilongjiang Province, Xiangfang District

... ...

Longsha District, Qiqihar, Heilongjiang Province

Qiqihar, Heilongjiang Province, Tiefeng District (Ti)

Fularki District, Qiqihar, Heilongjiang Province

... ...

Changsha, Hunan Province, Furong District

Yuelu District, Changsha, Hunan

Changsha, Hunan Province, Kaifu District

... ...

Oracle is not intelligent, it will only follow the map, the index structure is sorted by province first, so as long as you give the province name, you can use the index. Without the provincial name,oracle becomes a headless fly, a random search, and becomes a full table scan. For example, if you give only one county condition, such as "Kaifu District," Oracle will certainly not use that index anymore.

2. About skip scan index

Sometimes the first field of a composite index does not appear in the statement, and oralce uses that index. yes, this is called oralce's skip scan index function, which oracle 9i only provides.

What is skip index scan for? If oracle finds that the first field has a low value, for example, suppose the emp table has a gender field and a (gender,ename,job,mgr) composite index is created. Because gender is only male and female,oracle can split the index into two composite indexes ('male ',ename,job,mgr) and ('female',ename,job,mgr) in order to improve the utilization of the index. Thus, even if there is no gender condition,oracle will search the male index tree and female index tree separately.

However, the (gender,ename,job,mgr) index itself is designed to be irrational and violates the second principle of composite indexing, Selectivity, as described below.

3. The second principle of composite indexing: Selectivity

You may ask: How do I sort the field order in a composite index? The second principle of composite indexing is selectivity.Oracle recommends sorting fields by their selectivity, i.e., the ones with the most values come first. For example,(ename,job,mgr,gender),(county, city, province). This is because, the more field values, the stronger the selectivity, the fewer records located, the higher the query efficiency. For example, there may be only one Kaifu District in China, while there are too many records in Hunan Province.

4. Composite index design recommendations

(1). Parses constraint fields in SQL statements.

(2). If the constraint fields are fixed, creating a plain B* tree composite index for multiple fields is preferred. If it involves three fields: month, taxpayer identification number and tax authority code, a composite index can be considered.

(3). If a single field is a primary key or a unique field, or a field with very high selectivity, although the constraint conditions are relatively fixed, it is not necessary to build a composite index, but a single field index can be built to reduce the cost of composite index.

(4). In designing composite index, we should consider the first design principle of composite index: prefix property of composite index. That is, in SQL statements, a composite index is enabled only if its first field is used as a constraint.

(5). In composite index design, the optionality of composite index should be considered. That is, according to the optional high and low, composite index field sorting. For example, the order of the fields in the index is Taxpayer Identification Number, Tax Authority Code, Month.

(6). If the fields involved in the condition are not fixed and the combination is flexible, indexes are established for the three fields of month, tax authority code and taxpayer identification number respectively.

(7). If it is a multi-table join SQL statement, note whether composite indexes can be created on the join fields of the driven table and other constraint fields of the table.

(8). Analyze execution plans to quantify their effectiveness using multiple SQL analysis tools.

Thank you for reading, the above is "how to understand oracle composite index" content, after the study of this article, I believe we have a deeper understanding of how to understand oracle composite index this problem, the specific use of the situation also needs to be verified by practice. Here is, Xiaobian will push more articles related to knowledge points for everyone, welcome to pay attention!

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.