select substr(w.salecode, 1, 2),
sum(w.num),
'1' flag
from tbbuss w,
(SELECT icode from tbitb) purchem
where w.icode= purchem.icode
and w.date>= '31-12月-07'
and w.date<= '25-2月-08'
and (substr(w.purcode, 1, 2) >
substr(w.salecode, 1, 2) or
substr(w.purcode, 1, 2) < substr(w.purcode, 1, 2))
group by substr(w.salecode, 1, 2)
sum(w.num),
'1' flag
from tbbuss w,
(SELECT icode from tbitb) purchem
where w.icode= purchem.icode
and w.date>= '31-12月-07'
and w.date<= '25-2月-08'
and (substr(w.purcode, 1, 2) >
substr(w.salecode, 1, 2) or
substr(w.purcode, 1, 2) < substr(w.purcode, 1, 2))
group by substr(w.salecode, 1, 2)
给where条件后的字段加索引,用了函数的可以考虑加函数索引,
另外一个表的索引不要建太多.
create index idx_tbbuss on tbbuss(purcode)
单就这个sql来说,应该建立如下index:
CREATE INDEX i_tbitb_icode ON tbitb(icode);
CREATE INDEX i_TBBUSS_date ON TBBUSS(DATE,icode);ps:
如果tbbuss.date是日期类型的,你需要使用to_date把你给的常量转换。
如果tbbuss.date是字符形的,如果存储的是类似"31-12月-07"这样的格式,估计取出的数据会不对;应该转换成"YYYY-MM-DD"格式。
(icode, date,
substr(purcode,1,2),
substr(salecode,1,2))
tablespace USERS这个索引用不到
该怎么建这个索引
substr(w.purcode, 1, 2) < substr(w.purcode, 1, 2)有意义吗?索引:
tbitb.icode
substr(tbbuss.salecode, 1, 2)