Differences between full table scan and index Scan

1) Full Table scan (Full Table Scans, FTS)
To scan the entire table, Oracle reads all rows in the table and checks whether each row meets the WHERE restrictions of the statement. Oracle reads each data block allocated to the table sequentially until it reads the highest waterline of the table (high water mark, HWM, marking the last data block of the table ). One multi-block read operation enables I/O to read multiple data blocks at a time (the db_block_multiblock_read_count parameter setting), instead of reading only one data block, this greatly reduces the total number of I/O operations and increases the throughput of the system. Therefore, the full table scan can be very efficient using multiple read methods, in addition, multiple read operations can be used only when full table scan is performed. In this access mode, each data block is read only once. Because HWM identifies the last piece of data to be read, and the delete operation does not affect the HWM value, after all the data in a table is deleted, the full table scan time will not be improved, generally, we need to use the truncate command to classify the HWM value as 0. Fortunately, after oracle 10 Gb, The HWM value can be manually reduced.
The data read in the FTS mode is placed at the end of the high-speed cache Least Recently Used (LRU) list so that it can be quickly exchanged out of memory, this prevents important data in the memory from being swapped out of the memory.
Prerequisites for using FTS: full table scanning is not recommended for large tables unless a large amount of data is retrieved, exceeding 5% -- 10% of the total amount, or you want to use the parallel query function.
Example of using full table scan:
~~~~~~~~~~~~~~~~~~~~~~~~
SQL> explain plan for select * from dual;
Query Plan
-----------------------------------------
Select statement [CHOOSE] Cost =
TABLE ACCESS FULL DUAL
2) Access through a ROWID Table (Table Access by ROWID or rowid lookup)
The ROWID of a row indicates the location of the data file, data block, and row in the row. Therefore, you can use ROWID to access the data and quickly locate the target data, it is the fastest way for Oracle to access a single row of data.
To access a table through ROWID, Oracle first needs to obtain the ROWID of the selected row, or obtain it from the WHERE clause of the statement, or by scanning one or more indexes of the table. Oracle then locates each selected row based on the obtained ROWID.
This access method does not use multiple read operations, and only one data block can be read at a time. We often see this access method in the execution plan, such as querying data through indexes.
Method of ROWID access:
SQL> explain plan for select * from dept where rowid = 'aaaaygaadaaataaf ';
Query Plan
------------------------------------
Select statement [CHOOSE] Cost = 1
Table access by rowid dept [ANALYZED]
3) Index Scan (index Scan or Index lookup)
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 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. 23
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. 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.

Author: "Soy Milk"