求助帮忙优化一条SQL语句

Question

SELECT   SUBSTR (a.survey_id, 1, 12) AS apply_num,
         a.survey_id,
         lot_number,
         state,
         old_data_flag,
         IDLE_TIME,
         lot_use_desc,
         lot_use,
         approval_time,
         approval_num,
         group_approval_num,
         group_approval_time,
         supply_mode,
         approval_area,
         CORP_NAME,
         prj_name,
         data_type,
         objectid,
         c.rep_num AS survey_num
          FROM   land_apply_parcel a, gis_sys.s_report c, v_all_achi_case_t d,gis_sys.S_CASE_REPORT f，
           (select t.land_id from corebase.land_project_relation_t t
            where not exists (select b.project_id
                  from corebase.land_inform_bill b
                 where to_char(b.inform_proceeding) = '1'
                   and b.project_id = t.project_id)
            and not exists (
               select aa.project_id from corebase.land_projects aa,corebase.land_apply bb
               where aa.apply_id=bb.apply_id and bb.sub_business_code in    ('GENGDI_FUKEN_LIXIANG_5','GENGDI_FUKEN_YANSHOU_5')
               and t.project_id=aa.project_id
           )
          ) e
         WHERE       SUBSTR (a.survey_id, 13) = TO_CHAR (c.reportid)
                 AND old_data_flag IS NULL
                 AND state = 1
                 AND SUBSTR(a.survey_id,1,12) = d.apply_num
                 and SUBSTR (a.survey_id, 1, 12) = f.apply_num
                and to_char(e.land_id) = to_char(f.reportid)

Answer 1

其实主要也是要把E表的数据 不用not exists方式来取 

Answer 2

建立索引！
 create index E_index on E (col1_name,col2_name,col3_name,...) 

Answer 3

现在是要把not exist这种方式 改成用表连接来做 比not exist有效率吧

Answer 4

SELECT SUBSTR(a.survey_id, 1, 12) AS apply_num,
       a.survey_id,
       lot_number,
       state,
       old_data_flag,
       IDLE_TIME,
       lot_use_desc,
       lot_use,
       approval_time,
       approval_num,
       group_approval_num,
       group_approval_time,
       supply_mode,
       approval_area,
       CORP_NAME,
       prj_name,
       data_type,
       objectid,
       c.rep_num AS survey_num
  FROM land_apply_parcel     a,
       gis_sys.s_report      c,
       v_all_achi_case_t     d,
       gis_sys.S_CASE_REPORT f,(select t.land_id
                                   from corebase.land_project_relation_t t
                                  where not exists
                                  (select b.project_id
                                           from corebase.land_inform_bill b
                                          where to_char(b.inform_proceeding) = '1'
                                            and b.project_id = t.project_id)
                                    and not exists
                                  (select aa.project_id
                                           from corebase.land_projects aa,
                                                corebase.land_apply    bb
                                          where aa.apply_id = bb.apply_id
                                            and bb.sub_business_code in
                                                ('GENGDI_FUKEN_LIXIANG_5',
                                                 'GENGDI_FUKEN_YANSHOU_5')
                                            and t.project_id = aa.project_id)) e
 WHERE SUBSTR(a.survey_id, 13) = TO_CHAR(c.reportid)
   AND old_data_flag IS NULL
   AND state = 1
   AND SUBSTR(a.survey_id, 1, 12) = d.apply_num
   and SUBSTR(a.survey_id, 1, 12) = f.apply_num
   and to_char(e.land_id) = to_char(f.reportid)
只是把语句整理了下，把逻辑关系理清楚吧

Answer 5

一般来说是否存在比表关联效率要高吧。可以优化一下，把两个NOT EXISTS合并成一个。
还想优化就只能考虑建索引了。

Answer 6

恩 就是要优化下2个not exists这里 
写不来 希望达人帮下忙