ALTER INDEX¶
说明¶
更新、清理、重建索引。
参数¶
IF EXISTS
修改索引时，若存在同名索引则进行修改，否则会跳过操作，且会打印NOTICE提示信息
index_name
是索引名。索引名在数据库中的模式中必须唯一。索引名必须遵循标识符规则。
PRIORITY level
为该索引指定一个优先级参数，该参数表明了在生成执行计划时，该索引被考虑使用的优先级别，数值越大，表示其优先级越低，优先级为0的索引的优先级最高。当用户创建或修改索引时如果不指定其优先级，优化器就按常规的代价估算来进行路径选择，选择代价最小的索引来生成执行计划。用户一般不需要为索引指定优先级。
TABLESPACE tablespacename
索引所属的表空间名
LOGGING | NOLOGGING
对索引进行的操作记日志或不记日志。
- NOLOGGING可以使对象上的操作产生的日志量最少，通常指定NOLOGGING可以改善大数据量操作的性能，对少量数据的操作只有很小的影响，在大数据量NOLOGGING操作结束后应当将对象改回LOGGING模式。除以下操作外，不建议对其它操作使用NOLOGGING模式：
- CREATE INDEX
- ALTER INDEX REBUILD
- INSERT INTO ... SELECT
- 大量的大对象数据操作
由于NOLOGGING模式下记录的少量日志无法满足数据库回滚和介质恢复的需要，你必须非常谨慎地使用这种模式，而且要与负责备份和恢复的人沟通之后才能使用。在执行NOLOGGING操作前应当为可能受影响的数据文件建立一个备份，一旦操作失败或发生事务回滚，必须从备份进行恢复，以避免产生数据不一致的情况。 执行NOLOGGING操作后，必须执行ALTER SYSTEM CHECKPOINT命令建立检查点强制数据刷新到磁盘上，并尽快为受影响的数据文件建立一个新的基准备份，从而避免由于介质失败而丢失对这些对象的后续修改。
对象之间的LOGGING属性是相互独立的，表与LOB字段、表与索引的LOGGING属性都不会互相影响。例如，如果想对索引在NOLOGGING模式下重建，只需执行ALTER INDEX ... REBUILD NOLOGGING. 并不需要对表进行NOLOGGING设置。
REVERSE
只有在访问方法为BTREE时可以使用此选项，标明此选项后BTREE将为倒序BTREE索引。
INIT size [ K | KB | M | MB | G | GB | T | TB ]
索引大小的初始值， K,KB,M,MB,G,GB,T,TB分别是可选的字节单位。
默认是M(兆字节)。
NEXT size [ K | KB | M | MB | G | GB | T | TB ]
索引大小的增长步长， K,KB,M,MB,G,GB,T,TB分别是可选的字节单位，增长步长为64k的倍数。
默认是M(兆字节)。
PCTINCREASE pctincsize
段增长步长的增长比例。每次段增长的大小比上次段增长的大小多pctincsize%
MAX size [ K | KB | M | MB | G | GB | T | TB ]
索引大小的最大值，K,KB,M,MB,G,GB,T,TB分别是可选的字节单位。
默认是M(兆字节)
BUFFER_POOL {KEEP|RECYCLE|DEFAULT|SEQUENCE}
BUFFER_POOL子句用于为指定的模式对象指定一个默认的缓存方式。
- 指定KEEP，表示尽量将该模式对象数据缓存在内存中；
- 指定RECYCLE，表示采用LRU算法换出该模式对象数据；
- 指定SEQUENCE，表示按顺序换出该模式对象数据；
指定DEFAULT时，表示采用系统默认的缓存方式，当前系统默认缓存方式为RECYCLE。
FILL fillsize
索引填充系数，详见 sys_segment（段信息） 的 PCTFREE 列的说明
INITRANS initrans
页面上的初始事务槽数。 当事务对页面进行首次更新操作时，需要先占用一个本页面里的事务槽。在此事务结束后，占用的事务槽才可被其它事务重新占用。 initrans取值范围是[1, maxtrans]。
不指定的话默认值是2
MAXTRANS maxtrans
页面上的事务槽扩展上限。 当页面的initrans事务槽不够用时，如果页面有空闲空间，可以自动进行事务槽的扩展，扩展到不超过maxtrans个事务槽。 maxtrans取值范围是[1, 255]。
不指定的话默认值是255
n
是表示前面的项可重复 n 次的占位符。
COMPRESS [integer] | NOCOMPRESS
- 指定COMPRESS，表示对索引键值压缩存储，通过指定COMPRESS关键字消除重复键列值，可以大幅降低磁盘存储空间。COMPRESS后跟integer以指定压缩列前的列数；
- 指定NOCOMPRESS，表示对索引键值不压缩存储。
默认是NOCOMPRESS。
COALESCE [NOT SUSPEND]
清理索引中的空页面。
- 指定 NOT SUSPEND，表示持续执行清理索引中空页面的命令，忽略其它事务阻塞。
- 不指定 NOT SUSPEND，表示在执行清理索引中空页面期间，若检测到阻塞其他事务，则中止清理操作。
默认不指定 NOT SUSPEND。
注解
该功能会清理索引中的空页面，并且一旦清理，无法再恢复这些页面的历史版本。对于开启时间很早的可串行化事务，或者执行闪回查询时，若对索引进行较旧版本的快照扫描，可能会出现以下错误：
ERROR, 索引操作出错, 快照过旧, 当前索引有过空页面清理操作，因此无法处理清理操作之前索引相关的事务。
MAINTENANCE | UNMAINTENANCE
索引处于维护状态或非维护状态
默认为非维护状态，即正常状态。
VISIBLE | INVISIBLE
索引可见或不可见状态。不可见索引依然会随数据表的改变而更新，但是不会被数据库内部使用。
默认为可见状态。
UNUSABLE
设置索引的状态为UNUSABLE
不能将临时表上的索引设置为UNUSABLE
alter_index_partitioning
更改水平分区索引的结构。
modify_index_default_attrs
修改分区索引的缺省属性。若修改对象不存在子分区，对象不支持缺省值修改。
add_hash_index_partition
为当前索引增加一个分区，只支持hash分区。
modify_index_partition
修改索引一级分区的真实属性，这里只允许修改UNUSABLE。
rename_index_partition
重命名索引分区的名字。
drop_index_partition
删除索引的一个分区。
split_index_partition
分裂索引中一个分区为两个，只支持range分区。
coalesce_index_partition
结合索引中分区，只支持hash分区，要求分区个数必须大于两个。
modify_index_subpartition
修改索引二级分区的真实属性，这里只允许修改UNUSABLE。
注解
ALTER INDEX 语句清理索引中的空页面，可以在清理前后通过视图 V_SEGMENT_INFO 中的 AVAILPAGES 查看清理的效果：SELECT AVAILPAGES FROM V_SEGMENT_INFO WHERE RELID=(SELECT OID FROM SYS_CLASS WHERE RELNAME = '索引名称');
ALTER INDEX语句可以根据索引依赖的表的数据量变化，更改索引的物理存储参数。
ALTER INDEX语句可以用于重建损坏了的系统索引．尽管理论上这种情况决不应该发生，但实际上索引可能因为硬件失效而损坏．ALTER INDEX提供了一种恢复手段。
如果要修改临时表上的索引，需要注意以下事项：
- 每一个会话第一次向临时表中插入数据时会创建一个与会话相关联的数据段，即临时表的实例化（通过视图v_sys_temp_instance可以查看当前所有的临时表实例），临时表会与该会话绑定，当事务或会话结束时临时表与该事务或会话的绑定将会解除。
- 临时表没有与其他事务或会话绑定时才可以修改临时表上的索引。
- 修改全局临时表索引时支持指定存储参数，但不支持rebulid和nologging；修改本地临时表索引时没有限制。
示例¶
示例1: 更改索引的物理存储参数
-- 清理环境
DROP INDEX idx1 CASCADE;
DROP TABLE tab1 CASCADE;
-- 创建表
CREATE TABLE tab1(a INT, b INT);
-- 创建索引
CREATE INDEX idx1 on tab1(a);
-- 更改索引的物理存储参数
ALTER INDEX idx1 LOGGING NEXT 4M MAXSIZE UNLIMITED;
-- 删除索引和表
DROP INDEX idx1;
DROP TABLE tab1;
示例2: 重建索引
-- 清理环境
DROP INDEX idx2 CASCADE;
DROP TABLE tab2 CASCADE;
-- 创建表
CREATE TABLE tab2(a INT, b INT);
-- 创建索引
CREATE INDEX idx2 on tab2(a);
-- 重建索引
ALTER INDEX idx2 REBUILD LOGGING INIT 2M NEXT 4M MAXSIZE UNLIMITED FILL 70 SPLIT 50;
-- 删除索引和表
DROP INDEX idx2;
DROP TABLE tab2;
示例3: 禁用索引
-- 清理环境
DROP INDEX idx3 CASCADE;
DROP TABLE tab3 CASCADE;
-- 创建表
CREATE TABLE tab3(a INT, b INT);
-- 创建索引
CREATE INDEX idx3 on tab3(a);
-- 禁用索引
ALTER INDEX idx3 UNUSABLE;
-- 删除索引和表
DROP INDEX idx3;
DROP TABLE tab3;
示例4: 索引可见性
-- 清理环境
DROP INDEX idx4 CASCADE;
DROP TABLE tab4 CASCADE;
-- 创建表
CREATE TABLE tab4(a INT, b INT);
-- 创建索引
CREATE INDEX idx4 on tab4(a);
-- 索引默认为可见状态
ALTER INDEX idx4;
EXPLAIN SELECT a FROM tab4;
QUERY PLAN(text)
----------------------
Index Scan using IDX4(
Fast Index Scan) on T
AB4 (cost=0.00..82.9
2 rows=8192 width=4)
总数目：1
-- 将索引设置为不可见状态
ALTER INDEX idx4 INVISIBLE;
EXPLAIN SELECT a FROM tab4;
QUERY PLAN(text)
----------------------
Seq Scan on TAB4 (cos
t=0.00..89.92 rows=81
92 width=4)
总数目：1
-- 将索引设置为可见状态
ALTER INDEX idx4 VISIBLE;
EXPLAIN SELECT a FROM tab4;
QUERY PLAN(text)
----------------------
Index Scan using IDX4(
Fast Index Scan) on T
AB4 (cost=0.00..82.9
2 rows=8192 width=4)
总数目：1
-- 删除索引和表
DROP INDEX idx4;
DROP TABLE tab4;
-- 索引不存在时，将索引修改为可见状态
-- 使用 IF EXISTS 时，跳过操作，不报错
ALTER INDEX IF EXISTS idx4 VISIBLE;
-- 不使用 IF EXISTS 时，语句报错
ALTER INDEX idx4 VISIBLE;
ERROR, 索引 "IDX4" 不存在或无权访问
示例5: 清理索引中的空页面
-- 清理环境
DROP INDEX idx5;
DROP TABLE tab5;
-- 创建表
CREATE TABLE tab5(a VARCHAR(1000));
-- 创建索引
CREATE INDEX idx5 on tab5(a);
-- 插入值
INSERT INTO tab5 VALUES(REPEAT('a',1000));
INSERT INTO tab5 VALUES(REPEAT('c',1000));
INSERT INTO tab5 VALUES(REPEAT('e',1000));
INSERT INTO tab5 VALUES(REPEAT('g',1000));
INSERT INTO tab5 VALUES(REPEAT('i',1000));
INSERT INTO tab5 VALUES(REPEAT('k',1000));
INSERT INTO tab5 SELECT * FROM tab5 LIMIT 300;
INSERT INTO tab5 SELECT * FROM tab5 LIMIT 300;
INSERT INTO tab5 SELECT * FROM tab5 LIMIT 300;
INSERT INTO tab5 SELECT * FROM tab5 LIMIT 300;
-- 清空页面
DELETE FROM tab5;
-- 等待 200 秒后，再执行清理索引空页面。可以通过执行前后 V_SEGMENT_INFO 视图中 AVAILPAGES 的变化观察清理操作的效果。
-- 执行时若检测到阻塞其他事务，则会有警告信息：WARNING, 操作已终止。该命令运行期间，检测到其他事务被阻塞。如果希望命令持续执行，请使用“ALTER INDEX index_name COALESCE NOT SUSPEND”
ALTER INDEX IDX5 COALESCE;
-- 删除索引和表
DROP INDEX idx5;
DROP TABLE tab5;