Three questions about Oracle Index

The ORACLE tutorial is about Oracle Index.

Index is a common Database object. It is set to good or bad, and is used properly, which greatly affects the performance of Database applications and databases. Although there are a lot of materials about indexing usage, DBAs and developers often deal with it, but I found that there are still many people who have misunderstandings about it, so for the common problems in use, let's talk about three questions. The databases used in all examples in this article are Oracle 8.1.7 OPS on hp n series, all of which are real data. You do not need to pay attention to the specific data size. However, after using different methods, data comparison. This article is basically a cliché, but I try to use practical examples to really let you understand the key.

First, indexing is not always the best choice.

If Oracle does not use an index when it is indexed, it is not an Oracle optimizer error. In some cases, Oracle does select Full Table Scan instead of Index Scan ). These situations usually include:

1. The table is not statistics or statistics is outdated, leading to Oracle misjudgment.

2. According to the number of records and data blocks in the table, full table scan is faster than index scan.

The following SQL statement is the most common example of the 1st cases:


Before statistics, it uses full table scan and needs to read more than 6000 data blocks (one data block is 8 k). After statistics, INDEX (fast full scan) is used, and only 450 data blocks need to be read. However, if statistics does not do well, Oracle does not use indexes.

The 2nd cases are much more complicated. Generally, it is considered that the index is faster than the table, and it is hard to understand when full table scan is faster than index scan. To clarify this issue, we will first introduce two important data for Oracle to evaluate the cost of using indexes (cost): CF (Clustering factor) and FF (Filtering factor ).

CF: the so-called CF, in general, refers to the number of data blocks to be read each time an index block is read.

FF: the so-called FF is the result set selected by the SQL statement, as a percentage of the total data volume.

The approximate formula is FF * (CF + number of index blocks). Therefore, we can estimate the number of data blocks to be read in a query if an index is used. The more data blocks that need to be read, the larger the cost, the more likely Oracle will not choose to use index. (the number of data blocks to be read in the full table scan is equal to the actual number of data blocks in the table)

The core is that CF may be larger than the actual number of data blocks. CF is influenced by the data arrangement in the index. When the index is just created, the records in the index have a good correspondence with the records in the table, and CF is very small; after a large number of inserts and modifications to a table, the corresponding relationship becomes increasingly messy, and CF becomes larger and larger. In this case, the DBA needs to re-establish or organize the index.

If an SQL statement has been using an index for a long time and is not used for a long time, it is possible that CF has become too large and the index needs to be reorganized.

FF is an estimate made by Oracle based on statistics. For example, if the mytables table has 0.32 million rows, the minimum value of the primary key myid is 1 and the maximum value is 409654. Consider the following SQL statement:


These two seemingly similar SQL statements differ greatly from Oracle statements. Because the FF of the former is 100%, the FF of the latter may be only 1%. If its CF value is greater than the actual number of data blocks, Oracle may choose different optimization methods. In fact, tests on our database validate our predictions. The following are their explain plans when executed on HP:

First sentence:


Row 325917 has been selected.


Second sentence:


Obviously, 1st sentences do not use indexes, and 2nd sentences use the primary key index pk_mytables. FF, which has a huge impact. As a result, if we estimate FF in advance when writing SQL statements, you can almost predict whether Oracle will use indexes.

[NextPage]

Second, indexes are good or bad.

Indexes include B-tree indexes, Bitmap indexes, and Reverse B-tree indexes. B-tree indexes are commonly used. The full name of B is Balanced, meaning that, from the root of the tree to any leaf, it will go through the same level. an index can have only one field (Single column), multiple fields (Composite), a maximum of 32 fields, and Function-based index. many developers tend to use a single column B-tree index.

The so-called Index Quality refers:

1. The more indexes, the better. In particular, a large number of indexes that have never or are rarely used are only harmful to the system. In the OLTP system, more than five indexes per table will reduce the performance. In an SQL statement, Oracle cannot use more than five indexes.

2. In many cases, single-column indexes are not as efficient as compound indexes.

3. It is used for fields connected to multiple tables. Adding an index will be helpful.

So, under what circumstances should a single column index be less efficient than a composite index? It is obvious that when all the columns queried by SQL statements appear in the composite index, Oracle only needs to query the index block to obtain all the data, of course, it is much faster than using multiple single-column indexes. (In this case, this optimization method is called Index only access path)

What else? Let's look at an example:

Execute the following statement on HP (Oracle 8.1.7:


At the beginning, we have two single-column indexes: I _mytabs1 (coid) and I _mytabs2 (issuedate). The following figure shows the execution status:


As you can see, it reads 7000 data blocks to obtain more than 6000 rows queried.

Now, remove the two single-column indexes, add a composite index I _mytabs_test (coid, issuedate), and re-Execute. The result is as follows:


We can see that only 300 data blocks are read this time.

7000 to 300, which is the cost ratio of a single column index to a composite index in this example. This example shows that in many cases, single-column indexes are less efficient than compound indexes.

It can be said that there is actually a lot of work to do with index settings. To correctly set indexes, You need to perform an overall analysis on the application.
1 3

[NextPage]

Third, no need for a good index

Aside from what we mentioned above, false

[1] [2] Next page

The ORACLE tutorial is about Oracle Index. If you set a very good index, Any fool knows that you should use it, But Oracle does not need it. The first thing you need to do is to examine your SQL statements.

There are some basic conditions for Oracle to use an index:

1. This field in the where clause must be the first field of the composite index;

2. This field in the where clause should not be involved in any form of calculation.

Specifically, assume that an index is created in the order of f1, f2, and f3. Now there is an SQL statement. The where clause is f2 =: var2, this index cannot be used because f2 is not the index's 1st fields.

2nd problems are very serious among us. The following are several examples captured from the actual system:


The above example can be easily improved. Note that such statements run in our system every day, consuming our limited cpu and memory resources.

In addition to the principles, we must be aware of the impact of Operators on Oracle indexes. Here I will only explain which operations or operators will explicitly prevent Oracle from using indexes. The following are some basic rules:

1. If f1 and f2 are two fields in the same table, f1> f2, f1> = f2, f1

2, f1 is null, f1 is not null, f1 not in, f1! =, F1 like '% pattern % ';

3, Not exist

4. in some cases, f1 in does not need an index;

There is no way to avoid these operations. For example, if you find that the in operation in your SQL statement does not use indexes, you can change the in operation to a comparison operation + union all. In practice, I found that this is often effective.

However, whether or not Oracle actually uses indexes and whether indexes are actually valid must be tested in the field. A reasonable practice is to explain the complex SQL statement on the product database before writing it into the application. explain will get Oracle's plan for the SQL statement, and you can clearly see how Oracle optimizes the SQL statement.

If you often explain, you will find that it is not a good habit to write complex SQL statements, because the resolution plans of overly complex SQL statements are often unsatisfactory. In fact, splitting complex SQL statements can sometimes greatly improve the efficiency because it can be well optimized. Of course, this is a digress.

 

Previous Page

Previous Page [1] [2]