sql语句如下所示:
select a.an, a.num, b.num
from (select a.an, count(distinct b.cp) as num
from xq_jzw a, paper b
where a.is_xq = 'Y'
and instr(b.ad,a.an) =1
group by a.an) a,
(select a.a, sum(bn.cp) as num
from xq_jzw a, paper b
where a.is_xq = 'Y'
and instr(b.ad,a.an) =1
group by a.an) b
where a.n = b.n
该语句的执行计划如下:
SELECT STATEMENT, GOAL = ALL_ROWS Cost=43592665 Cardinality=2457 Bytes=270270
HASH JOIN Cost=43592665 Cardinality=2457 Bytes=270270
VIEW Object owner=PORTAL_DATA Cost=21705301 Cardinality=2457 Bytes=135135
SORT GROUP BY Cost=21705301 Cardinality=2457 Bytes=105651
NESTED LOOPS Cost=21691981 Cardinality=66218330 Bytes=2847388190
TABLE ACCESS FULL Object owner=PORTAL_DATA Object name=xq_jzw Cost=790 Cardinality=2458 Bytes=46702
TABLE ACCESS FULL Object owner=PORTAL_DATA Object name=paper Cost=8825 Cardinality=26941 Bytes=646584
VIEW Object owner=PORTAL_DATA Cost=21887364 Cardinality=2457 Bytes=135135
HASH GROUP BY Cost=21887364 Cardinality=2457 Bytes=95823
NESTED LOOPS Cost=21874044 Cardinality=66218330 Bytes=2582514870
TABLE ACCESS FULL Object owner=PORTAL_DATA Object name=xq_jzw Cost=790 Cardinality=2458 Bytes=46702
TABLE ACCESS FULL Object owner=PORTAL_DATA Object name=paper Cost=8899 Cardinality=26941 Bytes=538820
请问:该语句怎么优化一下,让其执行速度比较快,xq_jzw数据量17万,paper数据量300万
select a.an, a.num, b.num
from (select a.an, count(distinct b.cp) as num
from xq_jzw a, paper b
where a.is_xq = 'Y'
and instr(b.ad,a.an) =1
group by a.an) a,
(select a.a, sum(bn.cp) as num
from xq_jzw a, paper b
where a.is_xq = 'Y'
and instr(b.ad,a.an) =1
group by a.an) b
where a.n = b.n
该语句的执行计划如下:
SELECT STATEMENT, GOAL = ALL_ROWS Cost=43592665 Cardinality=2457 Bytes=270270
HASH JOIN Cost=43592665 Cardinality=2457 Bytes=270270
VIEW Object owner=PORTAL_DATA Cost=21705301 Cardinality=2457 Bytes=135135
SORT GROUP BY Cost=21705301 Cardinality=2457 Bytes=105651
NESTED LOOPS Cost=21691981 Cardinality=66218330 Bytes=2847388190
TABLE ACCESS FULL Object owner=PORTAL_DATA Object name=xq_jzw Cost=790 Cardinality=2458 Bytes=46702
TABLE ACCESS FULL Object owner=PORTAL_DATA Object name=paper Cost=8825 Cardinality=26941 Bytes=646584
VIEW Object owner=PORTAL_DATA Cost=21887364 Cardinality=2457 Bytes=135135
HASH GROUP BY Cost=21887364 Cardinality=2457 Bytes=95823
NESTED LOOPS Cost=21874044 Cardinality=66218330 Bytes=2582514870
TABLE ACCESS FULL Object owner=PORTAL_DATA Object name=xq_jzw Cost=790 Cardinality=2458 Bytes=46702
TABLE ACCESS FULL Object owner=PORTAL_DATA Object name=paper Cost=8899 Cardinality=26941 Bytes=538820
请问:该语句怎么优化一下,让其执行速度比较快,xq_jzw数据量17万,paper数据量300万
解决方案 »
- Oracle连接java软件(iReport报表软件)后,在同一张表同时获取大一大二大三的总人数、男生数、女生数,如何解决?求大神!
- 动态语句创建临时表权限不足????
- 请教:在不安装ORACLE的情况下,如何执行ORACLE存储过程??
- 请问索引表有什么作用
- 我的Oracle客户端如果几分钟不操作,就会自动与服务端断开。其他的的客户端都不这样,请问怎么回事?
- 有没有用jdeveloper11的大侠,里面的treetable的选中问题!
- oci使用中的一点疑问(回答有效, 另外开帖加分)
- 数据恢复的问题
- 请教:谁能告诉一下oracle数据库的各种文件类型和默认的存放位置这方面的资料
- exp 导出命令错误,客户端控制台下运行exp 说是协议适配器错误 大虾帮忙!??!!
- oracle查询(求救!求救!求救!求救!求救!求救!求救!求救!求救!)
- 让我十分纠结的pl/sql 问题:无法解析指定的连接标识符
select a.an,
count(distinct b.cp) as c_num
sum(bn.cp) as s_num,
from xq_jzw a, paper b
where a.is_xq = 'Y'
and instr(b.ad,a.an) =1
group by a.an
begin
dbms_stats.gather_table_stats('PORTAL_DATA','XQ_JZW');
dbms_stats.gather_table_stats('PORTAL_DATA','PAPER');
end;
/
where a.is_xq = 'Y'
and instr(b.ad,a.an) =1
这里能优化的话,性能问题就差不多解决了。首先,我的疑问是,你看看
select * from xq_jzw a, paper b
where a.is_xq = 'Y'
and instr(b.ad,a.an) =1
这里的结果是对的吗?你确定没有重复?