RelationalDBDesign RelationalDBDesign


Data Buffer Cache   «Prev 

Full Table Scan Data Block

Oracle Table Cache Directive

A scan of table data in which the database sequentially reads all rows from a table and filters out those that do not meet the selection criteria. All data blocks under the high water mark are scanned.

Index Clustering Factor

For a B-tree index, the index clustering factor measures the physical grouping of rows in relation to an index value, such as last name. The index clustering factor helps the optimizer decide whether
  1. an index scan or
  2. full table scan
is more efficient for certain queries. A low clustering factor indicates an efficient index scan. A clustering factor that is close to the number of blocks in a table indicates that the rows are physically ordered in the table blocks by the index key.
If the database performs a full table scan, then the database tends to retrieve the rows as they are stored on disk sorted by the index key. A clustering factor that is close to the number of rows indicates that the rows are scattered randomly across the database blocks in relation to the index key. If the database performs a full table scan, then the database would not retrieve rows in any sorted order by this index key.
The clustering factor is a property of a specific index, not a table. If multiple indexes exist on a table, then the clustering factor for one index might be small while the factor for another index is large. An attempt to reorganize the table to improve the clustering factor for one index may degrade the clustering factor of the other index.

Full table scan data block

Full table scan data progress bar moves towards LRU

Data block is in area LRU, which is reserved for full table scan data blocks

LRU is in the stage age out, and the data block returns to the MRU