白天一个朋友来电，咨询这样一个场景：生产环境上一个索引表空间，由于事先规划有问题，出现空间不足。申请额外空间需要走正式流程需要时间，问临时有没有什么好方法？
表空间对应的索引实际上是一张核心数据表的索引所在空间。由于经常性的进行数据导入操作，引起索引空间的不足。经过思考，在短时间内申请不到额外资源的情况下，可以从几个方面去进行尝试。
1、实验环境准备
朋友的环境是Oracle 10g，所以对应使用相似的实验环境。
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 – Production
2、尝试索引rebuild
对很多DBA而言，索引定期rebuild是维护相关查询效率的重要日常工作。很多时候，经常性的对一些索引进行rebuild重构操作，的确可以优化索引结构，提高执行作业效率。
在以前的Blog中，笔者讨论过对于定期rebuild索引的见解，此处不重复累述（http://space.itpub.net/17203031/spacelist-blog-itemtypeid-84194）。这里要强调的是，相对于堆表（Heap Table）结构，普通索引的空间使用上，是有不同的。
下面构建实验环境数据表T：
SQL> select count(*) from t;
COUNT(*)
----------
202872
SQL> create index idx_t_name on t(object_name);
Index created
SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);
PL/SQL procedure successfully completed
SQL> select segment_name, bytes from dba_segments where segment_name in ('T','IDX_T_NAME') and wner='SCOTT';
SEGMENT_NAME BYTES
-------------------- ----------
T 24117248
IDX_T_NAME 9437184
此时，我们删除数据表中数据，重新收集统计量。
SQL> delete t;
202872 rows deleted
SQL> commit;
Commit complete
SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);
PL/SQL procedure successfully completed
SQL> select segment_name, bytes from dba_segments where segment_name in ('T','IDX_T_NAME') and wner='SCOTT';
SEGMENT_NAME BYTES
-------------------- ----------
T 24117248
IDX_T_NAME 9437184
注意：我们将数据删除掉之后，数据表和索引的分配空间是不会回收的。堆表（Heap Table）的空闲结构，会在日后数据插入（非direct load）方式时被利用起来。但是，索引标注为删除的叶子节点，却可能不会被重用！
如果索引列是那种单向递增的取值方式，如使用sequence生成的数据值，所新插入的数值都是在叶子节点的右侧高位上。这样的话，过去被删除的叶子节点是不会别利用上的。从而，索引的体积就只能单向的不断膨胀。
解决的方法，就是进行周期性的rebuild操作。
SQL> alter index idx_t_name rebuild;
Index altered
SQL> select segment_name, bytes from dba_segments where segment_name in ('T','IDX_T_NAME') and wner='SCOTT';
SEGMENT_NAME BYTES
-------------------- ----------
T 24117248
IDX_T_NAME 65536
注意：有的时候，rebuild也是需要额外的一些空间使用的，所以在资源特别紧张的时候，可以先unuable掉索引，再进行rebuild操作。
3、索引对象压缩
压缩索引段对象，是另一种解决问题的方法。在Oracle的各个版本中，一直在不断完善压缩技术。希望实现一种对DML操作性能影响最小的压缩技术。
SQL> create table t as select owner a, owner b from dba_objects;
Table created
SQL> select count(*) from t;
COUNT(*)
----------
53301
SQL> create index idx_t_ownernocompr on t(a);
Index created
SQL> create index idx_t_ownercompr on t(b) compress;
Index created
SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);
PL/SQL procedure successfully completed
在构建索引时，使用compress关键字，可以定义压缩索引段结构。空间使用情况如下：
SQL> select index_name, compression from dba_indexes where wner='SCOTT' and table_name='T';
INDEX_NAME COMPRESSION
------------------------------ -----------
IDX_T_OWNERNOCOMPR DISABLED
IDX_T_OWNERCOMPR ENABLED
SQL> select segment_name, bytes, blocks from dba_segments where segment_name in ('IDX_T_OWNERNOCOMPR','IDX_T_OWNERCOMPR');
SEGMENT_NAME BYTES BLOCKS
-------------------- ---------- ----------
IDX_T_OWNERNOCOMPR 2097152 256
IDX_T_OWNERCOMPR 786432 96
可以看出，压缩的索引对象可以节省空间使用。同时，对执行计划来说，压缩索引也是可以出现在执行计划中的。
SQL> create table t_back as select * from t;
Table created
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 441125606
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2235 | 26820 | 20
| 1 | TABLE ACCESS BY INDEX ROWID| T | 2235 | 26820 | 20
|* 2 | INDEX RANGE SCAN | IDX_T_OWNERNOCOMPR | 2235 | | 6
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"='SCOTT')
14 rows selected
SQL> explain plan for select * from t where b='SCOTT';
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2961582115
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2235 | 26820 | 18
| 1 | TABLE ACCESS BY INDEX ROWID| T | 2235 | 26820 | 18
|* 2 | INDEX RANGE SCAN | IDX_T_OWNERCOMPR | 2235 | | 4
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("B"='SCOTT')
14 rows selected
注意：压缩索引，特别是11g之前的压缩索引虽然压缩比例尚可，但是对DML操作来说，必然存在一定的性能影响。这个方面到了11g有了根本性的改善，11g中推出的Advanced Compression组件，针对各种类型数据提供了全新的压缩解决方案，同时其中的OLTP属性，将压缩对DML操作的影响降到最小。
4、分区索引使用
另外还有一种思路，就是建立在分区表基础上。朋友的数据表是一张分区表，而且每次主要使用的就是一个分区的数据。那么，可否只是激活这个分区的索引，其他分区的索引不生成或者失效呢？
注意：分区表的索引分为全局索引Global Index和本地索引Local Index两个类型。如果是全局索引，这种思路就不成立，因为索引段对应是一个。如果是Local Index，这种可能性是存在的。
SQL> create table t
2 partition by list(owner)
3 (
4 partition t_list_sys values ('SYS'),
5 partition t_list_scott values ('SCOTT'),
6 partition t_list_hr values ('HR'),
7 partition t_list_others values (default)
8 )
9 as select * from dba_objects where 1=0;
Table created
SQL> insert into t select * from dba_objects;
50727 rows inserted
SQL> commit;
Commit complete
建立本地索引。
SQL> create index idx_t_idlocal on t(object_id);
Index created
SQL> select segment_name, partition_name, bytes from user_segments where segment_name='T';
SEGMENT_NAME PARTITION_NAME BYTES
-------------------- ------------------------------ ----------
T T_LIST_SYS 3145728
T T_LIST_SCOTT 65536
T T_LIST_HR 65536
T T_LIST_OTHERS 4194304
SQL> create index idx_t_idlocal on t(object_id) local;
Index created
SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);
PL/SQL procedure successfully completed
SQL> select index_name, index_type, status from user_indexes where index_name='IDX_T_IDLOCAL';
INDEX_NAME INDEX_TYPE STATUS
------------------------------ --------------------------- --------
IDX_T_IDLOCAL NORMAL N/A
SQL> select index_name, partition_name, status from user_ind_partitions;
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ --------
IDX_T_IDLOCAL T_LIST_SCOTT USABLE
IDX_T_IDLOCAL T_LIST_SYS USABLE
IDX_T_IDLOCAL T_LIST_HR USABLE
IDX_T_IDLOCAL T_LIST_OTHERS USABLE
可以通过部分unusable的方法，只启用一部分的索引段对象。
SQL> alter index idx_t_idlocal unusable;
Index altered
SQL> select index_name, partition_name, status from user_ind_partitions;
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ --------
IDX_T_IDLOCAL T_LIST_SCOTT UNUSABLE
IDX_T_IDLOCAL T_LIST_HR UNUSABLE
IDX_T_IDLOCAL T_LIST_OTHERS UNUSABLE
IDX_T_IDLOCAL T_LIST_SYS UNUSABLE
SQL> alter index idx_t_idlocal rebuild;
alter index idx_t_idlocal rebuild
ORA-14086: 不能将分区索引作为整体重建
SQL> alter index idx_t_idlocal rebuild partition T_LIST_SYS;
Index altered
SQL> select index_name, partition_name, status from user_ind_partitions;
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ --------
IDX_T_IDLOCAL T_LIST_SCOTT UNUSABLE
IDX_T_IDLOCAL T_LIST_HR UNUSABLE
IDX_T_IDLOCAL T_LIST_OTHERS UNUSABLE
IDX_T_IDLOCAL T_LIST_SYS USABLE
本地索引的rebuild，是通过每个部分分区分别进行rebuild。当一部分分区usable，另一部分unusable的时候，处在usable状态的索引是可以生效使用的。
SQL> explain plan for select * from t where wner='SYS' and object_id=1000;
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2092295073
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cos
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 92 |
| 1 | PARTITION LIST SINGLE | | 1 | 92 |
|* 2 | TABLE ACCESS BY LOCAL INDEX ROWID| T | 1 | 92 |
|* 3 | INDEX RANGE SCAN | IDX_T_IDLOCAL | 1 | |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OWNER"='SYS')
3 - access("OBJECT_ID"=1000)
16 rows selected
SQL> explain plan for select * from t where wner='SCOTT' and object_id=1000;
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 882533222
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | P
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 85 | 3 (0)| 00:00:01 |
| 1 | PARTITION LIST SINGLE| | 1 | 85 | 3 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL | T | 1 | 85 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_ID"=1000)
14 rows selected
同时，并不影响insert等操作。
SQL> insert into t select * from dba_objects;
50726 rows inserted
SQL> commit;
Commit complete
但是，一部分索引处在unusable状态，并不是没有问题。在重新收集统计量的时候，Oracle会报错。
SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);
begin dbms_stats.gather_table_stats(user,'T',cascade => true); end;
ORA-20000: index "SCOTT"."IDX_T_IDLOCAL" or partition of such index is in unusable state
ORA-06512: 在 "SYS.DBMS_STATS", line 13056
ORA-06512: 在 "SYS.DBMS_STATS", line 13076
ORA-06512: 在 line 1
所以，这种方法也只是权宜之计。
5、暂时rebuild到另一表空间
一个表空间空间缺少，可以暂时性的将索引rebuild到其他稍微空闲的表空间中，作为临时的替代。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/17203031/viewspace-712765/ ，如需转载，请注明出处，否则将追究法律责任。
