oracle| problem
Index is a common database object, it is set up and used properly, which greatly affects the performance of database applications and databases. Although there is a lot of data on the use of indexing, DBAs and Developer often deal with it, but the author found that there are still a lot of people misunderstand it, so for the use of common problems, say three questions. All of the examples used in this article are Oracle 8.1.7 OPS on HP N series, examples are all real data, readers do not need to pay attention to the specific data size, but should pay attention to the use of different methods after the data comparison. This article is basically a cliché, but the author tried to use practical examples to really let you understand the key to things.
First, indexing is not always the best choice
This is not an Oracle optimizer error if it is found that Oracle does not use indexes when indexed. In some cases, Oracle does choose a full table scan (fully table Scan) rather than an index scan (Scan). These situations usually include:
1. The table did not do statistics, or statistics obsolete, leading to Oracle judgment error.
2. A full table scan is actually faster than an index scan, depending on the number of records and blocks of data that the table has.
For the 1th case, the most common example is the following SQL statement:
Select COUNT (*) from mytable;
Before statistics, it uses a full table scan, reads more than 6,000 blocks of data (a block is 8k), statistics, uses INDEX (FAST full SCAN), and only needs to read 450 blocks of data. However, statistics does not do well, and also causes Oracle to not use indexes.
The 2nd situation will be much more complicated. The general concept is that indexes are faster than tables, and it is difficult to understand when full table scans are faster than index scans. To make this clear, here are two important data for Oracle when evaluating the cost of using indexes: CF (clustering Factor) and FF (filtering factor).
CF: the so-called CF, in layman's terms, is the number of pieces of data read into each index block.
FF: the so-called FF, which is the result set selected by the SQL statement, as a percentage of the total amount of data.
The approximate formula is: FF * (CF + index block number), which estimates that a query, if the use of an index, will need to read the number of blocks of data. The more data blocks you need to read, the greater the cost, and the more likely Oracle will not choose to use Index. (The number of data blocks that a full table scan needs to read is equal to the actual number of blocks in the table)
The core of this is that CF may be larger than the actual number of data blocks. CF is affected by how the data in the index is arranged, usually when the index is first established, the records in the index have a good correspondence with the records in the table, CF is very small, and after a large number of inserts and modifications, the corresponding relationship becomes more and more chaotic, CF is also getting bigger. The DBA is required to re-establish or organize the index at this time.
If an SQL statement has previously used an index and is no longer used after a long time, one possibility is that CF has become too large to rearrange the index.
FF is Oracle's estimate based on statistics. For example, the Mytables table has 320,000 rows, the minimum value for the primary key myID is 1, and the maximum value is 409654, consider the following SQL statement:
Select * from Mytables where myid>=1; And
Select * from Mytables where myid>=400000
These two seemingly similar SQL statements, for Oracle, there is a huge difference. Because the former FF is 100%, and the latter FF may be only 1%. If its CF is larger than the actual number of blocks, Oracle may choose a completely different optimization approach. In fact, the tests on our database validate our predictions. The following are their explain plan when executed on HP:
Obviously, the 1th sentence does not use the index, and the 2nd sentence uses the primary key index Pk_mytables. The huge impact of FF is evident. The idea is that when we write an SQL statement, if we anticipate FF in advance, you can almost foresee whether Oracle will use the index.
Second, the index is good or bad
Index has 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, the meaning of which is to go through just as much level from tree root to any leaf. Indexes can have only one field (single column) or multiple fields (composite), up to 32 fields, 8I also supports function-based index. Many developer tend to use a single-column B-tree index.
What else? Let's take a look at an example:
Execute the following statement on HP (Oracle 8.1.7):
Select COUNT (1) from Mytabs where coid>=130000 and IssueDate >= to_date (' 2001-07-20 ', ' yyyy-mm-dd ').
To begin with, we have two Single-column indexes: I_MYTABS1 (coid), I_MYTABS2 (IssueDate), and the following are the implementation:
As you can see, only 300 blocks of data were read this time.
7000 pairs of 300 blocks, which is the ratio of the cost of a Single-column index to a composite index in this example. This example suggests that in many cases, a single-column index is less efficient than a composite index.
It can be said that in the setting of the index, in fact, there are a lot of work can be done. Setting up the index correctly requires an overall analysis of the application.
Third, the index is good, no need to be in vain
Put aside the foregoing, suppose you set a very good index, and any fool knows it should be used, but Oracle does not, then the first thing to do is to look at your SQL statements.
Oracle wants to use an index with some of the most basic conditions:
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, suppose an index is established in the order of F1, F2, and F3, and now there is an SQL statement, where clause is F2 =: var2, because F2 is not the 1th field of the index, the index cannot be used.
The 2nd question is very serious among us. Here are a few examples captured from the actual system:
Select Jobid from mytabs where isreq= ' 0 ' and to_date (updatedate) >= to_date (' 2001-7-18 ', ' yyyy-mm-dd ');
.........
The above examples can be easily improved. Note that such statements run daily on our system, consuming our limited CPU and memory resources.
In addition to the 1, 2 of these two principles that we must keep in mind, we should also try to familiarize ourselves with the effects of various operators on whether or not Oracle uses indexes. I'm only talking about what operations or operations Fu (explicitly) prevents Oracle from using indexes. Here are some basic rules:
1, if F1 and F2 are two fields of the same table, then F1>f2, F1>=F2, F1
2, F1 is null, F1 isn't null, F1 not in, F1!=, F1 like '%pattern% ';
3, not exist
4, in some cases, F1 in will not be indexed;
For these operations, there is no way, only try to avoid. For example, if you find that an in operation in your SQL does not use an index, you may be able to change the in operation to a comparison operation + UNION ALL. The author found that many times this is very effective in practice.
However, whether Oracle really uses the index, whether the index is really effective, or if it has to be tested in the field. It's a good idea to do a explain on a product database before writing it to an application for complex SQL. Explain will get Oracle's parsing of the SQL (plan), and you can clearly see how Oracle optimizes the SQL.
If you often do explain, you'll find that liking to write complex SQL is not a good habit, because overly complex SQL's parsing plans are often unsatisfactory. In fact, taking complex SQL apart can sometimes greatly improve efficiency because of the good optimization. Of course, this is a digression.
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.
