oracle union后分组慢,数据量庞大（25万条），要运算加分组，反应极慢，如何优化啊求解，sql比较多...

最新推荐文章于 2022-10-14 18:15:54 发布
最新推荐文章于 2022-10-14 18:15:54 发布
阅读量541 收藏
点赞数
文章标签： oracle union后分组慢

select rownum,

collieryname,

transunitname,

collieryid,

transunitid,

plannum,

truenum,

decode

(substr(arrive, 1, 1), '.', '0' || arrive, arrive) arrive

from (select collieryname,

transunitname,

collieryid,

transunitid,

plannum,

truenum,

arrive

from (select thr2.collieryname,

thr3.transunitname,

thr1.collieryid,

thr1.transunitid,

thr1.plannum,

thr1.truenum,

round(thr1.truenum / thr1.plannum, 3) * 100 arrive

from (select distinct collieryid,

transunitid,

plannum,

truenum

from (select sec1.collieryid,

to_number(sec1.transunitid) transunitid,

plannum,

truenum

from (select t2.collieryid,

to_number(t1.TRANSUNIT) transunitid,

sum(t1.TCOUNT) plannum

from (select TRANSUNIT, TCOUNT, PLANID

from HNDFBPS_FUEL.T_TRUCKBILL

where TDATE >= '2013-01-25'

and TDATE <= '2013-01-25') t1,

HNDFBPS_FUEL.t_truckcoalplan t2,

HNDFBPS_fuel.T_TRUCKATTEMPPLAN t3

where t1.PLANID = t3.id

and t3.TRUCKCOALPLANID = t2.id

group by t2.collieryid, t1.TRANSUNIT) sec1,

(select count(id) truenum,

collieryid,

transunitid

from (select collieryid,

t.id,

(select transunitid

from HNDFBPS_FUEL.t_truckbelong

where truckid = t.truckid

and substr

(tosamtime,

1,

10) >=

starttime

and substr(tosamtime,

1,

10) <=

stoptime) transunitid

from HNDFBPS_FUEL.t_truckenter t

where SUBSTR(t.tosamtime, 1, 10) >=

'2013-01-25'

and SUBSTR(t.tosamtime, 1, 10) <=

'2013-01-25'

and t.eweighttime is not null) t1

group by t1.collieryid, t1.transunitid) sec2

where sec1.collieryid = sec2.collieryid(+)

and sec1.transunitid = sec2.transunitid(+)

union all

select sec2.collieryid,

sec2.transunitid,

plannum,

truenum

from (select t2.collieryid,

to_number(t1.TRANSUNIT) transunitid,

sum(t1.TCOUNT) plannum

from (select TRANSUNIT, TCOUNT, PLANID

from HNDFBPS_FUEL.T_TRUCKBILL

where TDATE >= '2013-01-25'

and TDATE <= '2013-01-25') t1,

HNDFBPS_FUEL.t_truckcoalplan t2,

HNDFBPS_fuel.T_TRUCKATTEMPPLAN t3

where t1.PLANID = t3.id

and t3.TRUCKCOALPLANID = t2.id

group by t2.collieryid, t1.TRANSUNIT) sec1,

(select count(id) truenum,

collieryid,

transunitid

from (select collieryid,

t.id,

(select transunitid

from HNDFBPS_FUEL.t_truckbelong

where truckid = t.truckid

and substr(tosamtime,

1,

10) >=

starttime

and substr(tosamtime,

1,

10) <=

stoptime) transunitid

from HNDFBPS_FUEL.t_truckenterover t

where SUBSTR(t.tosamtime, 1, 10) >=

'2013-01-25'

and SUBSTR(t.tosamtime, 1, 10) <=

'2013-01-25'

and t.eweighttime is not null) t1

group by t1.collieryid, t1.transunitid) sec2

where sec1.collieryid(+) = sec2.collieryid

and sec1.transunitid(+) = sec2.transunitid)) thr1,

HNDFBPS_FUEL.t_colliery thr2,

HNDFBPS_FUEL.t_transunit thr3

where thr1.collieryid = thr2.id

and thr1.transunitid = thr3.id

union all

select thr2.collieryname || '：合计' ncollieryname,

'' transunitname,

collieryid,

transunitid,

nplannum,

ntruenum,

arrive

from (select collieryid,

0 transunitid,

sum(plannum) nplannum,

sum(truenum) ntruenum,

round(sum(truenum) / sum(plannum), 3) * 100 arrive

from (select distinct collieryid,

transunitid,

plannum,

truenum

from (select sec1.collieryid,

to_number(sec1.transunitid) transunitid,

plannum,

truenum

from (select t2.collieryid,

to_number(t1.TRANSUNIT) transunitid,

sum(t1.TCOUNT) plannum

from (select TRANSUNIT,

TCOUNT,

PLANID

from HNDFBPS_FUEL.T_TRUCKBILL

where TDATE >=

'2013-01-25'

and TDATE <=

'2013-01-25') t1,

HNDFBPS_FUEL.t_truckcoalplan t2,

HNDFBPS_fuel.T_TRUCKATTEMPPLAN t3

where t1.PLANID = t3.id

and t3.TRUCKCOALPLANID = t2.id

group by t2.collieryid,

t1.TRANSUNIT) sec1,

(select count(id) truenum,

collieryid,

transunitid

from (select collieryid,

t.id,

(select transunitid

from HNDFBPS_FUEL.t_truckbelong

where truckid =

t.truckid

and substr(tosamtime,

1,

10) >=

starttime

and substr(tosamtime,

1,

10) <=

stoptime) transunitid

from HNDFBPS_FUEL.t_truckenter t

where SUBSTR(t.tosamtime,

1,

10) >=

'2013-01-25'

and SUBSTR(t.tosamtime,

1,

10) <=

'2013-01-25'

and t.eweighttime is not null) t1

group by t1.collieryid,

t1.transunitid) sec2

where sec1.collieryid =

sec2.collieryid(+)

and sec1.transunitid =

sec2.transunitid(+)

union all

select sec2.collieryid,

sec2.transunitid,

plannum,

truenum

from (select t2.collieryid,

to_number(t1.TRANSUNIT) transunitid,

sum(t1.TCOUNT) plannum

from (select TRANSUNIT,

TCOUNT,

PLANID

from HNDFBPS_FUEL.T_TRUCKBILL

where TDATE >=

'2013-01-25'

and TDATE <=

'2013-01-25') t1,

HNDFBPS_FUEL.t_truckcoalplan t2,

HNDFBPS_fuel.T_TRUCKATTEMPPLAN t3

where t1.PLANID = t3.id

and t3.TRUCKCOALPLANID = t2.id

group by t2.collieryid,

t1.TRANSUNIT) sec1,

(select count(id) truenum,

collieryid,

transunitid

from (select collieryid,

t.id,

(select transunitid

from HNDFBPS_FUEL.t_truckbelong

where truckid =

t.truckid

and substr(tosamtime,

1,

10) >=

starttime

and substr(tosamtime,

1,

10) <=

stoptime) transunitid

from HNDFBPS_FUEL.t_truckenterover t

where SUBSTR(t.tosamtime,

1,

10) >=

'2013-01-25'

and SUBSTR(t.tosamtime,

1,

10) <=

'2013-01-25'

and t.eweighttime is not null) t1

group by t1.collieryid,

t1.transunitid) sec2

where sec1.collieryid(+) =

sec2.collieryid

and sec1.transunitid(+) =

sec2.transunitid)) tem1

group by tem1.collieryid) thr1,

HNDFBPS_FUEL.t_colliery thr2

where thr1.collieryid = thr2.id

union all

select '总合计' ncollieryname,

'' transunitname,

collieryid,

transunitid,

nplannum,

ntruenum,

arrive

from (select 0 collieryid,

0 transunitid,

sum(plannum) nplannum,

sum(truenum) ntruenum,

round(sum(truenum) / sum(plannum), 3) * 100 arrive

from (select distinct collieryid,

transunitid,

plannum,

truenum

from (select sec1.collieryid,

to_number(sec1.transunitid) transunitid,

plannum,

truenum

from (select t2.collieryid,

to_number(t1.TRANSUNIT) transunitid,

sum(t1.TCOUNT) plannum

from (select TRANSUNIT,

TCOUNT,

PLANID

from HNDFBPS_FUEL.T_TRUCKBILL

where TDATE >=

'2013-01-25'

and TDATE <=

'2013-01-25') t1,

HNDFBPS_FUEL.t_truckcoalplan t2,

HNDFBPS_fuel.T_TRUCKATTEMPPLAN t3

where t1.PLANID = t3.id

and t3.TRUCKCOALPLANID = t2.id

group by t2.collieryid,

t1.TRANSUNIT) sec1,

(select count(id) truenum,

collieryid,

transunitid

from (select collieryid,

t.id,

(select transunitid

from HNDFBPS_FUEL.t_truckbelong

where truckid =

t.truckid

and substr(tosamtime,

1,

10) >=

starttime

and substr(tosamtime,

1,

10) <=

stoptime) transunitid

from HNDFBPS_FUEL.t_truckenter t

where SUBSTR(t.tosamtime,

1,

10) >=

'2013-01-25'

and SUBSTR(t.tosamtime,

1,

10) <=

'2013-01-25'

and t.eweighttime is not null) t1

group by t1.collieryid,

t1.transunitid) sec2

where sec1.collieryid =

sec2.collieryid(+)

and sec1.transunitid =

sec2.transunitid(+)

union all

select sec2.collieryid,

sec2.transunitid,

plannum,

truenum

from (select t2.collieryid,

to_number(t1.TRANSUNIT) transunitid,

sum(t1.TCOUNT) plannum

from (select TRANSUNIT,

TCOUNT,

PLANID

from HNDFBPS_FUEL.T_TRUCKBILL

where TDATE >=

'2013-01-25'

and TDATE <=

'2013-01-25') t1,

HNDFBPS_FUEL.t_truckcoalplan t2,

HNDFBPS_fuel.T_TRUCKATTEMPPLAN t3

where t1.PLANID = t3.id

and t3.TRUCKCOALPLANID = t2.id

group by t2.collieryid,

t1.TRANSUNIT) sec1,

(select count(id) truenum,

collieryid,

transunitid

from (select collieryid,

t.id,

(select transunitid

from HNDFBPS_FUEL.t_truckbelong

where truckid =

t.truckid

and substr(tosamtime,

1,

10) >=

starttime

and substr(tosamtime,

1,

10) <=

stoptime) transunitid

from HNDFBPS_FUEL.t_truckenterover t

where SUBSTR(t.tosamtime,

1,

10) >=

'2013-01-25'

and SUBSTR(t.tosamtime,

1,

10) <=

'2013-01-25'

and t.eweighttime is not null) t1

group by t1.collieryid,

t1.transunitid) sec2

where sec1.collieryid(+) =

sec2.collieryid

and sec1.transunitid(+) =

sec2.transunitid)) sec1) thr1)

order by collieryname)

