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@]