Oracle 大表添加索引的最佳方式

配景：

1. 业务系统中现在经常存在上亿数据的大表，在这样的大表上新建索引，是一个较为耗时的操作，特别是在生产环境的系统中，添加不当，有可能造成业务表锁表，业务表长时间的停服势必会影响正常业务的开展。根据个人的实际经验，我们可以使用三种手段来帮助大家解决这个问题，需要注意的是这三种方法并不是独立使用的，很多时候我们会结合起来一起使用来提升建索引的效率。

复制代码

办理方案：

1. 第一种方法就是使用并行——parallel 开启并发执行
3. 并发执行可以最大程度的利用我们的数据库的硬件资源，把大批量的数据分成小批量到不同的进程去执行，从而大大减少sql的执行的时间。由于建索引属于ddl操作，我们可以通过下面的语句来实现并发执行。 下面的语句中，我们就配置了使用并发值为8来执行我们的sql语句

复制代码

CREATE INDEX idx_table1_column1 ON table1 (column1) PARALLEL 8;

1. 注意：并不是所有的系统都适用使用并行来解决，比如：目前有个系统使用cpu已经很高，如果这时你再开启并行，只会加重系统的负载。因此，在执行并行操作前一定要看一下系统目前使用情况。
3. 第二种方法是不开启日志——nologging
5. 我们知道数据表新增、修改、删除记录都可能会触发redo日志和undo日志的记录，特别是insert into table1 select * from table2这种语句，每条insert动作都会同时生成redo日志和undo日志，从而降低sql的执行速度。 对于创建索引的操作也是如此，索引的创建同样也涉及到这两类日志的记录，我们可以手动指定不记录非必要日志来加快sql执行的速度。

复制代码

注意：nologging的焦点在于只输入最少的redo日志（注意，这里不是不输出日志，只是最小化须要输入的日志量而已） 用法的话非常简朴，只须要在我们创建索引的语句上加上nologging关键字即可
CREATE INDEX idx_table1_column1 ON table1 (column1) nologging;

1. 第三种方法是在线执行——online（推荐使用）
3. 前面介绍的两个命令虽然能大幅度提升效率，但归根结底建索引就是会导致锁表，不停服执行的话还是相当有风险的，online的作用在于不阻塞DML操作，使得生产环境不会因为执行DDL语句导致业务功能阻塞， 尤其适合于不停机新建表索引这类场景。

复制代码

须要注意的是，online关键字的使用相对来说耗时会长一些，而且online关键字只能用于新增索引，并不能用在修改表结构等SQL语句中。 online的使用也非常简朴，在sql语句背面加上online就行。
CREATE INDEX idx_table1_column1 ON table1 (column1)  online;
有了这三个方法，我们的终极的sql大概是如许的，有了online可以保障不影响业务主流程的举行，而nologging和parallel则可以大幅度进步我们sql的实行速率，个人以为是一种可行的办理方案。
CREATE INDEX idx_table1_column1 ON table1 (column1)   parallel 8 nologging  online ;
很多朋侪以为到这里就竣事了，实在oracle数据库优化的空间永无止境，如果有朋侪想寻求最佳，想把数据库的性能发挥到最佳。那么下面尚有三种方法，但是这些不常用，作为学习数据库的原理，可以相识一下。
===========================================
增补方法1：由于创建索引时须要对表举行全表扫描,可以恰当思量调大db_file_multiblock_read_count的值， db_file_multiblock_read_count影响Oracle在读取数据时一次读取的最大block数目，在举行一些数据量比力大的操纵时，可以恰当 调解当前session的db_file_multiblock_read_count值，会在IO上节流节流一些时间。
SQL> show parameter db_file
NAME                                 TYPE        VALUE

---

db_file_multiblock_read_count        integer     128
SQL> alter session set db_file_multiblock_read_count=256;
Session altered.
SQL> show parameter db_file
NAME                                 TYPE        VALUE

---

db_file_multiblock_read_count        integer     256
增补方法2：我们知道索引都是有序的，使用索引的这个特性，因此我们可以想到，在创建索引时，要把索引列的值拿到内存中举行排序，因此我们调解排序区的巨细(sort_area_size)，创建索引时要对大量数据举行排序操纵 在oracle11g,如果workarea_size_policy的值为AUTO,sort_area_size将被忽略，pga_aggregate_target将被启用,pga_aggregate_target决定了整个 的pga巨细，而且一个session并不能使用全部的pga巨细，它受到一个隐蔽参数的限定，大抵能使用pga_agregate_target的5%，因此可以 思量将workarea_size_policy的值为manual，然后设置较大的sort_area_size以满足需求。
SQL> alter system set workarea_size_policy=‘MANUAL’;
System altered.
SQL> alter session set sort_area_size=204800;
Session altered.
SQL> show parameter sort_area_size;
NAME                                 TYPE        VALUE

---

sort_area_size                       integer      204800
增补方法3 ：为了让添加索引的表能尽快加载到数据缓存区中buffer cache,我们可以使用cache和full hint对源表做fts,以使它尽大概的出现在 buffer cache中LRU的MRU一端。
SQL> select /*+ cache(t) full(t) / count() from big_table t;
扫除战场：添加完索引后，把扫除一下战场，把战场规复到操纵之前，因此我们要把调解的参数举行规复到原来的样子。
SQL> alter system set workarea_size_policy=‘AUTO’;
System altered.
SQL> alter session set db_file_multiblock_read_count = 128;
Session altered.

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。

继续阅读请点击广告