Master talk about Oracle index Scanning

Oracle is still quite common, So I studied Oracle index scanning and shared it with you here. I hope it will be useful to you. We first find the corresponding rowid value through the index (multiple rowid values may be returned for non-unique indexes), and then obtain specific data from the table based on the rowid, this search method is called Oracle index scan or index lookup ). A rowid uniquely represents a row of data. The data block corresponding to the row is obtained through I/o. In this case, this time I/o reads only one database block.

In the index, in addition to storing the value of each index, the index also stores the ROWID value corresponding to the row with this value. Oracle index scanning can be composed of two steps:
(1) scan the index to obtain the corresponding rowid value.
(2) read the specific data from the table by finding the rowid.

Each step is an independent I/O, but for indexes that are frequently used, most of them have been cached in the memory, so I/O in step 1 is often a logical I/O, that is, the data can be obtained from the memory. However, for step 1, if the table is large, the data cannot be in the memory, so its I/O may be physical I/O, which is a mechanical operation, it is extremely time-consuming for logical I/O. Therefore, if index scanning is performed on multiple large tables and the retrieved data is greater than 5%-10% of the total data, the efficiency of index scanning is greatly reduced.

See the following:

 
 

  
  
SQL> explain plan for select empno, ename from emp where empno=10;  
  
  
Query Plan  
  
  
SELECT STATEMENT [CHOOSE] Cost=1 
  
  
TABLE ACCESS BY ROWID EMP [ANALYZED]  
  
  
INDEX UNIQUE SCAN EMP_I1 
 
 

Note that table access by rowid emp indicates that data is accessed not through the fts access path, but through the rowid lookup ACCESS path. In this example, the required rowid is worth finding the empno column in the INDEX. This method is index unique scan, which will be introduced later, EMP_I1 is the index name used for index search.

However, if all the queried data can be found in the index, you can avoid Step 1 Operations and unnecessary I/O operations. Even if the Oracle index scans a large amount of data, the efficiency is still very high, because this will only be read in the index. So when I introduced the rule-based optimizer above, I used select count (id) from SWD_BILLDETAIL where cn <'6' instead of select count (cn) from SWD_BILLDETAIL where cn <'6 '. In practice, only the value of the indexed column is rarely queried, so it is not representative if I use count (cn) in the query.

 
 

  
  
SQL>Explain plan for select empno from emp whereEmpno=10; -- Query only the values of the empno Column
  
  
Query Plan
  
  
Select statement [CHOOSE]Cost=1 
  
  
Index unique scan EMP_I1
 
 

Further, if the index columns are sorted in SQL statements, you do not need to sort the index columns in the execution plan because the indexes are pre-sorted.

 
 

  
  
SQL> explain plan for select empno, ename from emp  
  
  
where empno > 7876 order by empno;  
  
  
Query Plan  
  
  
SELECT STATEMENT [CHOOSE] Cost=1 
  
  
TABLE ACCESS BY ROWID EMP [ANALYZED]  
  
  
INDEX RANGE SCAN EMP_I1 [ANALYZED] 
 
 

In this example, we can see that, because the index is sorted, rows that meet the criteria are queried in the order of the index, so further sorting is avoided.

1. Code Description Oracle Selectivity
2. Oracle management tool-Overview of Oracle SQL Handler Functions
3. The US Department of Justice publicly supports Oracle's acquisition of Sun
4. About the sequence of Oracle execution plans
5. Six-minute Oracle full table Scan