语句优化

[huiruansoft]

写的一个导出查询
CREATE OR REPLACE VIEW V_CREATE_KA_SELECTALL_EXPORT AS
select
a.BASE_TYPE,
a.AUTO_ID,
a.TRADE_CODE_B,
a.TRADE_NAME,
a.COP_APP_NO,
a.CAR_NO,
a.STATUS,
a.PORT_STATUS,
wm_concat(';'|| '' || b.gatejob_no || '</br>' )as BILL_NO,
a.SENDMARK,
a.TSTYPE,
a.YWTYPENEW,
a.GATEJOB_NO_T,
a.STATUS_NAME,
a.PORT_STATUS_NAME,
a.CREATEDATE,
a.GDATETIME,
a.YWTYPE,
a.TYPE,
a.YWTYPENAME,
a.CAR_NUM,
a.LAST_CAR,
a.LASTNAME,
a.FLAG1,
a.FLAG2,
a.TOTAL_WEIGHT,
a.TOTAL_AMOUNT,
a.ICCODE,
a.F_CONTA_NO,
a.F_CONTA_WT,
a.A_CONTA_NO,
a.A_CONTA_WT,
a.CAR_J_WT,
a.FROMKA,
a.TOKA,
a.FROMKA_NAME,
a.TOKA_NAME,
a.F_CONTA_TYPE,
a.A_CONTA_TYPE,
a.CAR_TYPE,
a.DET_TYPE,
a.REMARK2,
a.TSTYPENAMES
from V_CREATE_KA_SELECTALL a
left join (SELECT Base_ID, GATEJOB_NO, GATEJOB_NO_REL
FROM CREATE_KA_JOB
UNION ALL
SELECT Base_ID, GATEJOB_NO, GATEJOB_NO_REL
FROM PORT_CREATE_KA_JOB) b
on a.AUTO_ID = b.base_id
group by a.BASE_TYPE,
a.AUTO_ID,
a.TRADE_CODE_B,
a.TRADE_NAME,
a.COP_APP_NO,
a.CAR_NO,
a.STATUS,
a.PORT_STATUS,
a.SENDMARK,
a.TSTYPE,
a.YWTYPENEW,
a.GATEJOB_NO_T,
a.STATUS_NAME,
a.PORT_STATUS_NAME,
a.CREATEDATE,
a.GDATETIME,
a.YWTYPE,
a.TYPE,
a.YWTYPENAME,
a.CAR_NUM,
a.LAST_CAR,
a.LASTNAME,
a.FLAG1,
a.FLAG2,
a.TOTAL_WEIGHT,
a.TOTAL_AMOUNT,
a.ICCODE,
a.F_CONTA_NO,
a.F_CONTA_WT,
a.A_CONTA_NO,
a.A_CONTA_WT,
a.CAR_J_WT,
a.FROMKA,
a.TOKA,
a.FROMKA_NAME,
a.TOKA_NAME,
a.F_CONTA_TYPE,
a.A_CONTA_TYPE,
a.CAR_TYPE,
a.DET_TYPE,
a.REMARK2,
a.TSTYPENAMES;

3-4万条数据还可以 查到8万条的时候 就很慢 占用内存很大了

[sych888]

a.AUTO_ID = b.base_id 有索引没？ 要用那么多多字段排序，效率肯定会降下来，看看能否改写一下语句

• 打赏
• 举报

回复 赞