有4个表a,b,c,d。其中a的key是APP_ID, FREIGHT_ID,另外还有属性AUDIT_STATUS,FREIGHT,CURRENCY,REMARK,AUDIT_USER,AUDIT_TIME,FREIGHT_TYPE,b的key是APP_ID,另外还有属性POR,SERVICE,VESSEL,VOYAGE,c的key是APP_ID, BL_NO, PURPOSE,另外还有属性BL_NO,,d的key是APP_ID, STATUS, ROUTE_SEQ,另外还有属性LAST_UPDATE_DATE,STATUS,现在要查询a表的记录,并根据APP_ID关联其他的表b,c,d,从而查出AUDIT_STATUS,FREIGHT,CURRENCY,REMARK,AUDIT_USER,AUDIT_TIME,FREIGHT_TYPE,POR,SERVICE,VESSEL,VOYAGE,BL_NO,LAST_UPDATE_DATE,STATUS这些属性,外加条件:SERVICE = V_SERVICE,VESSEL = V_VESSEL,VOYAGE = V_VOYAGE,V_DATE_FROM < LAST_UPDATE_DATE < V_DATE_TO,STATUS = 'F1',FREIGHT_TYPE需等于(COD, LPS, MAF, OWF)中的一个,a,b,c,d都有10万的记录数,我的写法如下:SELECT DISTINCT F.APP_ID AS APP_ID,
F.FREIGHT_ID AS FREIGHT_ID,
F.AUDIT_STATUS AS AUDIT_STATUS,
D.BL_NO AS BL_NO,
F.FREIGHT AS FREIGHT,
F.CURRENCY AS CURRENCY,
F.REMARK AS REMARK,
F.AUDIT_USER AS AUDIT_USER,
F.AUDIT_TIME AS AUDIT_TIME,
H.POR AS POR,
H.POL AS POL,
H.POD AS POD,
H.DEL AS DEL,
H.DEST AS DEST,
F.POP AS POP,
F.PAYER AS PAYER,
F.FREIGHT_TYPE AS FREIGHT_TYPE,
F.LOCK_IND AS LOCK_IND,
F.LOCK_USER AS LOCK_USER,
F.LOCK_DATE AS LOCK_DATE
FROM CSC.BM_APP_FREIGHT F
LEFT JOIN CSC.BM_APP_HEADER H ON F.APP_ID = H.APP_ID
LEFT JOIN CSC.BM_APP_DETAIL D ON F.APP_ID = D.APP_ID
LEFT JOIN CSC.BM_APP_ROUTING R ON F.APP_ID = R.APP_ID
WHERE (V_SERVICE IS NULL OR H.SERVICE = V_SERVICE)
AND (V_VESSEL IS NULL OR H.VESSEL = V_VESSEL)
AND (V_VOYAGE IS NULL OR H.VOYAGE = V_VOYAGE)
AND (F.AUDIT_STATUS IS NULL OR F.AUDIT_STATUS IN ('0', '1'))
AND (V_DATE_FROM IS NULL OR
R.LAST_UPDATE_DATE >= TO_DATE(V_DATE_FROM, 'YYYY-MM-DD'))
AND (V_DATE_TO IS NULL OR
R.LAST_UPDATE_DATE < TO_DATE(V_DATE_TO, 'YYYY-MM-DD') + 1)
AND (R.STATUS = 'F1')
AND (F.FREIGHT_TYPE IN ('COD', 'LPS', 'MAF', 'OWF'));
因为是新手,不知道还需要说明什么,先上传这些,有需要的再说。
主要是帮我看看有没有查询速度快一点的写法,或是什么地方需要改进,现在速度很慢。
F.FREIGHT_ID AS FREIGHT_ID,
F.AUDIT_STATUS AS AUDIT_STATUS,
D.BL_NO AS BL_NO,
F.FREIGHT AS FREIGHT,
F.CURRENCY AS CURRENCY,
F.REMARK AS REMARK,
F.AUDIT_USER AS AUDIT_USER,
F.AUDIT_TIME AS AUDIT_TIME,
H.POR AS POR,
H.POL AS POL,
H.POD AS POD,
H.DEL AS DEL,
H.DEST AS DEST,
F.POP AS POP,
F.PAYER AS PAYER,
F.FREIGHT_TYPE AS FREIGHT_TYPE,
F.LOCK_IND AS LOCK_IND,
F.LOCK_USER AS LOCK_USER,
F.LOCK_DATE AS LOCK_DATE
FROM CSC.BM_APP_FREIGHT F
LEFT JOIN CSC.BM_APP_HEADER H ON F.APP_ID = H.APP_ID
LEFT JOIN CSC.BM_APP_DETAIL D ON F.APP_ID = D.APP_ID
LEFT JOIN CSC.BM_APP_ROUTING R ON F.APP_ID = R.APP_ID
WHERE (V_SERVICE IS NULL OR H.SERVICE = V_SERVICE)
AND (V_VESSEL IS NULL OR H.VESSEL = V_VESSEL)
AND (V_VOYAGE IS NULL OR H.VOYAGE = V_VOYAGE)
AND (F.AUDIT_STATUS IS NULL OR F.AUDIT_STATUS IN ('0', '1'))
AND (V_DATE_FROM IS NULL OR
R.LAST_UPDATE_DATE >= TO_DATE(V_DATE_FROM, 'YYYY-MM-DD'))
AND (V_DATE_TO IS NULL OR
R.LAST_UPDATE_DATE < TO_DATE(V_DATE_TO, 'YYYY-MM-DD') + 1)
AND (R.STATUS = 'F1')
AND (F.FREIGHT_TYPE IN ('COD', 'LPS', 'MAF', 'OWF'));
因为是新手,不知道还需要说明什么,先上传这些,有需要的再说。
主要是帮我看看有没有查询速度快一点的写法,或是什么地方需要改进,现在速度很慢。
不好意思,忘了改别名了。我重新传下:SELECT DISTINCT a.APP_ID AS APP_ID,
a.FREIGHT_ID AS FREIGHT_ID,
a.AUDIT_STATUS AS AUDIT_STATUS,
c.BL_NO AS BL_NO,
a.FREIGHT AS FREIGHT,
a.CURRENCY AS CURRENCY,
a.REMARK AS REMARK,
a.AUDIT_USER AS AUDIT_USER,
a.AUDIT_TIME AS AUDIT_TIME,
b.POR AS POR,
a.FREIGHT_TYPE AS FREIGHT_TYPE
FROM CSC.BM_APP_FREIGHT a
LEFT JOIN CSC.BM_APP_HEADER b ON a.APP_ID = b.APP_ID
LEFT JOIN CSC.BM_APP_DETAIL c ON a.APP_ID = c.APP_ID
LEFT JOIN CSC.BM_APP_ROUTING d ON a.APP_ID = d.APP_ID
WHERE (V_SERVICE IS NULL OR b.SERVICE = V_SERVICE)
AND (V_VESSEL IS NULL OR b.VESSEL = V_VESSEL)
AND (V_VOYAGE IS NULL OR b.VOYAGE = V_VOYAGE)
AND (V_DATE_FROM IS NULL OR
d.LAST_UPDATE_DATE >= TO_DATE(V_DATE_FROM, 'YYYY-MM-DD'))
AND (V_DATE_TO IS NULL OR
d.LAST_UPDATE_DATE < TO_DATE(V_DATE_TO, 'YYYY-MM-DD') + 1)
AND (d.STATUS = 'F1')
AND (a.FREIGHT_TYPE IN ('COD', 'LPS', 'MAF', 'OWF'));
其中V_SERVICE,V_VESSEL,V_VOYAGE,V_DATE_FROM,V_DATE_TO都是参数。
表BM_APP_ROUTING字段LAST_UPDATE_DATE
表BM_APP_FREIGHT字段FREIGHT_TYPE
上有索引吗,如果有索引选择如何?
我记得当join和where并存时,先按join里的条件连接,对其结果按where后的条件过滤
你试试把where后的条件全部改到join on后面
SELECT DISTINCT F.APP_ID AS APP_ID,
F.FREIGHT_ID AS FREIGHT_ID,
F.AUDIT_STATUS AS AUDIT_STATUS,
D.BL_NO AS BL_NO,
F.FREIGHT AS FREIGHT,
F.CURRENCY AS CURRENCY,
F.REMARK AS REMARK,
F.AUDIT_USER AS AUDIT_USER,
F.AUDIT_TIME AS AUDIT_TIME,
H.POR AS POR,
H.POL AS POL,
H.POD AS POD,
H.DEL AS DEL,
H.DEST AS DEST,
F.POP AS POP,
F.PAYER AS PAYER,
F.FREIGHT_TYPE AS FREIGHT_TYPE,
F.LOCK_IND AS LOCK_IND,
F.LOCK_USER AS LOCK_USER,
F.LOCK_DATE AS LOCK_DATE
FROM CSC.BM_APP_FREIGHT F
,CSC.BM_APP_HEADER H
,CSC.BM_APP_DETAIL D
,CSC.BM_APP_ROUTING R
WHERE F.APP_ID = H.APP_ID(+)
AND F.APP_ID = D.APP_ID(+)
AND F.APP_ID = R.APP_ID(+)
AND (V_SERVICE IS NULL OR H.SERVICE = V_SERVICE)
AND (V_VESSEL IS NULL OR H.VESSEL = V_VESSEL)
AND (V_VOYAGE IS NULL OR H.VOYAGE = V_VOYAGE)
AND (F.AUDIT_STATUS IS NULL OR F.AUDIT_STATUS IN ('0', '1'))
AND (V_DATE_FROM IS NULL OR
R.LAST_UPDATE_DATE >= TO_DATE(V_DATE_FROM, 'YYYY-MM-DD'))
AND (V_DATE_TO IS NULL OR
R.LAST_UPDATE_DATE < TO_DATE(V_DATE_TO, 'YYYY-MM-DD') + 1)
AND (R.STATUS = 'F1')
AND (F.FREIGHT_TYPE IN ('COD', 'LPS', 'MAF', 'OWF'));
不明白你用左连接是否是出于业务的需要,如果不是的话,全部改成内连接,即将上面语句的(+)去掉,速度会有提升。
在你的原句中,对R表的左连接没有意义,如果那几个参数非全为空的话,另外2表的外连接也没有意义
先谢谢你,我根据你这样写了,速度也差不多,后来我把DISTINCT去掉试了下,会快上一点,但是我又需要去重,不知道有没有其他的去重方法能快一点的。还有我这项目要求主要是提取CSC.BM_APP_FREIGHT F里面的记录,如果不设条件,是需要每一笔都要查的,不过它界面也会根据V_SERVICE,V_VESSEL,V_VOYAGE,V_DATE_FROM,V_DATE_TO这些参数来设置条件。
你用左连接的话,如果不设条件,会将符合F.FREIGHT_TYPE IN ('COD', 'LPS', 'MAF', 'OWF')的所有F表中的记录都查出来,即使不满足F表和其他3表的连接条件
不过这里首先你要建好相应的索引
4张表的APP_ID字段分别建1个索引
H.SERVICE,H.VESSEL,H.VOYAGE建个联合索引
F.AUDIT_STATUS;
R.LAST_UPDATE_DATE
R.STATUS
F.FREIGHT_TYPE
建好索引才看看效果