Index (index) is a common database object. Its setting is good, use it, greatly affects database applications and Database performance. Although there are many materials that are indexed, DBA and Developer are often dealing with it, but the author discovered that there are still many people who misunderstand it, so three issues are thoroughly reported. The database used in this article is Oracle 8.1.7 OPS ON HP N Series, all of which are real data, readers don't need to pay attention to the specific data size, but should be noted that the data is compared. This article is basically a crying, but the author tries to truly understand the key to things through the actual example.
In order to talk, index is not always the best choice.
If it is found that Oracle is in the case of an index, it is not an index, which is not an Oracle optimizer error. In some cases, Oracle does select a full table scan (Full Table Scan), not index scan. These sites are usually:
1, the table did not do Statistics, or statistics, changing the Oracle's judgment.
2. According to the number of records and data blocks owned by the table, the full table scan is faster than the profile scan.
For the first case, the most common example is the following SQL statement:
Select count (*) from myTable;
Before you don't make Statistics, it uses a full mete scan, you need to read more than 6,000 data blocks (a data block is 8K), after STATISTICS, use Index (Fast Full Scan), just read 450 data Piece. However, Statistics is not good, and it will also cause Oracle without the index.
The second case is much more complicated. Generally consulted, it is considered that the index is fast, and it is difficult to understand what the full mete scan is fast. To explain this problem, you will first introduce Oracle two important data when evaluating the cost of the index (COST): CF (Clustering Factor) and FF (Filtering Factor).
CF: The so-called CF, is popular, and each reads an index block, how many data blocks are to be read.
Ff: The so-called FF is the result set selected by the SQL statement, accounting for the percentage of the total amount of data.
The approximate calculation formula is: ff * (CF index block number), it is estimated that a query, if an index is used, the number of data block blocks will be required. The more data blocks that need to be read, the larger the COST, the more likely, the more likely not choose to use INDEX. (Full Table Scan requires the number of data blocks to be read, equal to the actual data block of the table)
Its core is that CF may be larger than the actual number of data blocks. The CF is affected by the data of the data in the index. When the index is just established, the records in the index have a good correspondence between the records in the table, and the CF is small; after a large number of insertions, this correspondence The relationship is getting more and more chaotic, and CF is getting bigger and bigger. The DBA is required to re-establish or organize the index.
If a SQL statement has always used an index, it is no longer used for a longer period of time. One may be that CF has become too big, and it is necessary to re-organize the index.
FF is an estimate made by Oracle according to Statistics. For example, the MyTables table has 32,000 lines. The minimum value of the primary key MyID is 1, the maximum is 409654, consider the following SQL statement: select * from myTables where myid> = 1; and
Select * from mytables where myid> = 400000
These two seems to be almost the SQL statement, and there is a huge difference in Oracle. Because the former's FF is 100%, while the latter's FF may only be 1%. If its CF is greater than the actual number of data blocks, Oracle may choose a completely different optimization. In fact, the tests on our database have verified our forecast. The following is performed on HP, their Explain Plan:
first sentence:
SQL> SELECT * from myTables where myid> = 1;
325917 is selected.
Execution Plan
-------------------------------------------------- ------------
0 Select Statement Optimizer = Choose (COST = 3132 Card = 318474 BYT ES = 141402456)
1 0 Table Access (Full) of 'MyTables' (COST = 3132 Card = 318474 BYT ES = 141402456)
Statistics
-------------------------------------------------- ------------
7 Recursive Calls
89 DB Block Gets
41473 CONSISTENT GETS
19828 Physical READS
0 redo size
131489563 BYTES SENT VIA SQL * NET to Client
1760245 BYtes Received Via Sql * Net from Cliant
21729 SQL * NET ROUNDTRIPS TO / FROM Client
1 Sorts (Memory)
0 Sorts (Disk)
325917 ROWS Processed
The second sentence:
Execution Plan
-------------------------------------------------- ------------
0 Select Statement Optimizer = Choose (COST = 346 Card = 663 BYtes = 2 94372)
1 0 Table Access (By Index Rowid) of 'MyTables' (COST = 346 Card = 663
Bytes = 294372)
2 1 Index (Range Scan) of 'PK_MYTABLES' (COST = 5 Card = 663)
Statistics
-------------------------------------------------- ------------
1278 Recursive Calls
0 DB Block Get
6647 Consistent Gets
292 Physical READS
0 redo size
3544898 BYTES SENT VIA SQL * NET to Client
42640 Bytes Received Via SQL * Net from Cliant
524 SQL * NET ROUNDTRIPS TO / FROM Client1 Sorts (Memory)
0 Sorts (Disk)
7838 ROWS Processed
Obviously, the first sentence does not use the index, and the second sentence uses the primary key index PK_MYTABLES. The huge impact of FF is hereby visible. Thus, when we write a SQL statement, if you estimate ff in advance, you can almost foresee whether Oracle uses an index.
Second, the index is also good
Indexes include B Tree index, Bitmap index, Reverse B Tree index, and so on. The most commonly used B TREE index. B's full name is Balanced, its meaning is that from Tree's root to any Leaf, you have to pass the same level. Index can have only one field (SINGLE Column), you can have multiple fields (Composite), up to 32 fields 8i also supports Function-Based Index. Many developers tend to use a single column B tree index.
The so-called quality of the index refers to:
1, the index is not, the better. In particular, a large number of never or almost no indices are only damage to the system. The OLTP system has more than 5 indexes per table, and in one SQL, Oracle never uses more than 5 indexes.
2, many times, a single column index is not as efficient as composite index.
3, the field used for multi-table link, plus indexes will work.
So, in what circumstances, single column index is not as efficient as composite index? One is obvious, that is, when the column query by the SQL statement appears in the composite index, when Oracle only needs to query the index block, all data can be obtained, of course, more than a single column index. Many. (At this point, this optimization method is called Index Only Access Path)
In addition to this? Let's take a look at an example:
Perform 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').
At first, we have two single column index: i_mytabs1 (coid), i_mytabs2 (Issuedate), below is execution:
Count (1)
------------
6427
Execution Plan
-------------------------------------------------- ------------
0 Select Statement Optimizer = Choose (COST = 384 Card = 1 Bytes = 11)
1 0 sort (aggregate)
2 1 Table Access (by index rowid) of 't_mytabs' (COST = 384 Card)
= 126 bytes = 1386)
3 2 INDEX (Range Scan) of 'i_mytabs2' (Non-Unique) (COST = 11
CARD = 126)
Statistics
-------------------------------------------------- -------- 172 Recursive Calls
1 DB Block Gets
5054 Consistent Gets
2206 Physical READS
0 redo size
293 BYTES SENT VIA SQL * NET to Client
359 Bytes Received Via Sql * Net from Cliant
2 SQL * NET ROUNDTRIPS TO / FROM Client
5 Sorts (Memory)
0 Sorts (Disk)
1 rows proped
It can be seen that it reads 7000 data blocks to get more than 6,000 rows of queries.
Now, remove these two single-column index, add a composite index i_mytabs_test (coid, issuedate), re-executed, the result is as follows:
Count (1)
------------
6436
Execution Plan
-------------------------------------------------- ------------
0 Select Statement Optimizer = Choose (COST = 3 Card = 1 BYTES = 11)
1 0 sort (aggregate)
2 1 Index (Range Scan) of 'i_mytabs_test' (Non-Unique) (COST = 3 Card = 126 bytes = 1386)
Statistics
-------------------------------------------------- ------------
806 Recursive Calls
5 DB Block Gets
283 Consistent Gets
76 Physical READS
0 redo size
293 BYTES SENT VIA SQL * NET to Client
359 Bytes Received Via Sql * Net from Cliant
2 SQL * NET ROUNDTRIPS TO / FROM Client
3 sorts (memory)
0 Sorts (Disk)
1 rows proped
It can be seen that only 300 blocks are only read this time.
7000 pieces to 300 pieces, this is in this example, the ratio of single column index and composite index. This example prompts us, in many cases, the single column index is not as efficient as the composite index.
It can be said that there is a lot of work to do on the problem of indexing. To properly set an index, you need to make an overall analysis of the application.
Three words, the index is better, no need to be white
Throws out, assuming that you have set a very good index, any fool knows that it should be used, but Oracle is not worthless, then the first thing you need to do is to examine your SQL statement.
Oracle To use an index, there are some 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 participate in any form of calculation
Specifically, it is assumed that an index is established in the order of F1, F2, F3, and now there is a SQL statement, where the clause is F2 =: var2, because F2 is not the first field of the index, the index cannot be used.
The second question is very serious among us. The following is a few examples caught from the actual system: Select Jobid from myTabs where isreq = '0' and to_date (update)> = to_date ('2001-7-18', 'YYYY-mm-DD');
.........
The above examples can be easily improved. Note that this statement runs in our system every day, consumes our limited CPU and memory resources.
In addition to 1,2, these two we must keep in mind the principle of the heart, we should also be familiar with the effects of various operators to use indexes in Oracle. Here I only say which operations or operators will explicitly prevent Oracle from using indexes. Here are some basic rules:
1. If F1 and F2 are two fields of 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 will not use index;
For these operations, there is no way, only try to avoid it. For example, if you find that the IN operation in your SQL does not use an index, you may change the in action to a comparison operation Union ALL. The author found many times this very effective in practice.
However, whether Oracle really uses an index, use indexes to be truly effective, or must be quiz. The reasonable approach is that the complex SQL written is written before writing it to the application. Explain will be explain on the product database. EXPLAIN will obtain an analysis of the SQL to the SQL (Plan), which can clearly see Oracle How to optimize the SQL.
If you often do explain, you will find that love written complex SQL is not a good habit, because it is often not satisfactory. In fact, unpacking complex SQL is sometimes greatly improved because it can be well optimized. Of course, this is already a question.
