玉面飞龙的BLOG

今天测试了一下，如果表中只有一条记录，查询该表并且指定一个order by，Oracle是否需要做排序操作。答案是的。

测试脚本，可以往测试表中插入1条或者多条记录。

create table test(id number);

insert into test values(1);
commit;

alter session set events ‘10031 trace name context forever,level 100′;
alter session set events ‘10032 trace name context forever,level 100′;
alter session set events ‘10033 trace name context forever,level 100′;

select * from test order by id;

alter session set events ‘10032 trace name context off’;
alter session set events ‘10031 trace name context off’;
alter session set events ‘10033 trace name context off’;

在test表只包括1条和2条记录的情况下，10g中相关event的结果。

—- Sort Parameters ——————————
sort_area_size                    4096000
sort_area_retained_size           4096000
sort_multiblock_read_count        2
max intermediate merge width      112
—- Sort Statistics ——————————
Input records                             1
Output records                            1
Total number of comparisons performed     0
Total amount of memory used               2048
Uses version 2 sort
Does not use asynchronous IO
—- End of Sort Statistics ———————–

—- Sort Parameters ——————————
sort_area_size                    4096000
sort_area_retained_size           4096000
sort_multiblock_read_count        2
max intermediate merge width      112
—- Sort Statistics ——————————
Input records                             2
Output records                            2
Total number of comparisons performed     1
Comparisons performed by in-memory sort 1
Total amount of memory used               2048
Uses version 2 sort
Does not use asynchronous IO
—- End of Sort Statistics ———————–

如上，可以看见Oracle分配了2K内存用来排序。

在9205中的结果，

—- Sort Parameters ——————————
sort_area_size                    4096000
sort_area_retained_size           4096000
sort_multiblock_read_count        2
max intermediate merge width      112
—- Sort Statistics ——————————
Input records                             1
Output records                            1
Total number of comparisons performed     0
Total amount of memory used               8192

—- Sort Parameters ——————————
sort_area_size                    4096000
sort_area_retained_size           4096000
sort_multiblock_read_count        2
max intermediate merge width      112
—- Sort Statistics ——————————
Input records                             2
Output records                            2
Total number of comparisons performed     1
Comparisons performed by in-memory sort 1
Total amount of memory used               8192
—- Sort Statistics ——————————

如上Oracle使用了8K的内存用来排序。

我还测试了MTS下，large pool min allocate为64K的时候，对很少纪录的表进行排序，10g中仍然是2K的内存。明显，当表中只包括一条记录的时候，Oracle没有必要分配这2K的内存。