【SQL】index索引

IQ索引类型

– CMP： compare index
Indexes relationship between any two distinct columns with identical data types, precision, and scale.

– WD： containment index
Stores words from a column string of CHAR, VARCHAR, or LONG VARCHAR data.

– DATE & DTTM：date & datetime
Processes queries involving date or datetime quantities

– FP： fast projection
Optimizes projections and enables certain kinds of search conditions to be evaluated. Assigned by default to any permanent table.

– HG： high group
Used for join columns with integer data types. Handles GROUP BY and equality operations efficiently. Recommended for columns with more than 1000 distinct values.

– HNG： high non group
Used for high-cardinality operations involving ranges or aggregates.

– LF： low fast
Ideal for columns with under 1000 unique values, such as Yes/No, or number of dependents, or with SUM, AVG, and COUNT. Fastest index type in SAP IQ.
Use an LF index when a column has fewer than 1,000 unique values, or a column has fewer than 1,000 unique values and is used in a join predicate.
Never use an LF index for a column with 10,000 or more unique values. If the table has fewer than 25,000 rows, use an HG index, as fewer disk I/O operations are required for the same operation.

– TEXT
Used for unstructured data analytics functionality, an SAP IQ licensed option.

– TIME
Processes queries involving time quantities.

查看索引信息

DESCRIBE TABLE DBA.BUS;   //查看表信息
DESCRIBE INDEX FOR TABLE DBA.BUS;   //查看表的index

sp_iqdbspaceinfo;  //空间使用信息
sp_iqindex 'BUS';  //表索引信息
sp_iqindexinfo 'TABLE BUS';  //表索引详细信息

创建&删除索引

CREATE LF INDEX rcd_time_LF on DBA.BUS (RCD_TIME);
commit;

DROP INDEX rcd_time_LF;
commit;

使用索引

指定使用特定索引

SELECT XXX 
FROM BUS WITH(INDEX(rcd_time_LF)) 
where XXX;

索引优化查询

参考：SQL 强制指定索引加快查询速度

参考：如何让你的SQL运行得更快

—- 可见，所谓优化即where子句利用了索引，不可优化即发生了表扫描或额外开销。
1. 任何对列的操作都将导致表扫描，它包括数据库函数、计算表达式等等，查询时要尽可能将操作移至等号右边。
2. in、or子句常会使用工作表，使索引失效；如果不产生大量重复值，可以考虑把子句拆开；拆开的子句中应该包含索引。
3. 要善于使用存储过程，它使SQL变得更加灵活和高效。
从以上这些例子可以看出，SQL优化的实质就是在结果正确的前提下，用优化器可以识别的语句，充份利用索引，减少表扫描的I/O次数，尽量避免表搜索的发生。其实SQL的性能优化是一个复杂的过程，上述这些只是在应用层次的一种体现，深入研究还会涉及数据库层的资源配置、网络层的流量控制以及操作系统层的总体设计。

索引基本知识

http://www.cnblogs.com/AK2012/archive/2013/01/04/2844283.html
http://blog.csdn.net/liujiahan629629/article/details/7803929