union all后排序速度问题

[starstroll]

两个大表union all后根据主键ID排序速度奇慢，求解决方案。谢谢！

[huangyunzeng2008]

是不是没有用上索引啊！

[huangyunzeng2008]

查看一下执行计划啊！

[starstroll]

讲讲大概情况：

有三张表project(pid,cid,others),contract1(c1id,others),contract2(c2id,code,others)
索引：project pid,cid分别都有独立索引 contract1 c1id有独立索引 contract2 c2id,code两列有组合索引

查询语句(只大概写一下)：
select * from pid p
left join (
select c1id as cid,others from contract1 where ...
union all
select c2id as cid,others from contract2 where ...
) c on c.cid = p.cid
where c.others=...
order by p.pid;

本来还有分页部分，因为跑到order by 时已经速度奇慢所以不加上了，去掉order by耗时2.5秒左右，加上order by 耗时85秒所有。

[Angly1018]

数据量有多大,看看执行计划

[sulins]

union all 本身不会耗用什么资源。
关键还是看数据量有多大。若较大，确认Oracle的排序区参数设置是不是太小了。

[crazylaa]

楼主应该在查询尽量缩小查询范围，你这里是把两表union起来之后，再去过滤others，试着把others放在union里面，比如：
select * from pid p
left join (
select c1id as cid,others from contract1 where ... and others=....
union all
select c2id as cid,others from contract2 where ... and others=...
) c on c.cid = p.cid
--where c.others=... 外层的others可以不要。
order by p.pid;
或者直接这样：
select * from(
select p.***,...c1.*** from pid p,contract1 c1
where c1.c1id = p.cid and c1.others=...
union
select p.***,...c2.*** from pid p,contract2 c1
where c2.c2id = p.cid and c2.others=...
)
order by p.pid;

另外，如果pid数据量比较小，把它作为基本表放在from的后面，即union的表放在前面。

之后，看看执行计划，是否有利用到索引。因为不能确定你的others到底是一个字段呢，还是其他几个字段＝什么东西，你这里全部用others来代替了。
总之能去掉最多记录的条件放在最后，数据量最少的表作为基本表放在from的一系列表的最后。

[starstroll]

一开始我是想写成project表与两个contract表各自left join后再union的，因为这样做的话SQL实在长得离谱而且可读性太差所以写成这样。

不过现在没办法验证这样是否能提高效率，因为现在周末休息。

但我记得把union all改为union后查询时间缩减为25秒了，但分页时Count时间增长了，所以也没这样用。

后来我把contract2也改为contract1后（union两张contract1）发现时间缩减至7秒左右，
所以估计是union all后两张表的索引没能用于排序中造成的。


请指教解决方案。

[BenChiM888]

[Quote=引用 7 楼 starstroll 的回复:]
一开始我是想写成project表与两个contract表各自left join后再union的，因为这样做的话SQL实在长得离谱而且可读性太差所以写成这样。

不过现在没办法验证这样是否能提高效率，因为现在周末休息。

但我记得把union all改为union后查询时间缩减为25秒了，但分页时Count时间增长了，所以也没这样用。

后来我把contract2也改为contract1后（union两张contract1）发现时间缩减至7秒左右，
所以估计是union all后两张表的索引没能用于排序中造成的。


请指教解决方案。
[/Quote]
可读性好的不一定性能好，人能读懂固然重要 但是任务还是要机器跑的，机器能够读懂而且选择最快的方法读是更重要的。

[碧水幽幽泉]

[Quote=引用 3 楼 starstroll 的回复:]
讲讲大概情况：

有三张表project(pid,cid,others),contract1(c1id,others),contract2(c2id,code,others)
索引：project  pid,cid分别都有独立索引  contract1  c1id有独立索引    contract2  c2id,code两列有组合索引

查询语句(只大概写一下)：
select * from pid p
left join (
  select c1id as cid,others from contract1 where ...
  union all
  select c2id as cid,others from contract2 where ...
) c on c.cid = p.cid
where c.others=...
order by p.pid;

本来还有分页部分，因为跑到order by 时已经速度奇慢所以不加上了，去掉order by耗时2.5秒左右，加上order by 耗时85秒所有。
[/Quote]
试试看这个！
select * from pid p
left join (
select c1id as cid,others from contract1 where ...
union all
select c2id as cid,others from contract2 where ...
order by cid
) c on c.cid = p.cid
where c.others=...

[starstroll]

因为max(LAST_UPDATE_DATE)导致速度变慢，已定位到一下SQL中

select * from (
select CONTRACT_ID,
     CUSTOMER_CONTRACT_NUMBER as CUSTOMER_PO_NUMBER,
     CONTRACT_NUMBER,
     OPERATION_TYPE,
     LAST_UPDATE_DATE,
     max(LAST_UPDATE_DATE) over(partition by CONTRACT_ID) as LAST_TIME
from PROJECT.CM_CONTRACTS_SINGLE_V) CC
where CC.LAST_UPDATE_DATE = CC.LAST_TIME

另一种方式：

select CC.CONTRACT_ID,
     CC.CUSTOMER_CONTRACT_NUMBER as CUSTOMER_PO_NUMBER,
     CC.CONTRACT_NUMBER,
     CC.OPERATION_TYPE
from PROJECT.CM_CONTRACTS_SINGLE_V CC
where 
CC.LAST_UPDATE_DATE=
(select max(CC1.LAST_UPDATE_DATE) from PROJECT.CM_CONTRACTS_SINGLE_V CC1 where CC1.CONTRACT_ID=CC.CONTRACT_ID)

上面两句都慢，请教有没有其他方式取得最后一次更新时的CM_CONTRACTS_SINGLE_V行，使查询效率提高？

[starstroll]

多天奋战后终于搞定了，把先前的SQL换一下85秒的SQL变成0.031秒。。。汗。。。（-.-!）

select CC.CONTRACT_ID,
       CC.CUSTOMER_CONTRACT_NUMBER as CUSTOMER_PO_NUMBER,
       CC.CONTRACT_NUMBER,
       CC.OPERATION_TYPE
  from PROJECT.CM_CONTRACTS_SINGLE_V CC
  left join (select CC1.CONTRACT_ID, max(CC1.LAST_UPDATE_DATE) as LAST_TIME
               from PROJECT.CM_CONTRACTS_SINGLE_V CC1
              group by CC1.CONTRACT_ID) CC0 on CC0.CONTRACT_ID =
                                               CC.CONTRACT_ID
   and CC.LAST_UPDATE_DATE = CC0.LAST_TIME

结贴。。。。

[starstroll]

写错了，应该是
where CC.LAST_UPDATE_DATE = CC0.LAST_TIME