oracle大表创建索引

1.表分析

查看有多少条记录

SQL> select count(1) from abce;

查看表的大小

SQL> select segment_name,sum(bytes)/1024/1024 from dba_segments where segment_name='ABCE' group by segment_name;   

2.评估创建索引需要的数据空间 需要评估表空间、临时表空间的大小是否满足

使用执行计划评估一下要创建的索引的大小：

SQL> explain plan for create index idx_abce_update_time on abce(update_time);
SQL> select * from table(dbms_xplan.display);

查看表空间可用空间的大小：

SQL> select tablespace_name,sum(bytes)/1024/1024/1024 gb from dba_free_space where tablespace_name='TBS_ABCE' group by tablespace_name;

查看临时表空间可用空间的大小：

SQL> select file_name,tablespace_name,bytes/1024/1024/1024 gb,autoextensible,maxbytes/1024/1024/1024 max_gb from dba_temp_files where tablespace_name='TEMP';

3.调整部分参数

适当调整db_file_multiblock_read_count

SQL> show parameter db_file_multiblock_read_count 
SQL> alter session set db_file_multiblock_read_count=256;

调大sort_area_size 创建索引时要对大量数据进行排序操做，在oracle11g,若是workarea_size_policy的值为AUTO,sort_area_size将被忽略，pga_aggregate_target将被启用。 pga_aggregate_target决定了整个pga大小，并且一个session并不能使用所有的pga大小，它受到一个隐藏参数的限制，大体能使用pga_agregate_target的5%。

SQL> show parameter sort_area_size 
SQL> alter system set workarea_size_policy='MANUAL';
SQL> alter session set sort_area_size=xxx;

4.创建索引 SQL> create index ind_abce_update_time on abce(update_time);

单机且没有dg的环境，可以考虑使用nologging；有dg的环境不能使用nologging方式。 也可以考虑并行和online，但是如果使用online，就不能同时开启并行。

SQL> create index ind_abce_update_time on abce(update_time) online;
SQL> create index ind_abce_update_time on abce(update_time) parallel nologging;

如果开启了并行，创建结束后要修改并行度：

SQL> alter index ind_abce_update_time noparallel;

如果使用了nologging：

SQL> alter index ind_abce_update_time logging;

可以借助sql监控报告，查看创建索引的报告

SQL> select dbms_sqltune.report_sql_monitor('gb7tu2jpwng3q') from dua

 

 

5.将参数调整回原样

SQL> alter system set workarea_size_policy='AUTO';
SQL> alter session set db_file_multiblock_read_count = xxx;