索引定义语句
为了提高系统的查询效率,DM 系统提供了索引。但也需要注意,索引会降低那些影响 索引列值的命令的执行效率,如INSERT、UPDATE、DELETE的性能,因为DM不但要维护 基表数据还要维护索引数据。
语法格式
CREATE [OR REPLACE] [CLUSTER|NOT PARTIAL][UNIQUE | BITMAP| SPATIAL] INDEX < 索引名> ON [<模式名>.]<表名>(<索引列定义>{,<索引列定义>}) [GLOBAL] [<STORAGE 子句>] [NOSORT] [ONLINE]; <索引列定义>::= <索引列表达式>[ASC|DESC] <STORAGE子句>::=<STORAGE子句1>|<STORAGE子句2> <STORAGE子句1>::= STORAGE(<STORAGE1项> {,<STORAGE1项>}) <STORAGE1项> ::= [INITIAL <初始簇数目>] | [NEXT <下次分配簇数目>] | [MINEXTENTS <最小保留簇数目>] | [ON <表空间名>] | [FILLFACTOR <填充比例>]| [BRANCH <BRANCH数>]| [BRANCH (<BRANCH数>, <NOBRANCH数>)]| [NOBRANCH ]|
[<CLUSTERBTR>]| [SECTION (<区数>)]| [STAT NONE] <STORAGE子句2>::= STORAGE(<STORAGE2项> {,<STORAGE2项>}) <STORAGE2项> ::= [ON <表空间名>]|[STAT NONE]
参数
- UNIQUE 指明该索引为唯一索引;
- BITMAP 指明该索引为位图索引;
- SPATIAL 指明该索引为空间索引;
- CLUSTER 指明该索引为聚簇索引(也叫聚集索引),不能应用到函数索引中;
- NOT PARTIAL 指明该索引为非聚簇索引,缺省即为非聚簇索引;
- <索引名> 指明被创建索引的名称,索引名称最大长度128字节;
- 模式名> 指明被创建索引的基表属于哪个模式,缺省为当前模式;
- <表名> 指明被创建索引的基表的名称;
- <索引列定义> 指明创建索引的列定义。其中空间索引列的数据类型必须是DMGEO 包内的空间类型,如ST_GEOMETRY等;
- <索引列表达式> 指明被创建的索引列可以为表达式;
- GLOBAL 指明该索引为全局索引,仅堆表的水平分区表支持该选项,非水平分区 表忽略该选项。堆表上的PRIMARY KEY会自动变为全局索引;
- ASC 递增顺序;
- DESC 递减顺序;
- <STORAGE子句> 普通表的索引参考<STORAGE子句1>,HUGE表的索引参考 <STORAGE子句2>;
- <STORAGE子句1>中,BRANCH和NOBRANCH只能用以指定聚集索引。
- NOSORT 指明该索引相关的列已按照索引中指定的顺序有序,不需要在建索引时 排序,提高建索引的效率。若数据非有序却指定了NOSORT,则在建索引时会报错;
- ONLINE 表示支持异步索引,即创建索引过程中可以对索引依赖的表做增、删、 改操作。
举例说明
例1 假设具有DBA权限的用户在VENDOR表中,以 VENDORID为索引列建立索引S1, 以ACCOUNTNO,NAME为索引列建立唯一索引S2。
`CREATE INDEX S1 ON PURCHASING.VENDOR (VENDORID); CREATE UNIQUE INDEX S2 ON PURCHASING.VENDOR` (ACCOUNTNO, NAME);
例 2 假设具有 DBA 权限的用户在 SALESPERSON 表中,需要查询比去年销售额超过 20 万的销售人员信息,该过滤条件无法使用到单列上的索引,每次查询都需要进行全表扫 描,效率较低。如果在SALESTHISYEAR-SALESLASTYEAR上创建一个函数索引,则可以 较大程度提升查询效率。
CREATE INDEX INDEX_FBI ON SALES.SALESPERSON(SALESTHISYEAR-SALESLASTYEAR);
例3 使用空间索引: --创建含空间索引类型的表
DROP TABLE testgeo; CREATE TABLE testgeo (id int, name varchar(20) , geo ST_polygon);
–创建空间索引
create spatial indeX spidx on testgeo (geo);
–删除空间索引
spidx DROP INDEX spidx;
索引修改语句
为了满足用户在建立索引后还可随时修改索引的要求,DM 系统提供索引修改语句,包 括修改索引名称、设置索引的查询计划可见性、改变索引有效性、重建索引和索引监控的功 能。 语法格式
`ALTER INDEX [<模式名>.]<索引名> <修改索引定义子句> <修改索引定义子句> ::= RENAME TO [<模式名>.]<索引名>| <INVISIBLE | VISIBLE> <UNUSABLE>| <REBUILD>[NOSORT][ONLINE]| <MONITORING | NOMONITORING> USAGE
参数
- <模式名> 索引所属的模式,缺省为当前模式;
- <索引名> 索引的名称。
图例 - 语句功能
- 供具有DBA角色的用户或该索引所属基表的拥有者或具有ALTER ANY INDEX的 用户修改索引;
- 当索引修改成INVISIBLE时,查询语句的执行计划不会使用该索引,该索引相关 的计划不会生成,用别的计划代替。修改成VISIBLE,则会生成索引相关的计划。 默认是VISIBLE;
- 当指定 UNUSABLE 时,索引将被置为无效状态,系统将不再维护此索引,若此索 引被用于保证数据唯一性时,则此时不能够对表更新数据,可以在 SYSOBJECTS 的 VALID 字段查看该值。处于无效状态的索引,可以利用 REBUILD 来重建,或 者先删除该索引再新建该索引。TRUNCATE表会将该表所有失效索引置为生效;
- 当指定REBUILD时,将置索引的状态为生效状态。NOSORT指定重建时不需要排 序,ONLINE子句表示重建时使用异步创建逻辑,在过程中可以对索引依赖的表做 增、删、改操作;
- MONITORING USAGE对指定索引进行监控;NOMONITORING USAGE对指定索引 取消监控。索引监控(MONITORING USAGE)仅支持对用户创建的二级索引进行 监控,且不支持监控虚索引、系统索引、聚集索引、数组索引,相关监控信息可查 看动态视图V$OBJECT_USAGE。
使用说明 - 使用者应拥有DBA权限或是该索引所属基表的拥有者;
- <INVISIBLE | VISIBLE>仅支持表的二级索引修改。对聚集索引不起作用;
- 仅支持对二级索引的修改,不支持位图索引、聚集索引、虚索引、列 存储表、水平分区子表和临时表上索引的修改;
- 是 UNUSABLE 的逆向操作,支持和不支持修改的索引类型同 UNUSABLE;
- 当INI参数MONITOR_INDEX_FLAG为1或2时,使用ALTER INDEX方式设置 索引监控失效。
举例说明
例1 具有DBA权限的用户需要重命名S1索引可用以下语句实现。
`ALTER INDEX PURCHASING.S1 RENAME TO PURCHASING.S2;`
例2 当索引为VISIBLE时,查询语句执行计划如下:
`CREATE TABLE TEST ( "C1" INT, "C2" INT); CREATE INDEX INDEX_C1 ON TEST (C1); explain select c1 from test; 1 #NSET2: [0, 1, 12] 2 #PRJT2: [0, 1, 12]; exp_num(2), is_atom(FALSE) 3 #SSCN: [0, 1, 12]; INDEX_C1(TEST)`
修改索引为INVISIBLE后,查询语句执行计划如下:
alter index index_C1 INVISIBLE; explain select c1 from test; 1 #NSET2: [0, 1, 12] 2 #PRJT2: [0, 1, 12]; exp_num(2), is_atom(FALSE) 3 #CSCN2: [0, 1, 12]; INDEX33555442(TEST)
索引删除语句
DM系统允许用户在建立索引后还可随时删除索引。 语法格式 DROP INDEX [<模式名>.]<索引名>; 参数 1. <模式名> 指明被删除索引所属的模式,缺省为当前模式; 2. <索引名> 指明被删除索引的名称。
举例说明 例 具有DBA权限的用户需要删除S2索引可用以下语句实现。
DROP INDEX PURCHASING.S2; 3