Full scan access method: (1) how to select full scan

Generally, there are only two basic data access methods: Full scan or index scan. During the full scan, multiple blocks are read into one Io operation. Index scanning first scans the leaf blocks of the index to obtain specific row IDs, and then uses these row IDs to access the parent table to obtain actual data.

Full scan access method: When scanning an object, all data blocks related to the object must be retrieved and processed, to determine whether the data rows contained in the block are required for your query, Oracle must read the entire data block to the memory to obtain the data of the data rows stored in the block. When a query needs to return the vast majority of data rows in the table, full scan is certainly the most likely.

How to perform full scan:

If a query returns a small number of rows, full table scan is also selected.

Create two test tables:

Eg:

Create Table T1
Select trunc (rownum-1)/100) ID,
Rpad (rownum, 100) t_pad
From dba_source
Where rownum: <= 10000;

Create index t1_idx1 on T1 (ID );

Exec dbms_stats.gather_table_stats (user, 't1', method_opt => 'for all columns size 1', cascade => true );

Create Table T2
Select Mod (rownum, 100) ID,
Rpad (rownum, 100) t_pad
From dba_source
Where rownum: <= 10000;

Create index t2_idx1 on T2 (ID );

Exec dbms_stats.gather_table_stats (user, 't2', method_opt => 'for all columns size 1', cascade => true );

For T1 and T2, there are 10000 rows of data, and the ID columns in the two tables have 100 rows for each value ranging from 0 to 99. Therefore, from the perspective of data content, the two images are the same. But for T1, the ID column is calculated through trunc (rownum-1)/100. The t2id column is calculated by MoD (rownum, 100. Displays the storage status of data blocks.

Run the aggregate function:

Select count (*) CT from T1 where id = 1;

Select count (*) CT from T2 where id = 1;

Suppose we want to find the value of ID = 1. We know that there are 100 rows in the table, and the ratio of selection is 10000 = 100/10000. We know that this ratio is very small, the optimizer should use indexes to obtain data, because we have already created indexes in the ID column, but at this time, we should know how the data is stored. If the data is stored in sequence, most rows with ID = 1 are physically stored in only a few data blocks. In this example, T1 is correct. (using indexes)

Why didn't the optimizer select the same explain Plan for the two queries? This is because the data in these two tables is stored differently. For query of table T1, Oracle only needs to access a few data blocks to obtain the required 100 rows of data. Therefore, index is the most attractive option. However, because the data rows of table T2 are physically scattered and stored in all data blocks of the table, for its query, it needs to read almost all data blocks to obtain the same 100 rows of data. the optimizer calculates that it may take longer to use indexes to read each data block in a table than to directly use full-Table scans to read all data blocks.