Three issues with Oracle indexes

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; 和
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:

First sentence:

SQL> select * from mytables where myid>=1;

325917 rows have been 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 client
21729 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
325917 rows processed