大虾们帮忙优化一下sql

[CSDN20110130]

1.
select distinct a.TERMINAL_ID,
a.TERMINAL_ADDR,
a.STATUS_CODE,
a.COLL_MODE,
a.TERMINAL_TYPE_CODE,
a.ID,
a.CP_NO,
e.NAME as terminal_name,
c.cons_no,
c.cons_name
from epma.R_TMNL_RUN a,
epma.g_line b,
epma.c_cons c,
epma.r_cp_cons_rela d,
epma.r_cp e,
epma.g_subs f,
epma.o_org g,
epma.g_subs_line_rela h
where a.CP_NO = e.CP_NO(+)
and a.cp_no = d.cp_no(+)
and d.cons_id = c.cons_id(+)
and d.cons_id = b.cons_id(+)
and b.line_id = h.line_id(+)
and h.subs_id = f.subs_id(+)
and c.org_no = g.org_no(+)
and g.org_no like '32401%%'
and a.TERMINAL_TYPE_CODE = '05'-- 低压集中器
order by a.TERMINAL_ID asc


2.
select A.IR_TASK_ID,
A.CP_NO,
A.APP_NO,
A.ASSET_ID,
A.ASSET_TYPE_CODE,
A.OBJECT_NO,
B.CONS_NAME,
B.MR_SECT_NO,
D.TERMINAL_ADDR,
D.COLL_MODE,
C.NAME,
A.CHG_MODE,
A.TERMINAL_TYPE_CODE,
A.CP_TYPE_CODE,
A.STATUS,
D.TERMINAL_ID,
null as HANDLE_TIME
from epma.R_TMNL_IR_TASK A,
epma.R_CP C,
epma.R_TMNL_RUN D,
epma.S_APP E,
epma.C_CONS B
where A.APP_NO = E.APP_NO
and A.CP_NO = C.CP_NO
and A.OBJECT_NO = B.CONS_NO(+)
and (A.CP_NO = D.CP_NO(+) and D.STATUS_CODE(+) = '01')

union
select A.IR_TASK_ID,
A.CP_NO,
A.APP_NO,
A.ASSET_ID,
A.ASSET_TYPE_CODE,
A.OBJECT_NO,
B.CONS_NAME,
B.MR_SECT_NO,
D.TERMINAL_ADDR,
D.COLL_MODE,
C.NAME,
A.CHG_MODE,
A.TERMINAL_TYPE_CODE,
A.CP_TYPE_CODE,
A.STATUS,
D.TERMINAL_ID,
null as HANDLE_TIME
from epma.R_TMNL_IR_TASK A,
epma.R_CP C,
epma.R_TMNL_RUN D,
epma.R_CP_CONS_RELA M,
epma.C_CONS B
where A.CP_NO = M.CP_NO
and M.CONS_ID = B.CONS_ID
and A.OBJECT_NO = B.CONS_NO
and A.CP_NO = C.CP_NO

and (A.CP_NO = D.CP_NO(+) and D.STATUS_CODE(+) = '01')

[304的的哥]

--查询条件只涉及到a,e,c,d,b,g,
select distinct a.TERMINAL_ID,
                a.TERMINAL_ADDR,
                a.STATUS_CODE,
                a.COLL_MODE,
                a.TERMINAL_TYPE_CODE,
                a.ID,
                a.CP_NO,
                e.NAME as terminal_name,
                c.cons_no,
                c.cons_name
from epma.R_TMNL_RUN a,
     epma.g_line b,
     epma.c_cons c,
     epma.r_cp_cons_rela d,
     epma.r_cp e,
     epma.g_subs f,--1
     epma.o_org g,
     epma.g_subs_line_rela h--2
where a.CP_NO = e.CP_NO(+)
  and a.cp_no = d.cp_no(+)
  and d.cons_id = c.cons_id(+)
  --下面这三个连接条件你在查询中都没有用到，你真觉得他们有用吗？
  --如果没用，那么可以去掉这几个表:h,f
--------------------------------
--and d.cons_id = b.cons_id(+)--
--and b.line_id = h.line_id(+)--
--and h.subs_id = f.subs_id(+)--
  ------------------------------
  and c.org_no = g.org_no(+)
  and g.org_no like '32401%%'
  and a.TERMINAL_TYPE_CODE = '05'-- 低压集中器
order by a.TERMINAL_ID asc

[tangren]

从优化角度看，只给出SQL语句本身提供的信息不多，
还要看数据量、数据分布、列上索引建立情况、
ORACLE使用的优化模式、执行计划等。

所以，不要寄希望给出一个SQL就能得到一个很好的优化结果。