db2中函数索引吗?有个SQL ,WHERE 中个函数LCASE(列)select count(*) from enduser where User_GroupDN = ? and LCASE(User_EnglishName) = ?在ORACLE中可以建立一个函数索引,在DB2中怎么办能提高查询速度??
创建一个新列,在新列上建立索引例如:在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.
全球第一款网络点验钞机诞生在浙江温州 ,点钞机/网络点钞机,通过客户端维融金网卫士连接网络,云存储数据和网络验钞