10
CREATE ENCODED VECTOR INDEX myevi2
ON sales(state) INCLUDE ( SUM(saleamt) )
WITH 50 DISTINCT VALUES
Listing 1 – EVI Include aggregate example
When an SQL statement requests the SUM of the saleamt column for each state using a GROUP
BY clause that references the state column (that is, SELECT state, SUM(saleamt) FROM sales
GROUP BY state), DB2 for i can very quickly return the aggregated sales amount total for each
state by performing index-only access on the symbol table. All access and processing of the
underlying table rows can be eliminated.
As EVIs were created primarily to support business intelligence and ad-hoc query environments,
there are EVI creation and maintenance considerations, as well as recommendations — both of
which are covered in later sections.
Derived and sparse indexes
Starting with the IBM i 6.1 release, DB2 for i provides the ability to create derived key indexes (also
known as function-based indexes) and sparse indexes. Both radix and encoded vector indexes can
include key derivations and selection criteria in the index definition.
The derived key index support is a significant addition because on previous releases, the usage of a
function on a search predicate prevented the optimizer from using an index to speed up the selection
processing. The SELECT statements in Listing 2 are an example of the type of statements that were
unabl
e to ben
efit from the usage of an index in prior releases.
SELECT * FROM table1 WHERE UPPER(lastname) = 'CAIN'
SELECT * FROM table1 WHERE YEAR(shipdate) = 2010
Listing 2 - Example SQL statements with function-based search predicates
Now with the derived key index support, the indexes in Listing 3 can be created to give the query
optimizer a way to speed up query performance. The ability to create an index key from the result of
the UPPER function offers huge performance potential because many applications wrap the UPPER
function around character columns as a way to implement case-insensitive searches.
CREATE INDEX upper_lastname_ix ON table1( UPPER(lastname) )
CREATE INDEX shipdate_year_ix ON table1( YEAR(shipdate) )
Listing 3 - SQL derived index examples
Adding a WHERE clause to the Create Index statement creates what is known as a sparse index. A
normal index contains a key value for every row in the table while a sparse index only contain a key
value for a subset of the rows in the table. The condition on the WHERE clause effectively limits the
key values to only those rows in the table that meet the specified search condition or conditions.
Listing 4 contains two example sparse indexes. The first index, blu
e_ind
ex, only contains key values
for those rows in the items table that are blue in color. On the second index definition, notice that the
WHERE clause references a column (activeCust) that is not part of the key definition (cust_id). As a
result, the activeCust_index will only contain customerID values for those customers that have been
flagged as active with the activeCust column.
CREATE INDEX blue_index ON items(color) WHERE color='BLUE'
CREATE INDEX activeCust_index ON customers(customerID) WHERE activeCust=’Y’
Listing 4 - Sparse index definition examples
IBM DB2 for i indexing methods and strategies
剩余75页未读，继续阅读
weixin_38707061
- 粉丝: 2
- 资源: 921
我的内容管理
展开
最新资源
- AirKiss技术详解：无线传递信息与智能家居连接
- Hibernate主键生成策略详解
- 操作系统实验：位示图法管理磁盘空闲空间
- JSON详解：数据交换的主流格式
- Win7安装Ubuntu双系统详细指南
- FPGA内部结构与工作原理探索
- 信用评分模型解析：WOE、IV与ROC
- 使用LVS+Keepalived构建高可用负载均衡集群
- 微信小程序驱动餐饮与服装业创新转型：便捷管理与低成本优势
- 机器学习入门指南：从基础到进阶
- 解决Win7 IIS配置错误500.22与0x80070032
- SQL-DFS：优化HDFS小文件存储的解决方案
- Hadoop、Hbase、Spark环境部署与主机配置详解
- Kisso：加密会话Cookie实现的单点登录SSO
- OpenCV读取与拼接多幅图像教程
- QT实战：轻松生成与解析JSON数据
资源上传下载、课程学习等过程中有任何疑问或建议，欢迎提出宝贵意见哦~我们会及时处理！
点击此处反馈
