一. 创建测试用表 big_table，并进行表分析
创建测试用户及表空间：
SQL> create tablespace tbs_a datafile '/u01/app/oracle/oradata/orcl/tbs_a01.dbf' size 100m autoextend on next 10m maxsize unlimited;
Tablespace created.
SQL> create user test identified by oracle default tablespace tbs_a;
User created.
SQL> grant resource,connect,dba to test;
Grant succeeded.
tom big_table脚本(去掉主键语句)
create table big_table
as
select rownum id, a.*
from all_objects a
where 1=0
/
alter table big_table nologging;
declare
l_cnt number;
l_rows number := &1;
begin
insert /*+ append */
into big_table
select rownum, a.*
from all_objects a
where rownum <= &1;
l_cnt := sql%rowcount;
commit;
while (l_cnt < l_rows)
loop
insert /*+ APPEND */ into big_table
select rownum+l_cnt,
OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID,
OBJECT_TYPE, CREATED, LAST_DDL_TIME, TIMESTAMP, STATUS,
TEMPORARY, GENERATED, SECONDARY, NAMESPACE, EDITION_NAME
from big_table
where rownum <= l_rows-l_cnt;
l_cnt := l_cnt + sql%rowcount;
commit;
end loop;
end;
/
exec dbms_stats.gather_table_stats( user, 'BIG_TABLE', estimate_percent=> 1);
执行：
SQL> @big_table;
Table created.
Table altered.
Enter value for 1: 20000000
old 3: l_rows number := &1;
new 3: l_rows number := 20000000;
Enter value for 1: 20000000
old 9: where rownum <= &1;
new 9: where rownum <= 20000000;
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
SQL> select count(1) from big_table;
COUNT(1)
----------
20000000
SQL> select segment_name,sum(bytes)/1024/1024 from dba_segments where segment_name='BIG_TABLE' group by segment_name;
SEGMENT_NAME
--------------------------------------------------------------------------------
SUM(BYTES)/1024/1024
--------------------
BIG_TABLE
2176
二. 评估创建索引需要的数据空间，查看表空间和临时表空间的大小，不够则增加。
通过执行计划可以预估创建索引需要的空间：
SQL> explain plan for create index idx_big_table_1 on big_table(object_id);数据空间可用大小：
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2090220367
--------------------------------------------------------------------------------
----------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |
--------------------------------------------------------------------------------
----------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 0 | CREATE INDEX STATEMENT | | 20M| 95M| 85550 (1)|
00:17:07 |
| 1 | INDEX BUILD NON UNIQUE| IDX_BIG_TABLE_1 | | | |
|
| 2 | SORT CREATE INDEX | | 20M| 95M| |
|
| 3 | TABLE ACCESS FULL | BIG_TABLE | 20M| 95M| 75404 (1)|
00:15:05 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
----------
Note
-----
- estimated index size: 486M bytes
14 rows selected.
SQL> select tablespace_name,bytes/1024/1024 from dba_free_space where tablespace_name='TBS_A';可以看出数据文件是可扩展的，可以暂时不用考虑数据空间不够的问题。
TABLESPACE_NAME BYTES/1024/1024
------------------------------ ---------------
TBS_A 113
SQL> select file_name,tablespace_name,autoextensible from dba_data_files where tablespace_name='TBS_A';
FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME AUT
------------------------------ ---
/u01/app/oracle/oradata/orcl/tbs_a01.dbf
TBS_A YES
查看临时表空间可用空间：
when you create a new segment (i.e. building an index), Oracle uses temporary extents
to initially build it and then at the end of the process -- converts (via a simple dictionary update)
the extents
into permanent ones.
SQL> select file_name,tablespace_name,bytes/1024/1024,autoextensible,maxbytes/1024/1024 from dba_temp_files where tablespace_name='TEMP';可以看出临时表空间大小为20M,可用空间为19m且不可扩展
FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME BYTES/1024/1024 AUT MAXBYTES/1024/1024
------------------------------ --------------- --- ------------------
/u01/app/oracle/oradata/orcl/temp01.dbf
TEMP 20 NO 0
SQL> select tablespace_name,free_space/1024/1024 from dba_temp_free_space where tablespace_name='TEMP';
TABLESPACE_NAME FREE_SPACE/1024/1024
------------------------------ --------------------
TEMP 19
增加临时表空间大小：
SQL> alter database tempfile '/u01/app/oracle/oradata/orcl/temp01.dbf' resize 600m;
Database altered.
三. 由于创建索引时需要对表进行全表扫描,可以适当考虑调大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 121
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
四. 调整排序区的大小(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';五. 为了让尽可能的源表进入buffer cache,使用cache和full hint对源表做fts,以使它尽可能的出现在LRU的MRU一端
System altered.
SQL> alter session set sort_area_size=102400;
Session altered.
SQL> show parameter sort_area_size;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sort_area_size integer 102400
SQL> select /*+ cache(t) full(t) */ count(*) from big_table t;
COUNT(*)
----------
20000000
六. 创建索引，使用nologging选项以减少在创建索引过程中产生大量的redo,另外使用online的方式创建索引可以避免在
创建索引的过程中在表上加锁，导致DML操作不可用，使用online则只会在表上加一个共享锁，仅不允许变更表的定义。
SQL> create index ind_big_table_id on big_table(id)
2 tablespace tbs_a
3 pctfree 5 initrans 4 maxtrans 255 nologging
4 storage(initial 104857600)
5 parallel 4 online;
Index created.
Elapsed: 00:02:01.73
七. 将参数调整回原样
SQL> alter system set workarea_size_policy='AUTO';
System altered.
Elapsed: 00:00:00.02
SQL> alter session set db_file_multiblock_read_count = 121;
Session altered.
Elapsed: 00:00:00.00