oracle SQL优化到10人同时查询返回1s

Question

SELECT l.id,
l.pro_name,
l.pro_number,
s.qutt_financing_value,
s.qutt_project_deadline,
s.qutt_ready_rate,
s.qutt_cal_way,
NVL(
(SELECT c.CUST_SIMPLENAME FROM T_CUST_COMPANY c WHERE c.id=l.assure_cust_id
),'-') AS assure_name,
l.pro_type,
(SELECT NVL(SUM(b.CLAIM_PRINCIPAL),0.00)
FROM T_LOAN_BID b
WHERE b.PRO_ID =l.ID
AND b.BID_STATUS>=1
AND b.BID_TYPE =1
),
l.PRO_BILLSTATUS,
l.deploy_date,
l.END_DATE,
s.QUTT_INTEREST_STARTDATE,
s.QUTT_RENT_PAYDATE,
s.QUTT_MIN_INVEST,
s.QUTT_MAX_INVEST,
c.USER_ID,
s.QUTT_NEWCUSTOMER_FLAG,
s.QUTT_PROJECT_DEADLINE_FLAG,
NVL(s.QUTT_RECOMMENDREASON,' '),
NVL(
(SELECT c.SOURCE_SIMPLE_NAME
FROM T_CUST_REPAY_SOURCE c
WHERE c.id=l.REPMENT_USER
),'-'),
(SELECT NVL(SUM(p.REPAY_INTEREST),0)
FROM T_LOAN_REPAYMENT p
WHERE p.PRO_ID=l.ID
),
NVL(l.PRO_STANDARD_TYPE,0),
l.PRO_STANDARD_TIME,
l.PRO_STATUS,
NVL(l.PACT_TYPE,'phContract1'),
s.QUTT_INTEREST_ENDDATE,
NVL(e.REVIEW_LEVEL,'-'),
NVL(l.IS_BID_PSWD,0) ,
d.FLAG,
NVL(l.IS_STAR_INVEST,0)
FROM T_LOAN_LOAN_INFO l,
T_LOAN_SCHEME_INFO s,
T_LOAN_CREDIT_LEVEL e,
T_CUST_COMPANY c,
T_DATA_DICTIONARY d
WHERE l.id = s.pro_id
AND l.id =e.pro_id
AND c.id =l.customer_id
AND l.PRO_BILLSTATUS >2
AND l.PRO_BILLSTATUS!=11
AND d.TYPE ='项目类型'
AND d.CODE =l.PRO_TYPE
AND l.PRO_TYPE ='1'
ORDER BY l.PRO_BILLSTATUS,
l.DEPLOY_DATE DESC

这条SQL子连接太多,性能太低了,有没有大牛帮忙优化一下这条语句,优化到这条SQL优化到十个人同时查询还能保证1秒内返回.有报酬.如果有方案的,请加QQ:834254081

Answer 1

1.首先所有都要大写，省去oracle为你转大写步骤
2.不要使用> 换成 >=
3.看下表结构，这里面用到的字段是否有加索引，如果加了索引了，看看那些函数索引有没有加
4.这边部分可以用decode代替NVL