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

Clarify the relationship between Full Table scans and high levels and row prefetch (I) between Full Table scans and HWM www.2cto.com in Table Access Full, the database engine may not be ideal enough to scan all data blocks in the HWM table sequentially, especially when the table contains a large number of empty or near-empty blocks, 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, the logical read depends on the number of blocks rather than the number of rows. The test is as follows: [SQL] 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 | latency | 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 stat hr @ 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 processed hr @ ORCL> delete t where object_id <> 51; 50321 rows deleted. hr @ ORCL> select * from t where object_id = 51; -- in this case, access to www.2cto.com Statistics blocks 0 recursive cballs 0 db block gets 698 consistent gets will be invalid for many completely empty blocks --- 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 the logic reading of www.2cto.com full table scan is heavily dependent on the setting of row prefetch. Below Think will share with you the logic reading of row prefetch for full table scan. in short, each time the database engine accesses a data block, a logical read is generated. For full table scanning, there are two extremes: If the row prefetch is set to 1, each returned row is equivalent to a 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 similar to the number of blocks in the table: [SQL] hr @ ORCL> ed Wrote 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 stat hr @ ORCL> set arraysize 2 hr @ 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 processed hr @ ORCL> set arraysize 3000 hr @ 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 the number of rows (50323) half of the rows. When the row prefetch setting is higher than the average number of rows in each block (71), the number of logical reads (714) and the total number of blocks (712) are close