Clarify the relationship between full table scanning, high water level, and row prefetch

I used to write a blog post to tease me about full table scan. Today, I continue to feel that I am not familiar with full table scan.

(I) Full table scan and hwm

In table access full, the database engine reads all data blocks of the table in hwm sequentially.
Full table scan may not be ideal, especially when a table contains a large number of empty or near-empty blocks.
Obviously, a block must be read before it can know whether it contains data.
When a table has more delete operations than insert operations, it is most likely that the table contains many sparse data blocks.
Therefore, logical reads depend on the number of blocks rather than the number of rows.
The test is as follows:

HR @ orcl> select/* + full (t) */* from t where object_id = 51; Execution Plan -------------------------------------------------------- plan hash value: 1601196873 bytes | ID | operation | Name | rows | bytes | cost (% CPU) | time | bytes | 0 | SELECT statement | 1 | 87 | 160 (2) | 00:00:02 | * 1 | table access full | T | 1 | 87 | 160 (2) | 00:00:02 | -------------------------------------------------------------------------- HR @ orcl> set autot trace stathr @ orcl> select * from t where object_id = 51; statistics defaults 0 recursive cballs 0 dB block gets 698 consistent gets 335 physical reads 0 redo size 1199 bytes sent via SQL * Net to client 385 bytes received via SQL * Net From Client 2 SQL * Net roundtrips to/from client 0 sorts (memory) 0 sorts (Disk) 1 rows processedhr @ orcl> Delete t where object_id <> 51; 50321 rows deleted. HR @ orcl> select * from t where object_id = 51; -- in this case, invalid access to many completely empty blocks will be performed to statistics defaults 0 recursive cballs 0 dB block gets 698 consistent gets --- the logical reads required for the query have not changed 0 physical reads 0 redo size 1199 bytes sent via SQL * Net to client 385 bytes encoded ed via SQL * Net From Client 2 SQL * Net roundtrips to/from client 0 sorts (memory) 0 sorts (Disk) 1 rows processed -- to reduce the high water level line, it is necessary to reorganize the table HR @ orcl> alter table t enable row movement; -- Row migration must be activated first, table altered is generated due to a new rowid in the reorganization process. HR @ orcl> alter table t shrink space; Table altered. HR @ orcl> select * from t where object_id = 51; statistics limit 179 recursive cballs 0 dB block gets 26 consistent gets -- the logical read required for the query is changed to 26 5 physical reads 0 redo size 1199 bytes sent via SQL * Net to client 385 bytes encoded ed via SQL * Net From Client 2 SQL * Net roundtrips to/from client 4 sorts (memory) 0 sorts (Disk) 1 rows processed

(Ii) full table scan and row prefetch

Full table scan logic reading relies heavily on Row prefetch settings
The following think will share with you the impact of row prefetch on the logical reads of full table scans.
In short, each data block accessed by the database engine generates a logical read
Full table scan has two extremes:
If the row prefetch is set to 1, each returned row is equivalent to one logical read.
If the row prefetch setting is greater than the number of rows in a single block storage for each table, the number of logical reads is close to the number of blocks in the table.
Test:

HR @ orcl> edwrote file afiedt. buf 1 select num_rows, blocks, round (num_rows/blocks) as rows_per_block 2 from user_tables 3 * Where table_name = 't'hr @ orcl>/num_rows blocks rows_per_block ------------ -------------- 50323 712 71 -- t table each block has 71 rows HR @ orcl> set autot trace stathr @ orcl> set arraysize 2hr @ orcl> select * from T; 50323 rows selected. statistics limit 1 recursive cballs 0 dB block gets 25532 consistent gets 0 physical reads 0 redo size 8236771 bytes sent via SQL * Net to client 277156 bytes encoded ed via SQL * Net from client 25163 SQL * Net roundtrips to/from client -- [total number of returned rows/arraysize] + 1 0 sorts (memory) 0 sorts (Disk) 50323 rows processedhr @ orcl> set arraysize 3000hr @ orcl> select * from t; 50323 rows selected. statistics defaults 0 recursive cballs 0 dB block gets 714 consistent gets 0 physical reads 0 redo size 4967955 bytes sent via SQL * Net to client 561 bytes received via SQL * Net from client 18 SQL * Net roundtrips to/from client 0 sorts (memory) 0 sorts (Disk) 50323 rows processed

When the row prefetch is set to 2, the number of logical reads (25532) is about half of the number of rows (50323 ).
When the row prefetch setting is higher than the average number of rows in each block (71), the number of logical reads (714) is close to the total number of logical reads (712 ).