我写的一句查询语句
效率特别低,要执行40秒左右
各位大侠看看问题出在哪里
select count(1) jbxxtqhs,
nvl(sum(sb.ysb), 0) ysbnshs,
sum(decode(sb.sb_se, 0, 1, 0)) lsbhs,
nvl(count(1) - sum(ysb), 0) yqwsbhs,
nvl(round((sum(ysb) * 100) / count(1), 2), 0) sbl,
nvl(to_char(sum(sb_se) / 10000, 'FM999,999,999,990.00'), 0) sbynse
from hd_hdxx hd,
(select a.nsrsbh, 1 ysb, sum(a.sb_se) sb_se
from sb_sbqk a
where 1 = 1
and (a.sbny = '200807')
and a.sbzt_dm = '0000'
group by a.nsrsbh) sb
where hd.sbny = '200807'
and (hd.nsrsbh = sb.nsrsbh(+))
and (hd.swjg_dm like '113%')其中hd_hdxx 数据量是130000条左右,sb_sbqk是80000条左右
效率特别低,要执行40秒左右
各位大侠看看问题出在哪里
select count(1) jbxxtqhs,
nvl(sum(sb.ysb), 0) ysbnshs,
sum(decode(sb.sb_se, 0, 1, 0)) lsbhs,
nvl(count(1) - sum(ysb), 0) yqwsbhs,
nvl(round((sum(ysb) * 100) / count(1), 2), 0) sbl,
nvl(to_char(sum(sb_se) / 10000, 'FM999,999,999,990.00'), 0) sbynse
from hd_hdxx hd,
(select a.nsrsbh, 1 ysb, sum(a.sb_se) sb_se
from sb_sbqk a
where 1 = 1
and (a.sbny = '200807')
and a.sbzt_dm = '0000'
group by a.nsrsbh) sb
where hd.sbny = '200807'
and (hd.nsrsbh = sb.nsrsbh(+))
and (hd.swjg_dm like '113%')其中hd_hdxx 数据量是130000条左右,sb_sbqk是80000条左右
SORT AGGREGATE Cardinality=1 Bytes=55
HASH JOIN OUTER Cost=11 Cardinality=891 Bytes=49005
TABLE ACCESS BY INDEX ROWID Object owner=USR_SB Object name=HD_HDXX Cost=6 Cardinality=891 Bytes=24057
INDEX RANGE SCAN Object owner=USR_SB Object name=PK_HD_HDXX Cost=2 Cardinality=7131
VIEW Object owner=USR_GGYW Cost=4 Cardinality=29 Bytes=812
SORT GROUP BY Cost=4 Cardinality=29 Bytes=1131
TABLE ACCESS BY INDEX ROWID Object owner=USR_SB Object name=SB_SBQK Cost=2 Cardinality=29 Bytes=1131
INDEX RANGE SCAN Object owner=USR_SB Object name=IDX_SB_SBQK_SBNY_SBZT Cost=1 Cardinality=5
表hd_hdxx :
SBNY CHAR(6) N
NSRSBH VARCHAR2(20) N
LRR_DM VARCHAR2(11) N
LRRQ DATE N
XGR_DM VARCHAR2(11) N
XGRQ DATE N
HDXX CLOB Y
FPLGXX VARCHAR2(4000) Y
JXFP_MX CLOB Y
XXFP_HZ VARCHAR2(4000) Y
SWJG_DM VARCHAR2(11) N
表SB_SBQK
SB_XH NUMBER(10) N
SBFS_DM CHAR(2) N
SBNY CHAR(6) N
SBZL_DM CHAR(5) N
SBZT_DM CHAR(4) N
ZSXM_DM CHAR(2) N
NSRSBH VARCHAR2(20) N
SWJG_DM VARCHAR2(11) N
LRR_DM VARCHAR2(11) N
LRRQ DATE N
XGR_DM VARCHAR2(11) N
XGRQ DATE N
QQWJM VARCHAR2(80) Y
JYLSH_SB CHAR(28) Y
QQKS_SJ DATE Y
JYWC_SJ DATE Y
TJYS NUMBER(4) Y
TJSJD NUMBER(2) Y
FKXX CLOB Y
DLNSRSBH VARCHAR2(20) Y
SB_SE NUMBER(16,2) Y
ZKK_SE NUMBER(16,2) Y
YKK_SE NUMBER(16,2) Y
PZXH CHAR(20) Y
SPHM CHAR(20) Y
YZM VARCHAR2(16) Y
看看主要在等待什么.是IO的问题还是其它问题 另外用的是 HASH JOIN , HASH JOIN 应该是小表快点
你用
select /*+USE_NL(SB)*/count(1) jbxxtqhs,
nvl(sum(sb.ysb), 0) ysbnshs,
sum(decode(sb.sb_se, 0, 1, 0)) lsbhs,
nvl(count(1) - sum(ysb), 0) yqwsbhs,
nvl(round((sum(ysb) * 100) / count(1), 2), 0) sbl,
nvl(to_char(sum(sb_se) / 10000, 'FM999,999,999,990.00'), 0) sbynse
from hd_hdxx hd,
(select a.nsrsbh, 1 ysb, sum(a.sb_se) sb_se
from sb_sbqk a
where 1 = 1
and (a.sbny = '200807')
and a.sbzt_dm = '0000'
group by a.nsrsbh) sb
where hd.sbny = '200807'
and (hd.nsrsbh = sb.nsrsbh(+))
and (hd.swjg_dm like '113%') 去试试,不过这种HINT我没用过,不知道写法对吗?
换一下where 的条件顺序!试试看!