Three problems with Oracle Index

  • 2020-05-27 07:24:49
  • OfStack

The ORACLE tutorial you are looking at is: 3 questions for Oracle Index.

Indexes (Index) are common database objects that can greatly affect the performance of database applications and Database if they are set up and used properly. Although there are a lot of materials on the use of index, DBA and Developer often deal with it, but the author found that there are still a lot of people have misunderstandings about it, so for the common problems in the use of three questions. The database used for all the examples in this article is Oracle 8.1.7OPS on HP N series. The examples are all real data. The reader need not pay attention to the specific data size, but should pay attention to the comparison of the data after using different methods. This article is mostly a cliche, but I'm trying to get you to really see the point with real examples.

In lecture 1, indexes are not always the best choice

If you find that Oracle is not using an index when it has an index, it is not an error on Oracle's optimizer. In some cases, Oracle does choose a full table scan (Full Table Scan) over an index scan (Index Scan). These situations usually include:

1. statistics is not made in the table, or statistics is obsolete, resulting in Oracle's wrong judgment.

2. Based on the number of records and data blocks owned by the table, a full table scan is actually faster than an index scan.

For the first case, the most common example is the following sql statement:


Before statistics was done, it used a full table scan, requiring more than 6,000 data blocks to be read (one block is 8k). After statistics, it used INDEX (FAST FULL SCAN), requiring only 450 data blocks to be read. However, if statistics does a bad job, it also causes Oracle not to use indexes.

The second case is much more complicated. 1 the general concept is that the index is faster than the table, it is difficult to understand when the full table scan is faster than the index scan. To clarify the issue, here are two important data points for Oracle when evaluating the cost of using an index (cost) : CF(Clustering factor) and FF(Filtering factor).

CF: CF is the number of data blocks to be read for each index block.

FF: FF is the result set selected by the sql statement as a percentage of the total data volume.

The approximate calculation formula is: FF * (CF + number of index blocks), which estimates the number of data blocks to be read into a query if an index is used. The more data blocks you need to read in, the larger cost is, and the more likely Oracle is not to choose index.

At its core, CF may be larger than the actual number of data blocks. CF is affected by the arrangement of the data in the index. When the index is just established, the records in the index have a good correspondence with the records in the table. CF is small. After a large number of inserts and modifications to the table, this correspondence becomes increasingly chaotic and CF becomes larger and larger. At this point, DBA needs to re-establish or organize the index.

If an sql statement that used an index directly before 1 is no longer used after a long period of time, one possibility is that CF has grown too large and needs to be reorganized.

FF is Oracle's estimate based on statistics. For example, the mytables table has 320,000 rows, and its primary key myid has a minimum value of 1 and a maximum value of 409,654. Consider the following sql statements:


These two seemingly identical sql statements make a huge difference to Oracle. Because the FF of the former is 100%, while the FF of the latter is probably only 1%. If its CF is greater than the actual number of data blocks, Oracle may choose a completely different optimization approach. In fact, tests on our database confirmed our predictions. Here are their explain plan when executed on HP:

Line 1:


Line 325,917 has been selected.


The second sentence:


Obviously, the index is not used in the first sentence, and the primary key index pk_mytables.FF is used in the second sentence. As a result, when writing sql statements, you can almost predict whether Oracle will use an index if you estimate FF by 1.

[NextPage]

In lecture 2, indexes are good or bad

The indexes are B tree index, Bitmap index, Reverse b tree index, etc. The most commonly used is the B tree index. The full name of B is Balanced, which means that from tree root to any one leaf, the same number of level are passed through. An index can have only one field (Single column), or multiple fields (Composite), up to 32 fields. 8I also supports Function-based index. Many developer prefer a single-column B tree index.

The so-called index is good or bad refers to:

1, more indexes is not better. In particular, large Numbers of indexes that are never or rarely used are only damaging to the system. OLTP systems with more than five indexes per table degrade performance, and Oracle can never use more than five indexes in one sql.

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

3, for multiple table links of the field, with the index will be very useful.

So, when is a single-column index less efficient than a composite index? One obvious case is when all the columns queried by the sql statement appear in the composite index, and since Oracle only needs to query the index block to get all the data, it is of course much faster than using multiple single-column indexes. (at this point, this optimization is called Index only access path)

What else? Let's look at one example:

Execute the following statement on HP (Oracle 8.1.7) :


1 to start, we have two single-column indexes: I_mytabs1(coid), I_mytabs2(issuedate). Here's how it works:


As you can see, it reads 7,000 data blocks to get the 6,000 + rows it 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:


As you can see, only 300 blocks were read this time.

7000 pieces versus 300 pieces, which is the ratio of the cost of a single-column index to a composite index in this example. This example reminds us that in many cases, a single-column index is not as efficient as a composite index.

You can say that there is a lot of work that can be done to set up the index. Setting the index correctly requires an overall analysis of the application.
1 3

[NextPage]

3, no matter how good the index is, it is useless to use it

Forget what I said before, false

[1] [2] next page

The ORACLE tutorial you are looking at is: 3 questions for Oracle Index. Let's say you set up a really good index that any fool would know to use, but Oracle doesn't, so the first thing you need to do is look at your sql statement.

For Oracle to use an index, there are some basic conditions:

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

2, this field in the where clause should not participate in any form of computation

Specifically, if an index is established in the order of f1, f2 and f3, and there is now an sql statement, where clause is f2 = : var2, then the index cannot be used because f2 is not the first field of the index.

The second problem is very serious among us. Here are a few examples taken from the actual system:


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

In addition to the two principles of 1,2 that we must keep in mind, we should try to be familiar with the effects of various operators on whether Oracle USES indexes or not. I'll just show you which operations or operators explicitly (explicitly) prevent Oracle from using indexes. Here 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 null, f1 not in, f1! =, f1 like pattern '% %';

3, Not exist

4. In some cases, f1 in will not use the index;

There is no alternative but to avoid these operations. For example, if you find that the in operation in your sql is not using an index, you might change the in operation to the compare operation + union all. I've found in practice that this works a lot.

However, whether Oracle actually USES an index, and whether it really works, must be tested in the field. It makes sense to write a complex sql and do explain.explain once in the production database before writing it to the application. explain.explain will get Oracle's parsing of sql (plan), and you can see exactly how Oracle is optimized for sql.

If you do explain frequently, you will find that it is not a good habit to write complex sql, because the overly complex sql often has a unsatisfactory parsing plan. In fact, taking the complex sql apart can sometimes be a huge efficiency boost, as it can be well optimized. Of course, that's beside the point.

On 1 page

Previous page [1] [2]


Related articles: