Oracle Performance Analysis 6: Index scanning of data access methods

This section describes the various index scanning methods, and after understanding the characteristics of the various index scanning methods, you can determine whether the scanning method used in your execution plan is correct, and can make improvements to the information obtained.

Index unique scan

When an equality condition is used in the following scenario, the database uses an index unique scan.
1) When all columns in a unique index are included in the query condition;
2) When a query condition uses a PRIMARY KEY constraint column.
Here is a practical example of creating the following unique index in table Historyalarm:

Create unique index Idx_historyalarm$queryid on historyalarm (Queryid) tablespace Uep4x_fm_index

Then execute the query on the table:

SELECT * from historyalarm where Queryid = 3

The query conforms to the first case above and uses an index unique scan, which executes the following plan:

Descriptionobject NAME-----------------------------------------------------------------------SELECT STATEMENT, GOAL = All_rows TABLE ACCESS by GLOBAL index rowidhistoryalarm  index UNIQUE Scanidx_historyalarm$queryid

Here Oracle first finds the index node through a unique index scan, and then uses the ROWID contained in the index node to access the data in the table.

Index range Scan

An index range scan is used when a query condition may return a range of data, which can be either a unique index or a non-unique index, but can also cause a full table scan if the query criteria contain too large a range of data. Using <, >, like, between, =, and so on in query conditions may use index range scanning, note that a single = condition on a unique index or primary key will cause the index to scan uniquely.
Here is an example of an index range scan that performs a range query in the histroyalarm above:

SELECT * from Caffm4x.historyalarm where Queryid < 10

The data queried here is a range and uses the Queryid column, which has a unique index on the Queryid column, but still causes the index range to be scanned:

Descriptionobject NAME-----------------------------------------------------------------------SELECT STATEMENT, GOAL = All_rows TABLE ACCESS by GLOBAL index rowidhistoryalarm  index RANGE Scanidx_historyalarm$queryid

Index range scan from the root node of the index, find the first matching entry of the leaf data block begins to traverse the index structure, first remove the ROWID from the index entry and then take out the corresponding table data block (through ROWID access to the data table), The leaf index block is then accessed again and reads the next index entry and gets ROWID, so that the index entries are read out until the entire leaf index block is retrieved. Therefore, to exclude the index root node and the intermediate node, each row of data read need to read two data blocks, we can get the index height through the blevel, through the index height and the number of data rows to get the number of data required to read, for example: If Blevel is 3, read 5 rows of data, The total number of data blocks that need to be accessed will be (5*2) + 3 = 13 (Note that only the root node is blevel 0).
If, after reading the entire leaf index block, you also need to access the next leaf index block, there is a pointer to the next leaf index block in the current Leaf index block (also contains a pointer to the previous leaf index block).
Another advantage of using an indexed range scan is sorting, because the indexed nodes are ordered, so if the results of the query need to be sorted by indexed columns (ascending or descending), then using an index range scan can be a good way to avoid sorting operations such as:

SELECT * from Historyalarm where Queryid > 10

The Oracle Optimizer chooses a full-table scan because the amount of data that is greater than 10 for Queryid is 99% of the total.

Descriptionobject NAME-----------------------------------------------------------------------SELECT STATEMENT, GOAL = all_rows PARTITION RANGE all  TABLE ACCESS fullhistoryalarm

If we specify the sort of data at query time, as follows:

SELECT * from Historyalarm where Queryid > Ten ORDER by Queryid

The implementation plan is as follows:

Descriptionobject NAME-----------------------------------------------------------------------SELECT STATEMENT, GOAL = All_rows TABLE ACCESS by GLOBAL index rowidhistoryalarm  index RANGE Scanidx_historyalarm$queryid

The optimizer changed to use an index range scan. Because the cost of sorting is large when the amount of data is large (which can lead to physical sorting), using an indexed range scan is a good choice, especially if you select some data after sorting (RowNum < n).

Index full scan

The index full scan reads all entries on the index, and the following conditions may cause the index to be fully scanned:

1) There is no condition but the list of desired columns can be obtained by the index of one of the columns;

Select ID from T3descriptionobject NAME-----------------------------------------------------------------------SELECT STATEMENT, GOAL = All_rows INDEX Full scanidx_t3_id

Because the ID column is indexed, the optimizer selects the index full scan.

2) The query condition contains a sort operation

SELECT * from Historyalarm ORDER by Queryiddescriptionobject NAME-----------------------------------------------------------------------SELECT STATEMENT, GOAL = All_rows INDEX Full Scanidx_historyalarm$queryid

The index full scan reads a single block of data, reads the ROWID of each entry, and then fetches the data rows through ROWID, so the sort operation is not necessary because the index is already sorted. If the query requests only indexed columns, the database skips table access and only accesses the index to get the data.
Another advantage of index full scan is when calculating the maximum and minimum values:

Select min (Queryid) from Historyalarm descriptionobject NAME-----------------------------------------------------------------------SELECT STATEMENT, GOAL = All_rows SORT AGGREGATE  INDEX full SCAN (Min/max) Idx_historyalarm$queryid

Because the index itself is sorted, it only takes a small cost to calculate the maximum minimum value.

Index Hop Scan

An index hop scan can be caused when the query condition has a column that conforms to the index but does not contain a leading column. The database splits a composite index into multiple logical sub-indexes, and matching the different values of the index's leading columns determines the number of logical sub-indexes, that is, the fewer the different values of the leading column, the better the performance of the index hop scan.

Select value from t3 where value = ' Test ' Descriptionobject NAME-----------------------------------------------------------------------SELECT STATEMENT, GOAL = All_rows INDEX SKIP Scanidx_t3_combine

Index Fast Full Scan

Oracle performs an index fast full scan when the index itself contains all the columns specified in the query. The difference between index fast full scan and index full scan is that the index full scan uses a single block read operation, while the index fast full scan uses multiple blocks of read. This scan cannot be used to avoid sorting, because the block is read by an unordered number of read-more blocks.

Select Queryid from Historyalarmdescriptionobject NAME-----------------------------------------------------------------------SELECT STATEMENT, GOAL = All_rows INDEX FAST Full Scanidx_historyalarm$queryid

Oracle Performance Analysis 6: Index scanning of data access methods