Oracle's Index

Oracle Indexes (index) is a common database object that 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. The index is not always the best choice. If an oracle is found to be indexed without using an index, this is not an Oracle optimizer error. In some cases, Oracle does choose a full table scan (fully table Scan) rather than an index scan (Scan). These conditions are usually: 1, the table does 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 the number of blocks that the table has. For the 1th case, the most common example is the following SQL statement: SELECT COUNT (*) from mytable; It uses full table scans before statistics, and needs to read more than 6,000 blocks of data (one block is 8k), After the statistics is done, the INDEX (FAST full SCAN) is used, and only 450 blocks of data need to be read. 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, Oracle The more likely you are not to use index. (The number of blocks that a full table scan needs to read is equal to the actual number of blocks in the table) at its core, CF may be larger than the actual number of 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; The two seemingly similar SQL statements to the SELECT * from Mytables where myid>=400000, for Oracle, have 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 executing on HP: The first sentence:sql> select * from Mytables where myid>=1; has selected 325917 rows. 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 CALLS89 db block gets41473 consistent gets19828 PhysicalReads0 Redo size131489563 Bytes sent via sql*net to client1760245, bytes via received from Sql*net client21729 sql*net Dtrips To/from client1 Sorts (memory) 0 sorts (disk) 325917 rows processed 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) O F ' Pk_mytables ' (UNIQUE) (cost=5 card=663) Statistics----------------------------------------------------------1278 Recursive CALLS0 db block gets6647 consistent gets292 physical reads0 redo size3544898 Bytes sent via sql*net to client426 Bytes received via sql*net from client524 sql*net roundtrips to/from client1 sorts (memory) 0 sorts (disk) 7838 rows proc Essed 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, index also has 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 significance 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. The so-called index is good or bad refers to: 1, the index is not the more the better. In particular, a large number of never or almost no index, only damage to the system. OLTP systems degrade performance with more than 5 indexes per table, and Oracle cannot use more than 5 indexes in one SQL. 2, many times, a single-column index is less efficient than a composite index. 3, the fields used for multiple table links, plus the index can be useful. So, under what circumstances is a Single-column index less efficient than a composite index? It is obvious that when the columns queried by the SQL statement all appear in the composite index, it is much quicker than using multiple single-column indexes that Oracle only needs to query the index block to get all of the data. (At this point, this optimization is called Index only access Path) and beyond that? Let's take a look at an example: on HP (Oracle 8.1.7) Execute the following statement: SELECT COUNT (1) from Mytabs where coid>=130000 and IssueDate >= to_date (' 200 1-07-20 ', ' yyyy-mm-dd '). In the beginning, we have two Single-column indexes: I_MYTABS1 (coid), I_MYTABS2 (IssueDate), and here is the execution: COUNT (1)----------6427Execution 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 (RANG E SCAN) of ' I_MYTABS2 ' (non-unique) (cost=11card=126) Statistics----------------------------------------------------------172 recursive CALLS1 db block gets5054 consistent gets2206 Reads0 Redo size293 Bytes sent via sql*net to client359-bytes via received from Sql*net Client2 sql*net roundtrips Client5 sorts (memory) 0 sorts (disk) 1 rows processed you can see that it reads 7,000 blocks of data to get more than 6,000 rows of queries. Now, remove the two Single-column indexes, add a composite index i_mytabs_test (coid, IssueDate), and rerun the results as follows: COUNT (1)----------6436Execution 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 CALLS5 db block gets283 consistent gets76 Physical reads0 Redo size293 Bytes sent via sql*net to client359 bytes by received via sql*net from Client2 sql*net s to/from client3 sorts (memory) 0 sorts (disk) 1 rows processed you can see that only 300 blocks of data were read this time. 7000 pairs of 300 blocks, which is in this example, a Single-column indexThe cost of the composite index. 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, do not have to throw away the previous said, suppose you set a very good index, any fool knows should use it, but Oracle is not, then, the first thing to do is to look at your SQL statements. Oracle wants to use an index, there are some basic conditions: 1, this field in the WHERE clause must be the first field of the composite index; 2, the field in the WHERE clause should not be involved in any form of computation, suppose an index is established in the order of F1, F2, F3, There is now an SQL statement where the 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 1,2 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, F12, F1 is null, F1 are NOT NULL, F1 not in, F1!=, F1 like '%pat tern% '; 3, not exist 4, and in some cases F1 in will not be indexed; For these operations, there is no alternative but to try to avoid them. 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 is reasonable to write a complex SQL, before writing it to the application, in the product databaseTo do a explain at once. 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.