Chapter3? Access and Join Methods 访问和连接方法

Karen Morton???? (page 83)
??? The optimizer must determine how to access the data your SQL statements require. You formulate your statement and the optimizer, during a hard parse, will figure out which operations should provide the data in the most effective way possible.? Using statistics as the primary guide, the optimizer will compute the cost of the possible alternatives to first access data and then join multiple tables to get the final result set.? The more you understand about the different access and join methods the optimizer will consider, the more likely you will be to formulate your SQL to help the optimizer make the best choices.? And, when the operation chosen by the optimizer doesn’t provide the performance you need, you can more accurately determine which operations would be more suited to produce the response times you want.
??? 优化器必须确定如何访问你的SQL语句所需的数据。你公式化你的语句，而在硬解析时，优化器将计算出所需操作，为了尽可能最有效的给出数据。使用统计信息 作为首要的向导，优化器将计算出可能的替代方案的成本，首次访问在到多次连接多表获得最终的结果。你理解越多关于优化器所考虑的不同的访问和连接方法，越有可能你能公式化书写你的SQL以帮助优化器做出最佳的选择。 而且，当优化器所选的操作并没有达到你所需的性能要求时，你能够更准确的确定哪种操作将更适合于产生你想要的响应时间。
??? After your SQL statement’s expressions and conditions are evaluated and any query transformation? that might help it more accurately develop the execution plan are complete, the next step in the development of the execution plan is for the optimizer to determine which method of accessing the data will be best.? In general, there are only two basic ways to access data: either via a full scan or an index scan.? During a full scan (which, by the way, can be a full table scan or a fast full index scan) multiple blocks are read in a single IO operation.? Index scans first scan index leaf blocks to retrieve specific rowids and then hand those rowids to the parent table access step to retrieve the actual row data.? These accesses are performed via single block reads.? If there are additional filters that need to be applied to the data after the table access step, the rows will pass through that filter before being included in the final result set from that step.? ?
??? 在你的SQL语句的表达式和条件被评估，且任何可能有助于准确开发执行计划的查询变换完成之后，开发执行计划的下一步就是优化器确定哪种访问数据的方法是最佳的。一般说来，只有两种最基本的访问数据的方法：要么全扫描，要么索引扫描。 全扫描期间(顺便说一下，可以是全表扫描或者快速全索引扫描)多个块在一次IO操作中读取。索引扫描首先扫描索引页块检索指定的rowid然后传递这些rowid给父(上一步)的表访问步骤检索实际的行数据。这些访问都是通过单独的块读取(步骤)执行的。如果有附加的过滤器需要在表访问步骤之后应用于数据，在被包括在，自那步起的最终结果集之前，行集将穿过过滤器。
??? The access method that is chosen for a table is used to determine the join methods and join orders that will be used in the final plan.? So, if the access method chosen for a table is suboptimal, the likelihood that the whole plan is faulty is high.? As discussed in Chapter 2, statistics play a vital role in how accurate the optimizer is in determining the best method.? Along with representative statistics, the optimizer will use your query to figure out how much data you are requesting and which access method will provide that data as quickly as possible.? Each table in the query will first be evaluated independently from the others to determine its optimal access path.? In the next sections, I’ll review each of the access methods in detail.
??? 对某表所选的访问方法用于确定连接方法和连接顺序从而将用于最终的执行计划中。因此，如果对某表所选访问方法是欠优化的，很有可能整个执行计划的缺陷就很高。正如第二章讨论的，统计信息在让优化器如何准确的确定最佳方法上扮演了十分重要的角色。根据 有代表性的统计数据，优化器将使用你的查询计算出你请求了多少数据和哪种访问方法能尽可能快的提供数据。在查询中每张表将首先相互独立的评估从而确定他的最佳访问路径。 在接下的章节中，我将详细的检阅各种访问方法。

Ful