我们来看看该sort_area_size参数对创建索引时排序的具体影响:
SQL> select * from v$version; BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi PL/SQL Release 10.2.0.4.0 - Production CORE 10.2.0.4.0 Production TNS for Linux: Version 10.2.0.4.0 - Production NLSRTL Version 10.2.0.4.0 - Production /* 测试使用版本10.2.0.4 */ SQL> archive log list; Database log mode No Archive Mode Automatic archival Disabled Archive destination /s01/arch Oldest online log sequence 27 Current log sequence 34 /* 为了不受影响我们采用非归档模式 */ SQL> conn maclean/maclean Connected. SQL> alter session set workarea_size_policy=MANUAL; Session altered. /* 只有使用手动PGA管理时sort_area_size等参数才生效 */ SQL> alter session set db_file_multiblock_read_count=128; Session altered. /* 加大多块读参数帮助我们节约时间 */ SQL> alter session set "_sort_multiblock_read_count"=128; Session altered. /* 10g中sort_multiblock_read_count成为隐式参数,我们尝试手动固定它 */ SQL> set timing on; SQL> alter session set events '10032 trace name context forever ,level 10'; Session altered. Elapsed: 00:00:00.00 /* 在session级别设置10032事件,该事件帮助输出排序相关的统计信息*/ SQL> drop index ind_youyus; alter session set sort_area_size=1048576; alter session set sort_area_size=1048576;