那一次仰望
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
oracle sum() 慢,oracle 查询sum很慢
weixin_30488255的博客
04-09 2449
这是oracle 查询语句，查出来需要200s，返回行数100，表都不到100w，不加sum很快，加了sum很慢，sql如下：select sum(qty) tag_qty, rc_idfrom (select ser_id,func_get_pdr_count(service_tag, dispatch_time) as qty,rc_idfrom (select s.ser_id,p.rc_i...
oracle 分组_大数据分组怎样才会更快
weixin_39721370的博客
12-06 510
关键词导读：数据库分组慢 大数据分组慢 存储格式分组是数据库的常见运算，无论数据如何准备，通常都需要将所有数据遍历。建立索引这时是不起作用的，存储格式才是决定遍历效率的主要因素。数据库中数据的存放虽然是二进制格式的，但普遍IO性能差，库内遍历快，外部取数都很慢。下面用Oracle来举个例子，数据如下： ORDERID CLIENT SELLERID AMOUNT ...
解决UNION速度慢
皮匠的锦囊箱（達人になろう）
03-15 1610
本地SQL使用UNION时速度特别慢，另一台服务器特别快，查到原因是sort_area_size太小了 太小还时不时会报ORA-17410: No more data to read from socket Select name, value FROM v$sysstat where name like '%sort%' select * from v$parameter ...
Oracle19c中SQL分开跑很快UNION ALL之后变慢分析
落落的专栏 专注SQL调优 性能调优
10-11 3744
SQL分开跑很快，UNION ALL之后变慢
50种方法巧妙优化你的SQL Server数据库
guyue6670的专栏
06-20 1527
原文链接:http://blog.btidea.com/article.asp?id=440 查询速度慢的原因很多，常见如下几种： 　　 　　1、没有索引或者没有用到索引(这是查询慢最常见的问题，是程序设计的缺陷) 　　 　　2、I/O吞吐量小，形成了瓶颈效应。 　　 　　3、没有创建计算列导致查询不优化。 　　 　　4、内存不足 　　 　　5、网络速度慢 　　 　　6、
Oracle视图查询慢之统计信息收集
不忘初心、方得始终
08-04 913
今天发现数据库中有一个视图执行非常缓慢，查询从原来的0.4秒左右变慢到3秒多， 查看了sql语句并没有被修改过，怀疑可能是统计信息过旧。 --在当前用户下执行 select 'analyze table '||table_name||' compute statistics;' from user_tables; --得到如下语句，将这些语句整体执行一遍 analyze table PR...
Oracle Distinct优化 速度提升100倍
weixin_43171875的博客
09-02 3008
Oracle Distinct优化 速度提升100倍，优化前时间为：13s 608ms， 优化后：104ms。
oracle教程11 Oracle的分组函数与数据分组详解及举例
.... 永远年轻，永远热泪盈眶
04-12 5442
Oracle的分组函数 --求平均值avg select avg(sal) from emp; --求和sum select sum(sal) from emp; --求最大值max select max(sal) from emp; --求最小值min select min(sal) from emp; --计数函数count select count(*) from emp; se...
大数据分组怎样才会更快
rqgxy的博客
06-08 518
分组是数据库的常见运算，无论数据如何准备，通常都需要将所有数据遍历。建立索引这时是不起作用的，存储格式才是决定遍历效率的主要因素。数据库中数据的存放虽然是二进制格式的，但普遍IO性能差，库内遍历快，外部取数都很慢。 下面用Oracle来举个例子，数据如下： ORDERID CLIENT SELLERID AMOUNT ORDERDATE NOTE 1 287 47 5825 2013-05-31 gafcaghaf...
性能优化技巧：遍历复用提速多次分组
ertyee42的博客
12-17 194
我们知道，大数据运算性能的瓶颈常常是在外存（也就是硬盘）IO上，因为外存访问性能要比内存低一两个数量级。因此，做性能优化时，减少硬盘的访问量有时要比减少CPU计算量更为重要。同一个任务，如果能使用硬盘访问量更少的算法，即使CPU计算量不变甚至略多一点，也会获得更好的性能。 分组汇总需要对数据集进行遍历。同一个数据集可能会按不同维度进行分组，这样原则上就要遍历多次，大数据时就会涉及多遍硬盘访问。但...
oracle union有数量限制吗,对union集合操作理解不足造成的巨大的灾难
weixin_39859061的博客
04-04 1114
朋友和我说发现了oracle union操作的一个bug,他的查询语句如下:SQL> select sum(a1) a1,sum(a2) a22 from (3 select 1 a1,0 a2 from dual4 union5 select 0 a2, 1 a1 from dual6 )7 ;A1 A2---------- ----------1...
sql 用union统计多张表数据量总数偏少（记录小坑）
qq_42999256的博客
08-29 1292
用union统计多张表数据量总数时 数据量变少
换个思路解决union连接数据过多时，导致查询慢的问题
不试一下你怎么知道自己有多强？最不像程序员的程序员的专栏
03-05 7719
最近在一开发的过程中用到了union连接多个查询结果，但这种方式是数据量大的时候会导致查询慢。为此想了一些办法来解决之个问题，在开始之前呢，先简单说下union与union all的区别，union将查询结果合并时会将重复的数据合并为一条，union all则不会将重复数据其合并为一条。 业务场景：主表A存储了一个id，这个主键即有可能在B表中，也有可能在C表中（可以简单理解为分表操作，实际业务...
oracle 建临时表解决大量数据union产生的问题
老别
09-01 634
1 解决sql太长的问题 2 CREATE GLOBAL TEMPORARY TABLE tmp_a( cuid VARCHAR2(400) ) ON COMMIT DELETE ROWS; 3 insert into... 4 查询 5 回话关闭后，临时表数据自动清空
【Oracle】多表联合查询超时问题排查
最新发布
Kida 的技术小屋（CSDN 版）
10-14 2213
提供一个多表联合查询超时排查的思路，文章是2016年写的会存在一定的局限性各位将就着看，仅代表个人看法
oracle union后分组慢,单独执行很快,可是用union all就很慢了,请问如何优化?
weixin_36366711的博客
04-03 2527
[php]select t.id,t.group_order,t.ID_OHPPfrom (select rowid id,row_number() over(order by des) rn,1 as group_order,ID_OHPP,desfrom security_indicwhere des like 'US%') twhere rn>0 and rownum<=20UN...
oracle order by效率慢
weixin_47136046的博客
01-07 5256
springboot mybatis代码使用数据库oracle。使用select分页查询的时候，返回结果很耗时40+s。 我放到执行计划里面看了下也没耗很长时间，不应该需要使用40+s。 然后尝试了下把order by去掉就变很快了，再加回来就又慢了。初步判断是order by引起的。 做了个尝试把order by前置放到select语句中 原本 select * from xxx order by a desc,b desc; 改成： select row_number() over(ORDER BY
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值