SQL Work Areas(101)

For complex queries (for example, decision-support queries), a big portion of the
runtime area is dedicated to work areas allocated by memory-intensive operators such
as the following:
■ Sort-based operators (order by, group-by, rollup, window function)
■ Hash-join
■ Bitmap merge
■ Bitmap create
For example, a sort operator uses a work area (sometimes called the sort area) to
perform the in-memory sort of a set of rows. Similarly, a hash-join operator uses a
work area (also called the hash area) to build a hash table from its left input. If the
amount of data to be processed by these two operators does not fit into a work area,
then the input data is divided into smaller pieces. This allows some data pieces to be
processed in memory while the rest are spilled to temporary disk storage to be
processed later. Although bitmap operators do not spill to disk when their associated
work area is too small, their complexity is inversely proportional to the size of their
work area. Thus, these operators run faster with larger work area.
The size of a work area can be controlled and tuned. Generally, bigger database areas
can significantly improve the performance of a particular operator at the cost of higher
memory consumption. Optimally, the size of a work area is big enough such to
accommodate the input data and auxiliary memory structures allocated by its
associated SQL operator. If not, response time increases, because part of the input data
must be spilled to temporary disk storage. In the extreme case, if the size of a work
area is far too small compared to the input data size, multiple passes over the data
pieces must be performed. This can dramatically increase the response time of the
operator.

SQL工作区
在做排序操作 , 哈希连接 , 位图合并和创建操作时(memory-intensive operator), runtime area is dedicated
to work areas 排序操作和哈希连接处理的数据not fit into工作区时 , 数据被分成更小的块 ,
这样一部分数据在内存中处理 , 另外一部分则spilled to temporary disk storage稍后处理
位图操作不会有上述的spilled to disk操作 , their complexity is inversely proportional to the size of
their work area.
如果工作区远小于需要处理的数据 , 操作的相应时间将会dramatically increase

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10599713/viewspace-978652/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10599713/viewspace-978652/