Databases Reference
In-Depth Information
In step 14, we query some data from the database dynamic performance views for full table
scans executed on the database, obtaining the following result:
You can see from the results that they are divided between table scans on short tables and
table scans on long tables; let's see why.
When the database executes a Full Table Scan (FTS) operation, it reads all the database
blocks of a table to retrieve the data needed. This operation is often undesirable in OLTP
databases, because if we have to scan a very large table to retrieve the desired data, there
is most often something wrong with the access paths of data in the database.
It's better to use a Full Table Scan when we have small tables. You should follow this advice
because it may be more expensive to read an index entry and the corresponding data instead
of reading the full table. The first operation (index + data) reads a minimum of two database
blocks, one for the index and one for the data. Reading the full table, instead, is done by
reading only the table data, which could be only one database block in size. Hence, there is
a difference in the dynamic performance view between short tables and long tables.
The Full Table Scan operation has two problems related to performance; the first one is
obvious, many I/O operations are needed to perform the FTS to retrieve the blocks. The
second flaw is related to the buffer cache; if we have a well-tuned application, when we
execute a query it is better to find the data we need in the buffer cache, avoiding I/O
operations. If we do an FTS, database buffers are used to read all the table data, and this
situation may lead to flushing the buffer cache data to make room for the FTS data. To avoid
this situation and to limit the consequences on the database buffer cache, the database
blocks from FTS operations are put on the top of the LRU (Least Recently Used) list. This list
is used to keep track of the database buffers, deciding if they are good candidates to be
flushed. These candidates will be flushed before the database blocks the ones in use by
other kinds of operations in the database.
