2 tang huan 11 tang_huan_11 于 2016.05.09 20:37 提问

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

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

2个回答

CSDNXIAON
CSDNXIAON   2016.05.09 20:42

Oracle SQL 查询优化.Part1
----------------------同志你好,我是CSDN问答机器人小N,奉组织之命为你提供参考答案,编程尚未成功,同志仍需努力!

sunkaizhou504
sunkaizhou504   2016.05.09 20:46

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

u010793197
u010793197 decode 代替NVL 是什么意思?
一年多之前 回复
Csdn user default icon
上传中...
上传图片
插入图片
准确详细的回答,更有利于被提问者采纳,从而获得C币。复制、灌水、广告等回答会被删除,是时候展现真正的技术了!