Table Access----> Full table Scan (fully table Scans, FTS)

Full table Scan (Scans, FTS)

Full-table scanning means that when Oracle accesses the data in the target table, it starts scanning from the first block (block) of the first area occupied by the table, scanning to the table's high watermark (Hwm,high water Mark), EXTENT Oracle imposes the filter criteria specified in the Where condition of the target SQL for all data read during this period, and finally returns only those data that satisfy the filtering criteria.

Not that the full table scan is not good, in fact, Oracle in the full table scan operation will use a multi-block read,Oracle takes one read into multiple data blocks (database block) to optimize the full table scan , rather than just read a block of data, which greatly reduces the i/ o Total number of times, improve the system throughput, so the use of multi-block read method can be very efficient to achieve full-table scanning. This is very efficient when the data volume of the target table is small, but the biggest problem with the full table scan is that the execution time of the target SQL for the full table scan is unstable and not controllable, and this execution time must be incremented with the amount of data in the target table . Because as the target table data increases, its high watermark continues to rise, so the number of data blocks that need to be read when the table is scanned is increasing, which means that the I/O resources that are required to scan the table in the full table will grow. Of course, the time it takes to complete a full table scan of the table will also increase.

In Oracle, if you keep inserting data into the target table, the high watermark moves up when there is not enough existing space assigned to the table, but if you delete the data from the table with a DELETE statement, the high watermark does not move downward (which in some way fits the definition of "high watermark". Like the water level of the reservoir, when the reservoir swollen, the water level will move up, when the reservoir water, once the highest level of traces will be clearly visible). The side effect of this feature of the high watermark is that even if all the data in the target table is deleted by using the DELETE statement, the high watermark will still be in its original position, which means that Oracle still needs to scan all blocks of the table's high watermark when the table is scanned. Therefore, the time spent on the table's full-table scan operation will not be significantly improved compared to the previous one.

Prerequisites for using FTS: A full table scan is not recommended on larger tables, unless the data is fetched more than 5%-10% of the total, or when you want to use the parallel query feature.

Example

Test with Scott's EMP table

SYS@PDBORCL> Altersystem flush Shared_pool; SYS@PDBORCL> Altersystem flush Buffer_cache; SYS@PDBORCL>Conn Scott/Tiger@pdborclConnected. The session has changed. SCOTT@PDBORCL> Setautotrace Traceonlyscott@PDBORCL> Select *  fromEMP, selected -line. Execution Plan----------------------------------------------------------PlanHash value:3956160932--------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost (%CPU)|Time|--------------------------------------------------------------------------|   0 | SELECTSTATEMENT|      |     - |   532 |     3(0)| xx:xx: on ||   1 |  TABLEACCESS Full|Emp|     - |   532 |     3(0)| xx:xx: on |--------------------------------------------------------------------------Statistical Information----------------------------------------------------------          theRecursive calls0db block gets98consistent gets -Physical reads0Redo Size1647Bytes sent via SQL*Net toClient544Bytes received via SQL*Net fromClient2Sql*Net roundtrips to/ fromClient8sorts (memory)0Sorts (Disk)          -rows Processedscott@PDBORCL> Select *  fromEMP, selected -line. Execution Plan----------------------------------------------------------PlanHash value:3956160932--------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost (%CPU)|Time|--------------------------------------------------------------------------|   0 | SELECTSTATEMENT|      |     - |   532 |     3(0)| xx:xx: on ||   1 |  TABLEACCESS Full|Emp|     - |   532 |     3(0)| xx:xx: on |--------------------------------------------------------------------------Statistical Information----------------------------------------------------------          0Recursive calls0db block gets8consistent gets0Physical reads0Redo Size1647Bytes sent via SQL*Net toClient544Bytes received via SQL*Net fromClient2Sql*Net roundtrips to/ fromClient0sorts (memory)0Sorts (Disk)          -rows Processedscott@PDBORCL>

View Code

From the query plan we can see that the query used is "TABLE ACCESSfull",

Execute again

Reference

Http://book.51cto.com/art/201312/422337.htm

Table Access----> Full table Scan (fully table Scans, FTS)