大家来谈谈哪些情况可以导致索引失效 1.函数2.表达式3.部分隐式转换4.like和substr5.查询所有null值6.否定形式 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 好像是 我不太懂看执行计划,上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 F.DISTRICT_ID = G.DISTRICT_ID AND G.STREET_ID = H.STREET_ID --AND A.CONTRACT_CODE = 'd' 或者 AND upper(A.CONTRACT_CODE) = 'd' 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)A.CONTRACT_CODE = 'd': AND upper(A.CONTRACT_CODE) = 'd':]该字段是索引 上面的条件AND A.PARENT_CONTRACT_ID IS NULL不能省略掉 好像是 我不太懂看执行计划,上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 F.DISTRICT_ID = G.DISTRICT_ID AND G.STREET_ID = H.STREET_ID --AND A.CONTRACT_CODE = 'd' 或者 AND upper(A.CONTRACT_CODE) = 'd' 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)A.CONTRACT_CODE = 'd': AND upper(A.CONTRACT_CODE) = 'd':]该字段是索引 上面的条件AND A.PARENT_CONTRACT_ID IS NULL不能省略掉图错了 AND upper(A.CONTRACT_CODE) = 'd'应该是 上面一个图发错了加上AND upper(A.CONTRACT_CODE) = 'd'是这样的 Oracel 二次开发问题 求助个触发器的小问题 Oracle 10g 掉电后,现在能启动了,但数据表和用户都不能操作。 各位帮忙看看,这个SQL应该怎么写 vs2005 找不到occi.h 控制查询返回行数 将数据库信息加千分号的sql语句 请教一个关于oracle数据库同步(复制)的问题!???急!!!! 请问oracle for linux 免费版,与收费版有什么区别 CB+BDE+Oracle 如何按日期查询 在线等待!!! 怎样生成这样一张表? 这个语句怎么用关联的方法写出来。还可以怎么改能实现和这个语句一样的查询结果。
看执行计划,上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 F.DISTRICT_ID = G.DISTRICT_ID
AND G.STREET_ID = H.STREET_ID
--AND A.CONTRACT_CODE = 'd' 或者 AND upper(A.CONTRACT_CODE) = 'd'
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)
A.CONTRACT_CODE = 'd':
AND upper(A.CONTRACT_CODE) = 'd':]
该字段是索引 上面的条件AND A.PARENT_CONTRACT_ID IS NULL不能省略掉
看执行计划,上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 F.DISTRICT_ID = G.DISTRICT_ID
AND G.STREET_ID = H.STREET_ID
--AND A.CONTRACT_CODE = 'd' 或者 AND upper(A.CONTRACT_CODE) = 'd'
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)
A.CONTRACT_CODE = 'd':
AND upper(A.CONTRACT_CODE) = 'd':]
该字段是索引 上面的条件AND A.PARENT_CONTRACT_ID IS NULL不能省略掉
图错了 AND upper(A.CONTRACT_CODE) = 'd'应该是
加上AND upper(A.CONTRACT_CODE) = 'd'是这样的