4.4 调整sort_area_size
SQL> alter session setworkarea_size_policy=manual;
SQL> alter session setworkarea_size_policy=manual;
SQL> alter session setsort_area_size=2000000000;
SQL> alter session setsort_area_size=2000000000;
SQL> create index id1_ind on tbim(id1)tablespace imindex nologging parallel 4;
Index created.
Elapsed: 00:08:12.79
这个效果还是比较明显的
4.5 修改其他参数
修改全表扫描时一次读取的block的数量db_file_multiblock_read_count
直接路径IO的大小,10351 event level 128
禁用block checksum/checking
备选的排序算法_newsort_type
SQL> alter session setdb_file_multiblock_read_count=1024;
SQL> alter session setdb_file_multiblock_read_count=1024;
SQL> alter session set events '10351trace name context forever, level 128';
SQL> alter session setsort_area_size=2000000000;
SQL> alter session setsort_area_size=2000000000;
SQL> alter session set"_sort_multiblock_read_count"=128;
SQL> alter session set"_sort_multiblock_read_count"=128;
SQL> alter session enable parallel ddl;
SQL> alter session setdb_block_checking=false;
SQL> alter system setdb_block_checksum=false;
SQL> create index id1_ind on tbim(id1)tablespace imindex nologging parallel 4;
Index created.
Elapsed: 00:07:37.57
5、总结
我可以通过以下手段加快创建索引速度:
1)除此之外,还可以适当的调整并行查询的数量(一般不超过8);
2)索引和表分离,单独的临时表表空间;
3)把表调整为nologging状态,或者创建索引的时候指定nologging;
4)我们可以适当调整数据库相关参数加快左右创建索引速度,示例如下:
SQL> alter session setdb_file_multiblock_read_count=1024;
SQL> alter session setdb_file_multiblock_read_count=1024;
SQL> alter session set events '10351trace name context forever, level 128';
SQL> alter session setsort_area_size=2000000000;
SQL> alter session setsort_area_size=2000000000;
SQL> alter session set"_sort_multiblock_read_count"=128;
SQL> alter session set "_sort_multiblock_read_count"=128;
SQL> alter session enable parallel ddl;
SQL> alter session setdb_block_checking=false;
SQL> alter system setdb_block_checksum=false;
通过以上调整一般可以加快40%以上的创建速度