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分钟以上,有没有可以优化的呢?
9 个解决方案
#1
建表语句贴一下,索引情况也贴一下,你这个语句的执行计划也贴一下;
#2
这是建表语句
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
);
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
);
#3
没执行计划没法看啊
#4
大神们看看是这个sql执行计划么?
#5
sql执行计划 截图出来了
#6
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
先比较下,结果和你写的是不是一样的
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
先比较下,结果和你写的是不是一样的
#7
结果是我想要 的一样的
#8
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 表中一共有多少数据?
查看索引状态,重新收集统计信息,再看效果
#9
sql查询出来结果集:
表 的总数:
表 的总数:
#1
建表语句贴一下,索引情况也贴一下,你这个语句的执行计划也贴一下;
#2
这是建表语句
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
);
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
);
#3
没执行计划没法看啊
#4
大神们看看是这个sql执行计划么?
#5
sql执行计划 截图出来了
#6
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
先比较下,结果和你写的是不是一样的
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
先比较下,结果和你写的是不是一样的
#7
结果是我想要 的一样的
#8
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 表中一共有多少数据?
查看索引状态,重新收集统计信息,再看效果
#9
sql查询出来结果集:
表 的总数:
表 的总数: