Oracle Data access path index Scanning

There are only two basic data access methods in oracle: Full scan or index scan. The default index type is B-tree index. An index is created on one or more columns or columns in a table.

There are only two basic data access methods in oracle: Full scan or index scan. The default index type is B-tree index. An index is created on one or more columns or columns in a table.

There are only two basic data access methods in Oracle: Full scan or index scan.

The default index type is B-tree index. The index is created on one or more columns or column expressions in the table, and the column value and row number (ROWID) are stored together. Some other information is stored in the index, but you only need to care about the column value and row number. A row number is a pseudo column that uniquely identifies a row in a table. It is the internal address of the row data in the physical table. It contains two addresses. One is the address pointing to the data file that contains the block of the row in the data table, the other is that you can directly locate the address of the row in the data block. For more information, see rowid.

That is to say, for each row obtained through index scanning, at least two data blocks must be accessed: at least one index block and one data block.

Index scan types include: index range scan, unique index scan, full index scan, index skip scan, and index quick scan.

The index clustering factor statistics help the optimizer to generate cost information using the index, and are a measure of the data sorting excellence of the index created in the table. The clustering factor of the index indicates to the optimizer whether the data rows with the same index value are stored in the same or continuous series of data blocks, or whether the data rows are scattered in multiple data blocks of the table. See notes on clustering factor.

Note: Clustering factors are related to table data rather than indexes. Therefore, re-indexing has no effect on it.

UNIQUE INDEX SCAN)

When the predicate contains columns that use the unique or primary key index as the condition, the unique index scan is used. This type of index ensures that only one row of data is returned for a specific value.

INDEX RANGE SCAN)

If the predicate contains conditions that will return a certain range of data, index range scanning is used. The larger the range, the more likely it will be replaced by the Full scan operation. In some cases, you think index range scanning should be used, but it is not actually used. For example, if you use a condition starting with a wildcard such as the LIKE operator of '% abc', the optimizer will not use the index range scan on the column because the condition is too broad. Another similar case is that when you use a combination index, you have a predicate for a non-Bootstrap column. In this case, index skip scanning is more likely to be used.

Note: The last note of index range scanning is that it can use an index in ascending order (default) to return data rows in descending order.

INDEX FULL SCAN)

In several cases, full index scanning will be selected, including: the list of columns to be obtained without a predicates can be obtained through the index of one of the columns, the predicate contains a condition in the non-Bootstrap column of the index, or the data can be obtained through an index with an ascending order, which saves individual sorting steps. The full index scan operation scans each leaf block in the index structure, reads the row numbers of each entry, and retrieves the data rows. Each leaf block must be accessed. This is usually more efficient than full table scanning, because each index block contains more entries than the table data block, and thus the total number of accessed blocks is reduced accordingly. When you query all fields in the field list as part of an index entry, you can skip the table access step. This means that using full index scanning is more efficient than reading data blocks from all tables.

Note: Full index scanning can also read data in descending order (in ascending order) to avoid separate sorting. There is another Optimal Method for full index scanning. When you need to query the minimum or maximum value of a column and this column is an index column.

INDEX SKIP SCAN)

If the predicate contains the condition of the non-Bootstrap column in the index and the value of the bootstrap column is unique, the index skip scan is selected. In earlier versions of ORACLE, if a predicate uses a non-Bootstrap column in the index, the index cannot be used. In Versions later than 9, index skip scanning has been introduced.

Fast and full index Scanning

Quick full index scanning is more like full table scanning than other types of index scanning. When an index is selected for quick and full scanning, all index blocks are read by multiple blocks. This type of index scan is used to replace full table scan when all fields in the query list are included in the index and at least one column in the index has non-null constraints. In this case, data is accessed through indexes instead of table data blocks. Unlike other index scan types, quick full index scan cannot be used to avoid sorting, because data blocks are read from unordered multiple tables.

In short, the index quick full scan operation depends on non-empty constraints. If there is no such constraint, full table scan will be selected.