我现在有一条sql查询速度太慢，求一个解决方法
原sql
SELECT A.CONTRACT_ID,
A.CONTRACT_CODE,
A.CUST_ID,
A.PROJECT_NAME,
A.EFF_DATE,
A.EXP_DATE,
A.RENEWAL_FLAG,
A.SIGNATURE_DATE,
A.DEMAND_COMPLETED_DATE,
A.SALE_STAFF_ID,
A.SALE_PROPORTION,
A.SALE2_STAFF_ID,
A.SALE2_PROPORTION,
A.SALE_TYPE,
A.SALE_CODE,
A.STATE,
A.STATE_DATE,
A.CREATED_DATE,
A.UPDATE_DATE,
A.PARTY_TYPE,
A.PARTY_CODE,
A.COMMENTS,
A.LINKMAN_ID,
A.CHECK_PARTY_TYPE,
A.CHECK_PARTY_CODE,
A.CONTRACT_ORG_ID,
A.CONTRACT_TERM,
A.CANCEL_REASON,
A.CHECK_FAILURE_REASON,
FOR_COMM_QUERY.FORMAT_PRICE_VALUE_BY_CVBS(A.CHARGE, 0) CHARGE,
B.SUBS_ID,
B.SUBS_NAME,
C.SALE_TYPE_NAME,
E.ADDRESS,
E.ADDRESS_ID,
F.DISTRICT_NAME,
G.STREET_NAME,
H.ROAD_NAME
FROM CVBS_CONTRACT A,
CVBS_SUBS B,
CVBS_SALE_TYPE C,
CVBS_SUBS_ADDRESS D,
CVBS_ADDRESS E,
CVBS_DISTRICT F,
CVBS_STREET_INFO G,
CVBS_ROAD_INFO H
WHERE 1 = 1
AND A.CONTRACT_ID = B.CONTRACT_ID
AND A.SALE_TYPE = C.SALE_TYPE(+)
AND A.PARENT_CONTRACT_ID IS NULL
AND A.STATE IN ('E', 'G')
AND B.SUBS_ID = D.SUBS_ID
AND D.ADDRESS_ID = E.ADDRESS_ID
AND D.STATE = 'A'
AND D.ADDRESS_TYPE IN ('A', 'C')
AND E.ROAD_ID = H.ROAD_ID(+)
AND H.STREET_ID = G.STREET_ID(+)
AND G.DISTRICT_ID = F.DISTRICT_ID(+)
--AND A.CONTRACT_ORG_ID = '2022'
--AND UPPER(A.CONTRACT_CODE) LIKE '%'||UPPER('-')||'%'
--AND UPPER(A.PROJECT_NAME) LIKE '%'||UPPER('中')||'%'
--AND A.EXP_DATE >= TO_DATE('2004-02-14 14:13:05', 'yyyy-mm-dd hh24:mi:ss')
--AND A.EXP_DATE <= TO_DATE('2014-02-14 14:13:09', 'yyyy-mm-dd hh24:mi:ss')
--AND UPPER(B.SUBS_NAME) LIKE '%'||UPPER('中')||'%' --快
AND EXISTS
(SELECT R.SUBS_ID
FROM (SELECT DISTINCT T.SUBS_ID
FROM IB_SUB_COMPANY_CONTRACT_SUBS T
WHERE 1 = 1
AND T.STATE = 'A'
) R
WHERE B.SUBS_ID = R.SUBS_ID);
执行完是1.3秒左右 可以查到5条记录
注释掉的部分是界面可选参数
如可带上可选参数AND UPPER(B.SUBS_NAME) LIKE '%'||UPPER('中')||'%'的话执行完的时间是0.4秒
带上其他5个任意查询条件执行完的时间有好几十秒
请问怎么优化？
------解决方案--------------------
前排帮顶 友情混分 各大版主手下留情
------解决方案--------------------
前排帮顶 友情混分 各大版主手下留情
------解决方案--------------------
其实我也不太懂优化，我觉得你是不是可以通过子查询将你需要的数据从每个表中筛选出来后再进行连接，直接连接感觉太混乱了。我也不知道行不行，你可以试试
------解决方案--------------------