Oracle performance analysis 6: Index scanning of data access methods, oracle Index

Oracle performance analysis 6: Index scanning of data access methods, oracle Index

This section describes various index scanning methods. After learning about the features of various index scanning methods, you can determine whether the scanning methods used in your execution plan are correct, you can also make improvements to the obtained information.

Unique index Scan

When equal conditions are used in the following scenarios, the database uses unique index scans.
1) when the query condition contains all columns in the unique index;
2) When the query condition uses the primary key to constrain the column.
The following is an actual example. Create the following unique index in the historyalarm table:

create unique index idx_historyalarm$queryid on historyalarm(queryid) tablespace uep4x_fm_index

Then execute the query in the table:

select * from historyalarm where queryid = 3

The query meets the first condition above and uses unique index scanning. The execution plan of the query is as follows:

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

When the query condition may return a certain range of data, index range scanning is used. The index can be a unique index or a non-unique index. However, if the query condition contains a large data range, it may also cause a full table scan. You can use <,>, LIKE, BETWEEN, and = in the query condition to scan the index range. Note that a single = condition causes a unique index scan on the unique index or primary key.
The following is an example of an index range scan. In the above histroyalarm, a Range Query is executed:

select * from caffm4x.historyalarm where queryid < 10

The data queried here is a range, and the queryid column is used. There is a unique index in the queryid column. However, any query results in index range scanning:

DESCRIPTIONOBJECT NAME-----------------------------------------------------------------------SELECT STATEMENT, GOAL = ALL_ROWS TABLE ACCESS BY GLOBAL INDEX ROWIDHISTORYALARM  INDEX RANGE SCANIDX_HISTORYALARM$QUERYID

The index range scan starts from the root node of the index, finds the leaf data block where the first matched entry is located, and then traverses the index structure, first, retrieve the rowid from the index entry and then obtain the corresponding table data block (access the data table through rowid). Then, the leaf index block will be accessed again, read the next index entry, and obtain the rowid, this is repeated until all the index entries in the entire leaf index block are read. Therefore, the root and intermediate nodes of the index are excluded. Two data blocks need to be read for each row of Data Reading. We can use blevel to obtain the index height, the index height and the number of data rows can be used to obtain the number of data blocks to be read. For example, if the blevel is 3, five rows of data are read, the total number of data blocks to be accessed is (5*2) + 3 = 13 (note that blevel is 0 only for the root node ).
If you still need to access the next leaf index after reading the entire leaf index block, in the current leaf index block, there is a pointer to the next leaf index block (also contains a pointer to the previous leaf index block ).
Another advantage of index range scanning is sorting. Because the index nodes are ordered, if the query results need to be sorted by index column (ascending or descending ), using index range scanning can effectively avoid sorting operations, such:

select * from historyalarm where queryid > 10

Because queryid is greater than 10 of the total data size, Oracle optimizer selects full table scan:

DESCRIPTIONOBJECT NAME-----------------------------------------------------------------------SELECT STATEMENT, GOAL = ALL_ROWS PARTITION RANGE ALL  TABLE ACCESS FULLHISTORYALARM

If we specify the sorting for the data during the query, as follows:

select * from historyalarm where queryid > 10 order by queryid

The execution plan is as follows:

DESCRIPTIONOBJECT NAME-----------------------------------------------------------------------SELECT STATEMENT, GOAL = ALL_ROWS TABLE ACCESS BY GLOBAL INDEX ROWIDHISTORYALARM  INDEX RANGE SCANIDX_HISTORYALARM$QUERYID

The optimizer uses index range scanning instead. When the data volume is large, sorting costs a lot (which may lead to physical sorting). Using index range scanning is a good choice, especially when you select some data after sorting (rownum <n ).

Full index Scan

Full index scan reads all entries on the index. The following situations may cause full index scan:

1) No conditions, but the list of columns to be retrieved can be obtained through 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 has an index, the optimizer selects full index scanning.

2) query conditions include sorting operations

