SQL 优化
SELECT a.opp_province,
a.area_type,
a.opp_local,
a.cust_manager_name,
(CASE
WHEN length(a.cust_man_dept) != lengthb(a.cust_man_dept) THEN
a.cust_man_dept
ELSE
(SELECT d.dept_name
FROM th_int_dept d
WHERE d.row_id = a.cust_man_dept)
END) AS cust_man_dept_name,
a.cust_name,
a.cust_id,
a.cust_indu,
(CASE
WHEN length(a.opp_id) = 9 OR SUBSTR(a.opp_id, 1, 2) = 'SX' THEN
(SELECT t.contact_name
FROM th_contact t
LEFT JOIN th_customer cust ON (cust.row_id = t.work_unit_id)
LEFT JOIN th_party pt ON (pt.row_id = cust.party_id)
WHERE pt.party_code = a.cust_id
AND ROWNUM = 1)
ELSE
(SELECT t.cust_contact
FROM tm_province_cust_temp t
WHERE t.cust_id = a.cust_id
AND t.opp_id = a.opp_id)
END) AS cust_link_man,
(CASE
WHEN length(a.opp_id) = 9 OR SUBSTR(a.opp_id, 1, 2) = 'SX' THEN
(SELECT t.office_phone
FROM th_contact t
LEFT JOIN th_customer cust ON (cust.row_id = t.work_unit_id)
LEFT JOIN th_party pt ON (pt.row_id = cust.party_id)
WHERE pt.party_code = a.cust_id
AND ROWNUM = 1)
ELSE
(SELECT t.cust_tel
FROM tm_province_cust_temp t
WHERE t.cust_id = a.cust_id
AND t.opp_id = a.opp_id)
END) AS cust_link_phone,
(CASE
WHEN length(a.opp_id) = 9 OR SUBSTR(a.opp_id, 1, 2) = 'SX' THEN
(SELECT t.email_addr
FROM th_contact t
LEFT JOIN th_customer cust ON (cust.row_id = t.work_unit_id)
LEFT JOIN th_party pt ON (pt.row_id = cust.party_id)
WHERE pt.party_code = a.cust_id
AND ROWNUM = 1)
ELSE
(SELECT t.cust_email
FROM tm_province_cust_temp t
WHERE t.cust_id = a.cust_id
AND t.opp_id = a.opp_id)
END) AS cust_link_email,
(CASE
WHEN length(a.opp_id) = 9 OR SUBSTR(a.opp_id, 1, 2) = 'SX' THEN
''
ELSE
(SELECT t.cust_property
FROM tm_province_cust_temp t
WHERE t.cust_id = a.cust_id
AND t.opp_id = a.opp_id)
END) AS cust_attr,
a.opp_id,
a.opp_name,
(SELECT p.display_value
FROM pb_list_of_value p
WHERE p.TYPE = 'OPP_DEGREE'
AND p.language_flag = 'CHS'
AND p.stand_code = a.opp_imp) AS opp_imp,
(SELECT p.display_value
FROM pb_list_of_value p
WHERE p.TYPE = 'OPP_PHASE'
AND p.language_flag = 'CHS'
AND p.stand_code = a.opp_phase) AS opp_phase,
a.opp_forsign_time,
(SELECT p.display_value
FROM pb_list_of_value p
WHERE p.TYPE = 'OPP_PROJ_TYPE'
AND p.language_flag = 'CHS'
AND p.stand_code = a.project_type) AS project_type,
(SELECT p.display_value
FROM pb_list_of_value p
WHERE p.TYPE = 'OPP_STATUS'
AND p.language_flag = 'CHS'
AND p.stand_code = a.opp_state) AS opp_state,
a.opp_sign_time,
a.opp_sign_province,
a.opp_sign_once,
a.opp_sign_month,
a.opp_once,
a.opp_month,
a.contract_term,
a.contract_sum,
a.opp_note,
a.opp_desc,
a.cust_man_contact cust_manager_link,
(SELECT p.display_value
FROM pb_list_of_value p
WHERE p.TYPE = 'OPP_SCALE'
AND p.language_flag = 'CHS'
AND p.stand_code = a.busi_area) AS busi_area,
a.opp_competitor,
a.opp_busi_name,
a.create_by_name,
a.create_date,
a.contract_id,
a.contract_date,
a.opp_contract_term,
a.contract_month,
a.contract_total,
a.contract_once,
a.contract_porvince,
a.national_opp,
a.b_row_id,
a.busi_id,
a.busi_name,
a.once_fee,
a.month_fee,
a.user_num,
a.display_value_con_type,
a.agrmt_sum_fee,
a.annual_inc_amt,
a.ict_amt,
a.device_cost,
a.display_value_client_type,
a.sal_supp_desc,
a.contract_year_increase,
a.contract_total_increase,
a.mana_dept_id,
a.mana_dept_name,
a.sec_cust_id,
a.sec_cust_name,
a.market_team_name,
a.COOP_AGENTS_NAME,
a.cust_marked
FROM (SELECT (case
when tml.tml_nbr != tmlb.tml_nbr then
tmlb.tml_name_text || tml.tml_name_text
else
tml.tml_name_text
end) AS opp_province,
a.area_type,
a.opp_local,
a.cust_manager,
(CASE
WHEN LENGTH(a.cust_manager) != LENGTHB(a.cust_manager) THEN
a.cust_manager
ELSE
(SELECT u.user_name
FROM th_user u
WHERE u.row_id = a.cust_manager)
END) AS cust_manager_name,
a.cust_man_dept,
a.cust_name,
a.cust_id,
a.cust_indu,
a.opp_id,
a.opp_name,
a.opp_imp,
a.opp_phase,
a.opp_forsign_time,
a.project_type,
a.opp_state,
a.opp_sign_time,
a.opp_sign_province,
a.opp_sign_once,
a.opp_sign_month,
a.opp_once,
a.opp_month,
a.contract_term,
a.contract_sum,
a.opp_note,
a.opp_desc,
a.cust_man_contact,
a.busi_area,
a.opp_competitor,
a.opp_busi_name,
(CASE
WHEN LENGTH(a.create_by) != LENGTHB(a.create_by) THEN
a.create_by
ELSE
(SELECT DISTINCT u.user_name
FROM th_user u
WHERE u.row_id = a.create_by)
END) create_by_name,
a.create_date,
a.last_modify_date,
a.opp_discover_place,
d.cust_agrmt_code AS contract_id,
d.assigned_date AS contract_date,
e.eff_months AS opp_contract_term,
e.month_rent_amt AS contract_month,
e.total_amount AS contract_total,
e.one_time_fee AS contract_once,
e.assign_bu AS contract_porvince,
a.national_opp,
f.row_id AS b_row_id,
f.busi_id,
f.busi_name,
f.once_fee,
f.month_fee,
f.user_num,
pb.display_value as display_value_con_type,
e.AGRMT_SUM_FEE as agrmt_sum_fee,
e.annual_inc_amt,
e.ICT_AMT as ict_amt,
e.DEVICE_COST as device_cost,
pb1.display_value as display_value_client_type,
a.SAL_SUPP_DESC as sal_supp_desc,
a.contract_year_increase,
a.contract_total_increase,
a.mana_dept_id,
(select pl.display_value
from pb_list_of_value pl
where type = 'MANA_DEPT'
and pl.stand_code = a.mana_dept_id) mana_dept_name,
a.sec_cust_id,
(select org.org_name
from th_party ty
join th_orgnization org on ty.row_id = org.party_id
where ty.party_code = a.sec_cust_id) as sec_cust_name,
tmt.market_team_full_name as market_team_name,
a.COOP_AGENTS_NAME,
(select pl.display_value
from pb_list_of_value pl
where type = 'cust_marked'
and pl.stand_code = a.cust_marked) cust_marked
FROM tm_sal_opp_sepc a
LEFT JOIN tm_opp_contract c ON (c.opp_id = a.opp_id)
LEFT JOIN th_cust_agrmt d ON (d.cust_agrmt_code = c.contract_id)
LEFT JOIN th_cust_agrmt_x e ON (e.agrmt_id = d.row_id)
left join pb_list_of_value pb on e.assign_type = pb.stand_code
left join th_party ty on a.cust_id = ty.party_code
left join th_customer v on v.party_id = ty.row_id
left join th_cust_attr att on v.cust_code = att.cust_id
left join pb_list_of_value pb1 on att.attr_value = pb1.stand_code
LEFT JOIN ta_tml tml ON (tml.tml_nbr = a.opp_province)
LEFT JOIN ta_tml tmlb ON (tmlb.tml_nbr =
substr(a.opp_province, 0, 6))
LEFT JOIN tm_opp_busi f ON (f.opp_id = a.opp_id)
left join tc_marketing_team tmt on a.market_team_id = tmt.row_id
AND (to_char(a.create_date, 'yyyy-mm-dd') >= '1970-01-01' AND
to_char(a.create_date, 'yyyy-mm-dd') <= '2099-12-31')
AND (a.create_by = '2008012713388212' OR
a.cust_manager = '2008012713388212')
) a
WHERE 1 = 1
ORDER BY a.last_modify_date DESC
------解决方案--------------------
关联表太多。且case when 还要关联select 语句。
效率应该是很低的。
to_char(a.create_date)尽量不要对列加函数。
[解决办法]
你给出表和数据，说你想要什么结果就行
谁要你写的sql啊！
[解决办法]
如果说就对你写的sql来说，可优化的地方也是有的
1,sql中使用函数的地方，比如说to_date()等，尽量用的=号右边的常量，不要用在字段上。
2，oracle的条件解析是从后向前的，所以把能过滤掉大数据的条件放到最后去。
3，创建索引，比如的你排序字段等可以创建索引能增加很快的速度。
4，from的表很多，表的顺序也是有关系的，oracle的表应该是从右向左寻找的，作为基础表的数据越少越好。
5，哇！好多case啊，这个看下可不可以替换成decode，decode会减少处理时间。
6，子查询？居然有好几个子查询，是否真的有必要用呢？你的sql看的我眼疼啊，不分析了。
[解决办法]
蛋疼的嵌套子查询，去看看SQL优化规则
[解决办法]
我求求你写存储过程吧。看都看到蛋疼