通过加索引对sql语句优化

今天看数据库的时候遇到这样一个SQL语句：

select

substr(a.djxh,6) as id,
       (a.nd || a.yf) DECL_YM,
       a.zspm_dm as LEVY_ITEM_ID,
       b.zsxmmc  as LEVY_ITEM_NAME, --||'--'||d.zspmmc
       100 as DECL_STATUS,
       a.nsqx_dm as TAX_TERM_CODE,
       c.nsqxmc  as TAX_TERM_NAME,
       to_char(a.sbqx, 'yyyyMMdd') as DECL_TREM,
       to_char(a.sjtb_sj, 'yyyyMM') as DATA_CREATE_YM,
       a.sjgsdq as area_code
       from
(
select djxh, nd, yf, zspm_dm, nsqx_dm, sbqx, sjtb_sj, sjgsdq,zsxm_dm
  from tci.sb_ysbtj
 where zfbz_1 is null
   or zfbz_1 = 'N'
   and exists (select *
          from (select to_number('10124' || i.id) as iid
                  from i_reg_account i
                 where mgr_dept in
                       (select t.org_id
                          from i_organization t
                         where t.isdirectguanhu = 1
                         start with (t.org_id = 24300900000)
                        connect by prior t.org_id = t.parent_id)) d
         where djxh = iid)
   and trim(yf) = to_char(sysdate, 'MM')
   and nd = to_char(SysDate, 'YYYY')

) a left join dm_gy_zsxm b
on a.zsxm_dm = b.zsxm_dm
left join dm_gy_nsqx  c
on a.nsqx_dm = c.nsqx_dm

 

sb_ysbtj有900万的数据量，查询了很久都没有出来。后来网上找资料别人说第一要加上索引。后来给每个需要查询的列都加上了索引，速度快了很多。

 

转载于:https://www.cnblogs.com/andrew-chen/p/5001498.html