select * from historyalarm order by queryidDESCRIPTIONOBJECT NAME-----------------------------------------------------------------------SELECT STATEMENT, GOAL = ALL_ROWS INDEX FULL SCANIDX_HISTORYALARM$QUERYID

The full index scan reads a single data block, reads the rowid of each entry, and then extracts the data row through the rowid. Because the index has been sorted, you do not have to perform the sorting operation. If only the index column is requested for a query, the database skips table access and obtains data only by accessing the index.
Another advantage of full index scan is that when the maximum and minimum values are calculated:

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 has been sorted, it only takes a small cost to calculate the maximum and minimum values.

Index skip Scan

When a query condition contains a column that meets the index but does not contain a leading column, the index skip scan may occur. The database Splits a composite index into multiple logical sub-indexes, and determines the number of logical sub-indexes according to the different values of the index's leading column, that is, the fewer different values of the leading column, the better the performance of index skip scanning.

select value from t3 where value = 'test'DESCRIPTIONOBJECT NAME-----------------------------------------------------------------------SELECT STATEMENT, GOAL = ALL_ROWS INDEX SKIP SCANIDX_T3_COMBINE

Fast and full index Scanning

When the index itself contains all the columns specified in the query, Oracle performs an index quick full scan. The difference between quick full index scan and full index scan is that full index scan uses a single read operation, while Quick full index scan uses multiple reads. This kind of scanning cannot be used to avoid sorting, because data blocks are read by unordered multi-block reading.

select queryid from historyalarmDESCRIPTIONOBJECT NAME-----------------------------------------------------------------------SELECT STATEMENT, GOAL = ALL_ROWS INDEX FAST FULL SCANIDX_HISTORYALARM$QUERYID

 

 

What is the command used to perform Table Analysis and Index Analysis on Oracle database tables?

Analyze table name compute statistics
Analyze index ID compute statistics

If you want to analyze all the table names and index names, you can obtain relevant information from the views user_tables and user_indexes and generate an SQL command automatically.

Analysis of several cases where indexes are not used in Oracle

Oracle does not use B * tree indexes. The following figure shows the situation: 1. If the where condition is used and null is used, indexes may not be used. 2: count, sum, ave, max, min and other aggregation operations may result in no index 3: display or implicit function conversion, resulting in no index 4: In cbo mode, the statistical information is too old, resulting in no index 5: if no leading column is used in the composite index, the index is not used. 6: The accessed data volume exceeds a certain proportion. As a result, if the index is not used, Let's explain the following points: can Null be indexed? Generally, fulltablescan is caused by comparing null in the where condition. In fact, if the value of the index column in the table is null, therefore, this row does not exist in indexes (B * tree, bitmap index, or clustered Index). Therefore, to ensure the accuracy of the query structure, instead of index scan, full table scan is used. Of course, if an index column is defined as not null, in this case, no index column is empty, so in this case, you can use index scan. Therefore, if the where condition contains something similar to is null, = null, whether to take the index depends on whether a column in the index is defined as not null. The specific experiment is as follows: SQL> create table t (x char (3), y char (5); SQL> insert into t (x, y) values ('001 ', 'xxxxx'); SQL> insert into t (x, y) values ('002 ', null); SQL> insert into t (x, y) values (null, 'yyyy'); SQL> insert into t (x, y) values (null, null); SQL> commit; SQL> create unique index t_idx on t (x, y ); SQL> analyze table t compute statistics for table for all indexes; SQL> select blevel, leaf_blocks, num_rows from user_indexes where index_name = upper ('t_ idx '); BLEVEL LEAF_BLOCKS NUM_ROWS ---------- ----------- ---------- 0 1 3isnert four records, but the index only saves three records, the last one is not saved in the index SQL> set autotrace traceonly explain; SQL> select * from t where x is null; Execution Plan limit 0 SELECT STATEMENT ptimizer = CHOOSE (Cost = 2 Card = 1 Bytes = 8) 1 0 TABLE ACCESS (FULL) OF 'T' (Cost = 2 Card = 1 Bytes = 8) SQL> create table t1 (x char (3), y char (5) not null ); SQL> insert into t1 (x, y) values ('001', 'xx ...... remaining full text>