这样的SQL句子,SELECT ACCT_ID, EXT_ID, EXT_ID_TY, JVL_COUNT as count FROM TA_SPEAR_JVL_DATA GROUP BY ACCT_ID, EXT_ID, EXT_ID_TY, JVL_COUNT HAVING JVL_COUNT > 0 如果不用游标,如何提高速度?
游标只是记录集,对查询上没有提供速度的说法。你可以建索引或者建一个物化视图都可以加快速度。 建索引: create index inx1 on TA_SPEAR_JVL_DATA(ACCT_ID, EXT_ID, EXT_ID_TY, JVL_COUNT );建物化视图: create materialize view mv1 build immediate refresh on commit enable query rewrite as SELECT ACCT_ID, EXT_ID, EXT_ID_TY, JVL_COUNT as count FROM TA_SPEAR_JVL_DATA GROUP BY ACCT_ID, EXT_ID, EXT_ID_TY, JVL_COUNT ;
象上所说,建索引,对SQL语句也可优化,改成: select a.acct_id,a.ext_id,ext_id_ty,a.jvl_count as count from ta_spear_jvl_data a where a.jvl_count>0 group by a.acct_id,a.ext_id,a.ext_id_ty,a.jvl_count 列名前加表名可以减少查询数据字典的时间,在分组前应该尽量的减少分组数据量
建索引:
create index inx1 on TA_SPEAR_JVL_DATA(ACCT_ID, EXT_ID, EXT_ID_TY, JVL_COUNT );建物化视图:
create materialize view mv1
build immediate
refresh on commit
enable query rewrite
as
SELECT ACCT_ID, EXT_ID, EXT_ID_TY, JVL_COUNT as count
FROM TA_SPEAR_JVL_DATA
GROUP BY ACCT_ID, EXT_ID, EXT_ID_TY, JVL_COUNT ;
select a.acct_id,a.ext_id,ext_id_ty,a.jvl_count as count from ta_spear_jvl_data a
where a.jvl_count>0
group by a.acct_id,a.ext_id,a.ext_id_ty,a.jvl_count
列名前加表名可以减少查询数据字典的时间,在分组前应该尽量的减少分组数据量