oracle 表空间排列,关于排序、sort_area_size、临时表空间

《按照我个人的猜测，应该是排好后每写入一条入磁盘则将该记录所在小组重新抽取一条出来进行排序(这时是有序记录组里面所以很快)》。

之所以这样猜测是因为个人觉得这样的方式效率能比较高并且算法简单容易控制，资源消耗也少

大家可参考内容

http://www.ixora.com.au/q+a/space.htm#end

If my table occupies 500M of disk space, and I have a 1M sort_area_size, how much temporary tablespace disk space do I need to perform the following sorts?

1. select col1, col2, col3 from tablename order by col1, col2, col3;

2. select col1, col2, col3 from tablename order by col1, col2;

The sort space requirements are dependent on the size of the row source being sorted, rather than the size of the key. Your two sorts would require the same amount of disk space. However, if you were joining to another table and including one of its columns in the select list, then the size of the row source to be sorted could be larger than the largest base table.

It is also possible for the disk space requirements to be up to twice the size of the row source if the sort area size is too small. If for example you need to sort 1,001,000 rows and your sort area size can only accommodate 1000 rows at a time. Then the first phase of the sort will be to read the row source, and write 1001 sort runs of 1000 rows each to the temporary segment. The next phase is to read one row at a time from each of the first 1000 sort runs and merge them into a single large sort run. Towards the end of this phase, you need space for all the original 1001 sort runs and the big sort run at the same time - which is nearly twice the row source size. The third phase of this sort would be a second merge pass to merge to large sort run with the remaining original 1000 row sort run. To avoid this, and process a sort in a single merge pass, the sort area size must hold at least as many rows as the square root of the number of rows in the row source.