目录
1、管理索引的准则
1.1索引正确的表和列
1.如果需要经常地检索大表中的少量的行，就为查询键创建索引；
2.为了改善多个表的连接的性能，可为连接列创建索引；
3.主键和唯一键自动具有索引；
4.小表不需要索引。
1.2合适的组合索引
如果查询中有多个字段组合定位，则不应为每个字段单独创建索引，而应该创建一个组合索引。
当两个或多个字段都是等值查询时，组合索引中各个列的前后关系是无关紧要的。但是如果是非等值查询时，要想有效利用组合索引，则应该按等值字段在前，非等值字段在后的原则创建组合索引。
1.3限制每个表的索引的数量
如果一个表主要仅用于读，则索引多就有好处；如果一个表经常被更新，则索引不宜多建。索引越多，修改表数据的开销就越大。
1.4为每个索引指定表空间
将表及其索引放在不同的表空间（在不同磁盘上）产生的性能比放在相同的表空间更好，因为这样做减少了磁盘竞争。
2、创建索引
2.1创建聚集索引
DM8 中表（列存储表和堆表除外）都是使用 B 树索引结构管理的，每一个普通表都有且仅有一个聚集索引，当建表语句未指定聚集索引键时，DM8 的默认聚集索引键是 ROWID。若指定索引键，表中数据都会根据指定索引键排序。
建表后，DM8 也可以用创建新聚集索引的方式来重建表数据，并按新的聚集索引排序。
CREATE CLUSTER INDEX clu_emp_name ON emp(ename);新建聚集索引会重建这个表以及其所有索引，包括二级索引、函数索引，是一个代价非常大的操作。因此，最好在建表时就确定聚集索引键。
2.2创建唯一索引
唯一索引可以保证表上不会有两行数据在键列上具有相同的值。
CREATE UNIQUE INDEX dept_unique_index ON dept (dname) STORAGE (ON users);2.3创建函数索引
基于对表中列进行计算后的结果创建索引。如果没有函数索引，那么任何在列上执行了函数的查询都不能使用这个列的索引。
1.函数索引创建时要和使用时保持一致
2.不建议使用自定义函数
3.表达式不支持集函数和不确定函数，不确定函数为每次执行得到的结果不确定，系统中不确定函数包括：RAND、SOUNDEX、CURDATE、CURTIME、CURRENT_DATE、CURRENT_TIME、CURRENT_TIMESTAMP、GETDATE、NOW、SYSDATE、CUR_DATABASE、DBID、EXTENT、PAGE、SESSID、UID、USER、VSIZE、SET_TABLE_OPTION、SET_INDEX_OPTION、UNLOCK_LOGIN、CHECK_LOGIN、GET_AUDIT、CFALGORITHMSENCRYPT、SF_MAC_LABEL_TO_CHAR、CFALGORITHMSDECRYPT、BFALGORITHMSENCRYPT、SF_MAC_LABEL_FROM_CHAR、BFALGORITHMSDECRYPT、SF_MAC_LABEL_CMP；
举例说明：
SQL> create table test.t1 as select * from "DMHR"."EMPLOYEE";
操作已执行
已用时间: 20.757(毫秒). 执行号:901.
SQL> EXPLAIN select * from test.t1 where to_char(HIRE_DATE,'yyyy-mm-dd') ='2012-03-27';
1 #NSET2: [1, 21, 285]
2 #PRJT2: [1, 21, 285]; exp_num(12), is_atom(FALSE)
3 #SLCT2: [1, 21, 285]; exp11 = '2012-03-27'
4 #CSCN2: [1, 856, 285]; INDEX33555625(T1); btr_scan(1)
已用时间: 1.687(毫秒). 执行号:0.
SQL> create index idx_func_t1 on TEST.T1(to_char(HIRE_DATE,'yyyy-mm-dd'));
操作已执行
已用时间: 17.666(毫秒). 执行号:901.
SQL> EXPLAIN select * from test.t1 where to_char(HIRE_DATE,'yyyy-mm-dd') ='2012-03-27';
1 #NSET2: [1, 21, 285]
2 #PRJT2: [1, 21, 285]; exp_num(12), is_atom(FALSE)
3 #BLKUP2: [1, 21, 285]; IDX_FUNC_T1(T1)
4 #SSEK2: [1, 21, 285]; scan_type(ASC), IDX_FUNC_T1(T1), scan_range['2012-03-27','2012-03-27']
已用时间: 1.605(毫秒). 执行号:0.
SQL> 以上可见，建了函数索引idx_func_t1为to_char(HIRE_DATE,'yyyy-mm-dd')后，再次查询，执行计划走索引idx_func_t1，查询效率有提升。
2.4创建位图索引
位图索引主要针对含有大量相同值的列而创建。
CREATE BITMAP INDEX S1 ON PURCHASING.VENDOR (VENDORID);1.不支持对大字段创建位图索引；
2.不支持对存在 CLUSTER KEY 的表创建位图索引；
3.MPP 环境下不支持位图索引的创建；
4.不支持全局位图索引；
3、SQL 语句中使用索引
如果 SQL 语句想使用某一个索引，则SQL 语句的查询项和 WHERE 子句的过滤项中必须包含该索引的全部索引键。
CREATE TABLE T2 (ID int,NAME varchar(20),DEPARTMENT varchar(20),SALARY INT);
CREATE CLUSTER INDEX S21 on T2(ID); //聚集索引
CREATE INDEX S22 on T2(NAME,DEPARTMENT);
CREATE INDEX S23 on T2(NAME);
CREATE INDEX S24 on T2(DEPARTMENT);
例 1 使用聚集索引 S21
EXPLAIN SELECT SALARY FROM T2 WHERE ID=10;
例 2 使用非聚集索引 S22
EXPLAIN SELECT DEPARTMENT FROM T2 WHERE NAME LIKE'%Zhang' AND DEPARTMENT='A部门';
EXPLAIN SELECT DEPARTMENT FROM T2 WHERE NAME='Zhang San';举例说明：
SQL> CREATE TABLE test.T2 (ID int,NAME varchar(20),DEPARTMENT varchar(20),SALARY INT);
操作已执行
已用时间: 7.072(毫秒). 执行号:902.
SQL> insert into test.t2 select employee_id,employee_name,identity_card,salary from test.t1;
影响行数 856
已用时间: 2.495(毫秒). 执行号:903.
SQL> commit;
操作已执行
已用时间: 1.654(毫秒). 执行号:904.
SQL> set schema test;
操作已执行
已用时间: 0.302(毫秒). 执行号:0.
SQL> CREATE CLUSTER INDEX S21 on T2(ID);
操作已执行
已用时间: 62.835(毫秒). 执行号:906.
SQL> CREATE INDEX S22 on T2(NAME,DEPARTMENT);
操作已执行
已用时间: 12.905(毫秒). 执行号:907
SQL> CREATE INDEX S23 on T2(NAME);
操作已执行
已用时间: 12.786(毫秒). 执行号:908.
SQL> CREATE INDEX S24 on T2(DEPARTMENT);
操作已执行
已用时间: 13.661(毫秒). 执行号:909.
SQL> EXPLAIN SELECT SALARY FROM T2 WHERE ID=1001;
1 #NSET2: [1, 21, 8]
2 #PRJT2: [1, 21, 8]; exp_num(1), is_atom(FALSE)
3 #CSEK2: [1, 21, 8]; scan_type(ASC), S21(T2), scan_range[1001,1001]
已用时间: 1.061(毫秒). 执行号:0.
SQL> EXPLAIN SELECT DEPARTMENT FROM T2 WHERE NAME LIKE'马%' AND DEPARTMENT='340102196202303000';
1 #NSET2: [1, 1, 96]
2 #PRJT2: [1, 1, 96]; exp_num(1), is_atom(FALSE)
3 #SLCT2: [1, 1, 96]; T2.DEPARTMENT = '340102196202303000'
4 #SSEK2: [1, 1, 96]; scan_type(ASC), S22(T2), scan_range[('马',min),('驭',min))
已用时间: 0.697(毫秒). 执行号:0.
SQL> EXPLAIN SELECT DEPARTMENT FROM T2 WHERE NAME LIKE'马%' ;
1 #NSET2: [1, 32, 96]
2 #PRJT2: [1, 32, 96]; exp_num(1), is_atom(FALSE)
3 #SSEK2: [1, 32, 96]; scan_type(ASC), S22(T2), scan_range[('马',min),('驭',min))
已用时间: 0.736(毫秒). 执行号:0.
SQL> EXPLAIN SELECT * FROM T2 WHERE NAME LIKE '马%';
1 #NSET2: [1, 32, 116]
2 #PRJT2: [1, 32, 116]; exp_num(5), is_atom(FALSE)
3 #BLKUP2: [1, 32, 116]; S23(T2)
4 #SSEK2: [1, 32, 116]; scan_type(ASC), S23(T2), scan_range['马','驭')
已用时间: 0.621(毫秒). 执行号:0.4、重建索引
可以使用重建索引来对索引的数据进行重组，使数据更加紧凑，并释放不需要的空间，从而提高访问效率和空间效率。DM8 提供的重建索引的系统函数为：
SP_REBUILD_INDEX(SCHEMA_NAME varchar(256), INDEX_ID int);生产环境建议使用 online 方式重建，不影响表的 DML 操作:
alter index HRTEST.IX_EMP01_EMPLOYEENAME rebuild ONLINE;5、删除索引
DROP INDEX IF EXISTS emp_ename;不能直接删除与已启用的 UNIQUE KEY 键或 PRIMARY KEY 键约束相关的索引。要删除一个与约束相关的索引，必须停用或删除该约束本身。
ALTER TABLE emp DROP CONSTRAINT pk_emp_name;6、查看索引信息
查看索引的定义
SELECT INDEXDEF(INDEX_ID int, PREFLAG int);INDEX_ID 为索引 ID，PREFLAG 表示返回信息中是否增加模式名前缀，0或1。
SQL> SELECT INDEXDEF(33555645, 1);
行号 INDEXDEF(33555645,1)
---------- ------------------------------------------------------------------------------------------------------
1 CREATE OR REPLACE CLUSTER INDEX "TEST"."S21" ON "TEST"."T2"("ID" ASC) STORAGE(ON "TBS", CLUSTERBTR) ;
已用时间: 1.777(毫秒). 执行号:913.数据字典：
select * from dba_indexes t where t.OWNER ='HRTEST';
select * from dba_ind_columns t where t.index_OWNER ='HRTEST';
--索引的有效性和可见性（只有有效且可见的索引，执行计划才会选择走该索引）
select t.INDEX_NAME, t.INDEX_TYPE, t.TABLE_NAME, t.TABLE_OWNER, t.status,
t.VISIBILITY from user_indexes t;