|
创建一个新列,在新列上建立索引
例如:在AAA表中加入一列,AA4 此列是AA1列的小写
然后在AA4上建立索引,当以AA1列的小写为查询条件时
优化器会使用AA4上的索引。
CREATE TABLE DB2INST1.AAA ("AA1" CHARACTER (10) NOT NULL , "AA2" CHARACTER (10) , "AA3" CHARACTER (10) , "AA4" CHARACTER (10) NOT NULL GENERATED ALWAYS AS (lcase(aa1)) )
create index idx_aa1 on aaa(aa4)
select count(*) from aaa where lcase(aa1)='aa'
下面是在一个已经存在数据的表上做的操作:
SET INTEGRITY FOR INSTANCE1.DETAILTBL OFF;
ALTER TABLE INSTANCE1.DETAILTBL
ADD COLUMN year_dob GENERATED ALWAYS (YEAR(DOB));
SET INTEGRITY FOR INSTANCE1.DETAILTBL
IMMEDIATE CHECKED FORCE GENERATED;
CREATE INDEX
INSTANCE1.IDX0001 ON INSTANCE1.DETAILTBL (PPLINX ASC, YEAR_DOB ASC) PCTFREE 10 MINPCTUSED 10
The DB2 optimizer will replace YEAR(DOB) with YEAR_DOB in your queries
and thsu pick up the index. |
|