Oracle sql语句优化
1:查询的数据量比较大的时候采用建立索引的方式
之前写了一个sql 查询速度在24秒
SELECT distinct sp.mr_rte_cd, cardinfo.acct_id, sp.sp_id, cardinfo.mtr_id, (select pn.entity_name from ci_per_name pn, ci_acct_per ap, ci_acct acct, cm_sp_acct v where ap.per_id = pn.per_id and ap.acct_id = acct.acct_id and v.SP_ID = sp.sp_id and v.ACCT_ID = acct.acct_id and pn.prim_name_sw = 'Y') entity_name, (select v.address1 from cm_sp_acct v where v.SP_ID = sp.sp_id) address, (select Max(info.read_dttm) from cm_bus_cardinfo info where info.sp_id = sp.sp_id) lastReadDate, (select a.cm_mr_base from cm_bus_cardinfo a where a.sp_id = sp.sp_id and a.read_dttm = (select Max(info.read_dttm) from cm_bus_cardinfo info where info.sp_id = sp.sp_id)) lastMrRead, (select a.cm_card_remain from cm_bus_cardinfo a where sp.sp_id = a.sp_id and a.read_dttm = (select Max(info.read_dttm) from cm_bus_cardinfo info where info.sp_id = sp.sp_id)) lastCardRemain, (select a.cm_mtr_remain from cm_bus_cardinfo a where a.sp_id = sp.sp_id and a.read_dttm = (select Max(info.read_dttm) from cm_bus_cardinfo info where info.sp_id = sp.sp_id)) lastMtrRemain, trunc(SYSDATE) mrDTTM, -1 regReading,-1 cardRemain, -1 mtrRemain, (select decode(sum(bus.cm_tran_qty), 0, sum((bus.cm_price * bus.cm_tran_amt))) from cm_cpu_gas_tran_bus bus where bus.sp_id = sp.sp_id and bus.cm_tran_status = 'Y' and bus.cm_tran_type in ('01', '04')) qtyCount, (select a.full_scale from ci_reg a where a.mtr_id = cardinfo.mtr_id) fullScale, (select mtr.mtr_type_cd from ci_mtr mtr where mtr.mtr_id = cardinfo.mtr_id) mtrTypeCd FROM ci_sp sp, cm_bus_cardinfo cardinfo WHERE 1 = 1 AND cardinfo.sp_id = sp.sp_id AND sp.mr_rte_cd = 'GF0028'
在from和where部分的表字段上加了索引,查询就是瞬间的事情
建立索引:
Create Index read_dttm On cm_bus_cardinfo(read_dttm)
read_dttm ----索引名称
cm_bus_cardinfo ---表名
read_dttm ---表字段名称
2:查询的sql语句尽量减少使用null和in做为筛选条件,因为索引会失效