我寫的查詢sql 是要計算的百分比的: select * from (SELECT je,gfnsrsbh,votesNo, 100 * round(je/ sum(je) over (),4) as percent
FROM (
SELECT gf_nsrsbh AS gfnsrsbh, SUM(je) AS je,count(*) as votesNo
FROM CB_FDKFPCGL_MX b
WHERE kprq BETWEEN to_date('2016-08-01', 'yyyy-mm-dd') AND to_date('2016-10-20', 'yyyy-mm-dd') and exists(
SELECT gf_nsrsbh FROM CB_FDKFPCGL_MX c
where kprq BETWEEN to_date('2016-08-01', 'yyyy-mm-dd') AND to_date('2016-10-20', 'yyyy-mm-dd') AND xf_nsrsbh ='330100555183365'
and b.gf_nsrsbh=c.gf_nsrsbh)GROUP BY gf_nsrsbh) ORDER BY percent desc) where ROWNUM <=5
查詢時間太長了要2分鐘以上，有沒有可以優化的呢？
uj5u.com熱心網友回復：
建表陳述句貼一下，索引情況也貼一下，你這個陳述句的執行計劃也貼一下；uj5u.com熱心網友回復：
這是建表陳述句create table CB_FDKFPCGL_MX
(
swjg_dm VARCHAR2(11) not null,
bs_nsrsbh VARCHAR2(20) not null,
kpjh INTEGER not null,
bsyf INTEGER not null,
fpdm CHAR(10) not null,
fphm CHAR(8) not null,
gf_nsrsbh VARCHAR2(20) not null,
xf_nsrsbh VARCHAR2(20) not null,
je NUMBER(19,2) not null,
se NUMBER(19,2),
kprq DATE not null,
zfbz CHAR(1) not null,
bdbz CHAR(1) not null,
zs_fpdm VARCHAR2(16),
zs_fphm CHAR(8),
bsfs CHAR(1),
bssj DATE,
czy_dm VARCHAR2(12),
czy_mc VARCHAR2(30),
crc VARCHAR2(32),
blbz CHAR(1),
ccbz CHAR(1),
packedtag VARCHAR2(20),
fp_lb VARCHAR2(2) default 'YB' not null,
nsr_xz INTEGER,
sl NUMBER(20,3),
xf_qymc VARCHAR2(100),
gf_qymc VARCHAR2(100),
qd_bz VARCHAR2(2),
bl_lb CHAR(1),
czdate TIMESTAMP(6),
czdate_char VARCHAR2(22),
xhqd_bz VARCHAR2(2),
jshj NUMBER(19,2),
jqbh VARCHAR2(16),
fp_mw VARCHAR2(200),
jym VARCHAR2(20),
skr VARCHAR2(20),
fhr VARCHAR2(20),
kpr VARCHAR2(20),
bz VARCHAR2(240),
gfyhzh VARCHAR2(120),
xfyhzh VARCHAR2(120),
gfdzdh VARCHAR2(120),
xfdzdh VARCHAR2(120),
kjlx CHAR(1),
zfsj DATE,
wspzh VARCHAR2(200),
fpqm VARCHAR2(1024),
bl_czy_mc VARCHAR2(30),
blsj DATE,
tspzbz CHAR(2),
fpqm_status VARCHAR2(15),
csqj VARCHAR2(6),
dk_nsrsbh VARCHAR2(20),
dk_qymc VARCHAR2(100),
sghp VARCHAR2(1),
spbmbbh VARCHAR2(19),
slbs CHAR(1)
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64
minextents 1
maxextents unlimited
);
-- Create/Recreate indexes
create index IDX_CBFDKFPCGLMX_RQGFNSRSBH on CB_FDKFPCGL_MX (GF_NSRSBH, KPRQ)
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
create index IDX_CBFDKFPCGLMX_XFNSRSBH on CB_FDKFPCGL_MX (XF_NSRSBH)
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
create index IDX_CBFDKFPCGL_MX_XFNSRSBH on CB_FDKFPCGL_MX (XF_NSRSBH, KPRQ)
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
uj5u.com熱心網友回復：
沒執行計劃沒法看啊uj5u.com熱心網友回復：
大神們看看是這個sql執行計劃么？
uj5u.com熱心網友回復：
sql執行計劃 截圖出來了
uj5u.com熱心網友回復：
select *from (SELECT je,
gfnsrsbh,
votesNo,
100 * round(je / sum(je) over(), 4) as percent
FROM (SELECT gf_nsrsbh AS gfnsrsbh,
SUM(je) AS je,
count(*) as votesNo
FROM CB_FDKFPCGL_MX b
WHERE kprq BETWEEN to_date('2016-08-01', 'yyyy-mm-dd') AND
to_date('2016-10-20', 'yyyy-mm-dd')
AND xf_nsrsbh = '330100555183365'
GROUP BY gf_nsrsbh)
ORDER BY percent desc)
where ROWNUM <= 5
先比較下，結果和你寫的是不是一樣的
uj5u.com熱心網友回復：
結果是我想要 的一樣的uj5u.com熱心網友回復：
SELECT gf_nsrsbh
FROM CB_FDKFPCGL_MX c
where kprq BETWEEN
to_date('2016-08-01', 'yyyy-mm-dd') AND
to_date('2016-10-20', 'yyyy-mm-dd')
AND xf_nsrsbh = '330100555183365'
and b.gf_nsrsbh = c.gf_nsrsbh
這里沒有走索引。
這個結果集差出來有多少資料。CB_FDKFPCGL_MX 表中一共有多少資料？
查看索引狀態，重新收集統計資訊，再看效果
uj5u.com熱心網友回復：
sql查詢出來結果集：
表 的總數：
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/96379.html
標籤：開發
上一篇：值傳遞
下一篇：ORACLE.exe 卡硬碟