Shulou(Shulou.com)06/01 Report--

Scene of multi-block reading

Full Table Scan-full table scan

Index Fast Full Scans-- Fast full scan of index

The scene of single-block reading

Rowid Scans-get it directly through Rowid

Index Unique Scans-unique scan of the index

Index Range Scans-Index local scan

Index Skip Scans-Index Jump scan

Index Full Scans-Index full scan

Full table scan (Full Table Scans FTS)

In order to achieve full table scan, Oracle reads all rows in the table and checks whether each row satisfies the WHERE restriction of the statement. When scanning a full table, it can read multiple database blocks (db_file_multiblock_read_count parameter setting) instead of only one data block, that is, multi-block read, which greatly reduces the total number of db_file_multiblock_read_count O and improves the throughput of the system.

Index scan (Index Scan or index lookup)

We first find the corresponding rowid value of the data through index (multiple rowid values may be returned for non-unique indexes), and then get the specific data directly from the table according to rowid, which is called index scan or index lookup (indexlookup).

A rowid uniquely represents a row of data, and the corresponding data block for that row is obtained by an iUniver 0, in which case only one database block is read, that is, a single block read.

In an index, in addition to storing the value of each index, the index also stores the ROWID value of the row with this value.

An index scan can consist of 2 steps:

1 scan the index to get the corresponding rowid value.

2 read out the specific data from the table through the rowid found.

Each step is a separate iUnip O, but for indexes, because they are often used, most of them have been CACHE into memory, so the iUnip O in step 1 is often a logical iUnip O, that is, the data can be obtained from memory. But for step 2, if the table is large, its data cannot be all in memory, so its Icano is likely to be a physical IUnip O, which is a mechanical operation, which is extremely time-consuming compared to logical Imax O.

Why multi-block reads are faster than single-block reads, that is, why full table scans are sometimes faster than index scans

Because the physical unit of logical read is sub-block (if you read the same or different number of blocks at one time, it depends on how many times you read. Of course, there is no IO for logical reading, but only IO for physical reading). If the total number of blocks is the same, the number of reads from multiple blocks is less, logical or physical reads are less, and full table scanning is multi-block reading.

Personal understanding: an IO is an IO, regardless of multiple blocks, or a single block, is a secondary IO.

For example, if you buy a candy for 1 yuan, someone can buy 10 candies for 1 yuan, and the cost is actually 1 yuan.

For example, if you want to read 10 blocks in a second, if you read a single block, you need to read one block at a time, and if you read more than one block, you need to read 10 blocks at a time. Although the IO throughput generated by the two is the same, the IOPS of the former is 10 and the IOPS of the latter is 1, and the opening and ending of an IO consumes a lot of operating system resources.

Case 1

Suppose 5 database blocks are read at a time, 10000 database blocks and 100 index blocks are read at a time. If the data to be fetched is more than 20% of the total, index scanning is used because each step of the two steps is a separate IcanBO, and each read is a single block and can only read one database block. So the number of times to scan = the number of index blocks + 20% of all data blocks = 100 "10000" 20% "2100. If they are all physical reads, then the number of IO is 2100. With full table scans, five data blocks are read at a time, so the number of times to scan = all databases / 5 million 10000 / 5 million 2000, if they are all physical reads, then the number of IO is 2000.

Case 2

Suppose a table contains 100000 rows of data-100000 rows

We need to read 20% (20, 000) of the data-20000 rows.

This table has a total of 10000 blocks-10000 blocks (10 rows of a block, 800bytes per row)

Reading 20000 rows of data through the index = about 20000 table access by rowid = you need to process 20000 blocks to execute this query, but the entire table has only 10000 blocks, so: reading 20% of the total data by index is equivalent to reading the whole table an average of 2 times.

Of course, it can not be said that the number of rows read by the index is greater than the number of blocks in the entire table, it is a full table scan, but also consider whether the read block is a logical read or a physical read.

If they are all logical reads, the number of index scans must be greater than the number of full table scans.

If all are physical reads, in the extreme case of CLUSTERING_FACTOR, the index scan must be smaller than the full table scan.

For example, in case 2 above, although the index scan needs to process 20000 blocks, not all of these 20000 blocks are physical reads. Normally, the physical read IO is only 2000 blocks (accounting for 20% of the entire table block). If all table scans are physical reads, then the IO is 10000 blocks.

Of course, if these 20% of the data are extremely hashed, distributed on all blocks of the table, that is, 10000 blocks, if both the index scan and the full table scan are physical reads, then the index scan IO=100+10000, the full table scan IO=10000

Therefore, if a larger table is scanned by an index, if the extracted data is more than 5% of the total, 10% of the total, the effect of using index scanning may not be as good as that of full table scanning.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.