《Pro Oracle SQL》Chapter10.2.3 Testing the Effects of Query Changes
Testing the Effects of Query Changes 测试查询改变的效果 (page 315)Even as data does not remain static, SQL is not always static. Sometimes requirements change, so code must be modified. What if the requirements changed for the examples in Listings 10-3 and 10-4? Would minor changes invalidate the use of the hints embedded in the SQL? This is probably something worth investigating, so let’s do so.
正如数据不会保持静态，SQL也不总是静态。有时候需求改变，因此代码需要修改。如果需求改变对于列表10-3和10-4中例子的会怎样？是否最小的改变使得嵌入SQL中的提示失效？这可能是值得调查的，因此让我们研究一下。
Previously, you were reporting on income brackets when the count of them for any country was
greater than or equal to 25% of the total global count for that bracket. Now you are asked to include an
income bracket if it is among those income brackets the number of which is greater than the median,
based on the number of customers per bracket. This SQL is seen in Listing 10-6. Notice that the INLINE
hint has been left in. So now there’s an additional full table scan and index scan as compared to the
execution plan in Listing 10-4. While the elapsed time has increased, it still seems reasonable.
之前，你报告了任意国家的收入阶层，他们的数量大于或等于全球那个阶层数量的25%。现在你要求包含一个收入阶层，如果它位列那些数量大于中位数的收入阶层，基于每桶的顾客数。SQL如列表10-6所示。注意INLINE提示留下了。因此，相比于列表10-4的执行计划，现在有一个额外的全表扫描和索引扫描，虽然消耗时间增加，它似乎还是合理的。
Now that there’s an additional table scan and index scan, how do you think the performance of this
query will fare if temporary table transformations are allowed to take place? The results can be seen in
Listing 10-7.
既然这里有了额外的表扫描和索引扫描，你如何认为这个查询的性能是公平的，即使临时表变换运行发生？结果可从列表10-7中看出。
Because there’s that additional scan taking place in the modified version of the query, the overhead
of logical IO becomes more apparent. It is significantly more efficient with this query to allow Oracle to
perform table transformations, writing the results of the hash join to a temporary table on disk where
they can be reused throughout the query.
因为在修改版的查询中有额外的扫描发生，逻辑IO的开销变得更明显。这个查询显然较为有效率，因为允许Oracle执行表变换，把哈希连接的结果写入磁盘的临时表，可贯穿于整个查询重用。
页:
[1]