对这个sql外面怎样优化呢?
SELECT S_JKSJL.*
FROM SJXMZZ,D_SWDJB INNER JOIN S_JKSJL ON D_SWDJB.ID_WJDM = S_JKSJL.ID_WJDM
WHERE (upper(trim(SJXMZZ.AUDIT_USER_ID))='PANG' AND trim(D_SWDJB.sjxx_sel_no)=SJXMZZ.audit_proj_index AND trim(S_JKSJL.sjxx_sel_no)=SJXMZZ.audit_proj_index ) AND ((S_JKSJL.ID_SM<>'7777' AND S_JKSJL.ID_SM<>'8888' AND S_JKSJL.ID_SM<>'9999' AND S_JKSJL.ID_SZ='51' AND SUBSTR(ID_SM,1,2)='01' AND TO_CHAR(DATE_JKRQ,'YYYY')>TO_CHAR((SJXMZZ.AUDIT_YEAR)-1) AND TO_CHAR(DATE_SSQZR,'YYYY')=TO_CHAR(SJXMZZ.AUDIT_YEAR) AND D_SWDJB.DATE_GSDJR<TO_DATE('2002/1/1','YYYY/MM/DD') AND D_SWDJB.ID_ZGFS='3') OR
(S_JKSJL.ID_SM<>'7777' AND S_JKSJL.ID_SM<>'8888' AND S_JKSJL.ID_SM<>'9999' AND S_JKSJL.ID_SZ='02' AND TO_CHAR(DATE_JKRQ,'YYYY')>TO_CHAR((SJXMZZ.AUDIT_YEAR)-1) AND TO_CHAR(DATE_SSQZR,'YYYY')=TO_CHAR(SJXMZZ.AUDIT_YEAR) AND D_SWDJB.DATE_GSDJR<TO_DATE('2002/1/1','YYYY/MM/DD') AND D_SWDJB.ID_ZGFS='3'))
SELECT S_JKSJL.*
FROM SJXMZZ,D_SWDJB INNER JOIN S_JKSJL ON D_SWDJB.ID_WJDM = S_JKSJL.ID_WJDM
WHERE (upper(trim(SJXMZZ.AUDIT_USER_ID))='PANG' AND trim(D_SWDJB.sjxx_sel_no)=SJXMZZ.audit_proj_index AND trim(S_JKSJL.sjxx_sel_no)=SJXMZZ.audit_proj_index ) AND ((S_JKSJL.ID_SM<>'7777' AND S_JKSJL.ID_SM<>'8888' AND S_JKSJL.ID_SM<>'9999' AND S_JKSJL.ID_SZ='51' AND SUBSTR(ID_SM,1,2)='01' AND TO_CHAR(DATE_JKRQ,'YYYY')>TO_CHAR((SJXMZZ.AUDIT_YEAR)-1) AND TO_CHAR(DATE_SSQZR,'YYYY')=TO_CHAR(SJXMZZ.AUDIT_YEAR) AND D_SWDJB.DATE_GSDJR<TO_DATE('2002/1/1','YYYY/MM/DD') AND D_SWDJB.ID_ZGFS='3') OR
(S_JKSJL.ID_SM<>'7777' AND S_JKSJL.ID_SM<>'8888' AND S_JKSJL.ID_SM<>'9999' AND S_JKSJL.ID_SZ='02' AND TO_CHAR(DATE_JKRQ,'YYYY')>TO_CHAR((SJXMZZ.AUDIT_YEAR)-1) AND TO_CHAR(DATE_SSQZR,'YYYY')=TO_CHAR(SJXMZZ.AUDIT_YEAR) AND D_SWDJB.DATE_GSDJR<TO_DATE('2002/1/1','YYYY/MM/DD') AND D_SWDJB.ID_ZGFS='3'))
access都能做到的,在oracle中 ,我不知怎么搞得,居然奇慢.
请大侠们援手,谢谢.
您好,我们是“2006中国杰出数据库工程师评选”活动组委会。
您的帖子已经被我们转载到本次评选官方网站的“专家在线答疑”区。
http://www.bestdba.cn/match_discussion.aspx在那里,进入本次评选复选的90位数据库工程师将与您展开积极的互动。
一方面,他们会为您的问题提供满意的答案,
另一方面,也邀请您为他们投上宝贵的选票。2006-7-8 ~ 2006-7-25日,每天我们将从当天参与"有奖投票"的网友
中抽取3名幸运者,赠送由IBM提供的精美礼品一份!此外,您还可以在“专家在线答疑”区提出新的问题并参与讨论。您的帖子位于:
http://www.bestdba.cn/match_discussion3.aspx?pointid=284&pointid2=1&pointid3=5&pcount=stc非常感谢您对本次活动的支持!
--------------------------------------------------------------
关于数据表结构,我在写程序时,并不知道将要执行什么样得sql,也不知道表结构.
只知道其中得某张表达到80万条记录,客户告诉我access能查处结果,4千条左右.
有人告诉我说建索引,或用游标.我觉得用游标得话,还是要执行sql语句,应该关系不大.
在这种情况下,各位高手能给我什么指点呢?
FROM d_swdjb, s_jksjl, sjxmzz
WHERE ( TRIM (d_swdjb.sjxx_sel_no) = sjxmzz.audit_proj_index
AND TRIM (s_jksjl.sjxx_sel_no) = sjxmzz.audit_proj_index
AND d_swdjb.id_wjdm = s_jksjl.id_wjdm
)
AND ( (s_jksjl.id_sz = '51' AND SUBSTR (id_sm, 1, 2) = '01')
OR (s_jksjl.id_sz = '02')
)
AND s_jksjl.id_sm <> '7777'
AND s_jksjl.id_sm <> '8888'
AND s_jksjl.id_sm <> '9999'
AND TO_CHAR (date_jkrq, 'YYYY') > TO_CHAR ((sjxmzz.audit_year) - 1)
AND TO_CHAR (date_ssqzr, 'YYYY') = TO_CHAR (sjxmzz.audit_year)
AND d_swdjb.date_gsdjr < TO_DATE ('2002/1/1', 'YYYY/MM/DD')
AND d_swdjb.id_zgfs = '3'
AND UPPER (TRIM (sjxmzz.audit_user_id)) = 'PANG'用这种再试一下,看速度是否提升,没有也没办法