《Pro Oracle SQL》Chapter3--3.1 Full Scan Access Methods 之二

Full Scans and Throwaway  全扫描和“抛弃”
    Always remember that whether or not a full scan will be an effective choice depends on the number of
blocks that will need to be read as much as on how many rows will end up in the final result set.  How
the data is stored plays an important role in the decision, as demonstrated in this example.  However,
the other key factor in whether or not a full scan is an effective choice is throwaway.  Throwaway rows
are those rows that are checked against a filter predicate and don’t match the filter and are thus
rejected from the final result set.
    永远记住全扫描是否是有效的选择取决于所需读入的块数，也就是有多少行集将影响最终结果集。数据如何存储在抉择的过程中扮演了重要的角色，正如本例中所演示的。然而，是否全扫描是一个有效的选择的另个关键的因素则是“抛弃”。抛弃行是指这些行由过滤器谓词检查，且不匹配过滤器，则被排除于最终的结果集中。
     In the previous example, the full table scan operation would have to check all 10,000 rows in the
table and throw away 9,900 of them to end up with the final result set of 100 rows.  The check on each
row is simply the filter predicate on  id = 1 (seen in Listing 3-4 in the Predicate Information section
for step 1).  In order to execute this filter, the CPU will be utilized for each check.  That means that while
the number of blocks accessed will be limited, there will be quite a bit of CPU resources used to
complete the filter checks for each row.  The use of the CPU will be factored into the cost of the full scan. As the number of blocks accessed and the amount of throwaway increases, the more costly the full
scan will become.  Listing 3-5 is a simple query to show the number of rows and number of blocks for
table T2 in your example.  Based on the number of blocks shown, the full table scan would access
approximately 164 blocks.
    在上个例子中，全表扫描操作将必须检查表中所有的10000行然后抛弃其中9900行而最终结果集是100行。对每行的检查简单的由过滤谓词id=1(查 看列表3-4的步骤1谓词信息段)。为了执行该过滤器，在每次检查中都要用到CPU。这意味着即使访问的块数是有限的，对于完成每行的检查依然要占用很大CPU资源。CPU的使用将算入全扫描成本中。 因为访问的块数和抛弃的数量的增加，全扫描的成本也越发变高。列表3-5是一个简单的查询展示例子中T2表的行数和块数。基于展示的块数，全表扫描将大概访问164个块。
Listing 3-5. Rows and Blocks Statistics for Tables T1 and T2       表T1和T2的行和块统计
SQL> select table_name, num_rows, blocks from user_tables where table_name = 'T2' ;
 
TABLE_NAME                            NUM_ROWS          BLOCKS
------------------------------             ---------------               ---------------
T2                                            10000                        164
 
1 rows selected.
 
    Over time, as rows are added to the table and the table grows larger, the cost of throwing away so
many rows would increase enough to cause the optimizer to switch to an index scan operation instead. 
The point where the optimizer decides to switch over may not necessarily be the point where you
achieve optimal performance.   You can use hints to force the optimizer to use an index and test to see at
what point it might make more sense to use an index,  and if the optimizer doesn’t choose that path,you
can consider using hints or SQL profiles to help.  Chapter 16 will cover using hints and profiles so
you’ll be prepared to use them if you ever need to do so.
    随着时间的推移，行加入表中，表变得越来越大，抛弃如此多的行的成本将迫使优化器切换成索引扫描操作来代替。优化器决定切换的那个(转折)点可能也不一定是你取得优化性能的那个(转折)点。 你能通过提示而强制优化器使用索引，再测试看在哪一点可能使用索引更合理,若优化器不选择那条路径，你就可以考虑使用 提示或者SQL配置参数予以辅助。 第十六章将讲述使用提示和配置，如果你曾经有需要这么做你将用的上他们。

Full Scans and Multiblock Reads    全扫描和多块读
    Another thing you need to know about full scans is how blocks are read.  A full scan operation makes
multiblock reads.  This means that a single IO call will request several blocks instead of just one.  The
number of blocks requested will vary and can actually range anywhere from one to the number of
blocks specified in the db_file_multiblock_read_count  parameter.   For example, if the parameter is set
to 16 and there are 160 blocks in the table, there could be only 10 calls made to get all the blocks.
    你需要知道的关于全扫描的另一件事就是块是如何读取的。全扫描可多块读入。这意味着一次IO调用将请求几个块而不只是一个。请求的块数会变动，实际的范围 可能是1到参数db_file_multiblock_read_count所指定的块数。 例如，如果参数设置成16而表中有160个块，可能只需要10 次读就能获取所有的块。
    I say that only 10 calls  could  be made because of the following limitations on multiblock read calls. 
Oracle will read db_file_multiblock_read_count  blocks unless reading the full number of blocks 
    我是说可能只要10次调用因为还有对多块读调用还有下面这些限制。Oracle将读db_file_multiblock_read_count设定的块数，除非读满额块数。

•   causes Oracle to have to read blocks that cross an extent boundary .  In this case, Oracle will

read the blocks up to the extent boundary in one call, then issue another call to read the
remainder.

•    means a block already in the buffer cache would be read again as part of the multiblock read. 

Oracle will simply read the blocks up to those not already in memory, then issue another read
call that skips those blocks to read the rest.  This could mean that a multiblock read might only
read one block at a time.  For example, let’s say the multiblock read count was 16 and the
range of blocks to be read was between block number 1 and 16.  If the even numbered blocks
had already been placed into the buffer cache, individual single block reads would be done for
each odd numbered block in that range.  In that case, 8 read calls would be made—one for

each block in that range not already in the buffer cache.

•     would exceed an operating system limit for multiblock read sizes.  This is dependent on your

operating system so it can vary.

•     若Oracle必须读取跨出范围边界 的块。在这种情况下，Oracle将一次调用中读在范围边界以内的块。再发送另一次调用读取其它的块。

•     若块已经在缓冲区缓存中，作为多块读的一部分，该块将再次被读取。Oracle将只是读取那些没有在内存中的块，而发送另一个读调用跳过那些块去读其它部分。 这意味着可能多块读只是一次读一个块。例如，若多块读数是16(就是要读16个块)而读入块的范围则是1到16.如果偶数标号的块已经置于缓冲区缓存 中，范围中各奇数块的读取就需由独立的单个块读完成。这种情况下，将有8次读----对在那个范围中的各个没在缓冲区中的块各一次。

•    多块读的大小可能超出操作系统的限制。这取决于你操作系统，所以它会变动。