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

解决方案 »

  1.   

    讲讲大概情况:有三张表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秒所有。
      

  2.   

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

  3.   

    楼主应该在查询尽量缩小查询范围,你这里是把两表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的一系列表的最后。
      

  4.   

    一开始我是想写成project表与两个contract表各自left join后再union的,因为这样做的话SQL实在长得离谱而且可读性太差所以写成这样。不过现在没办法验证这样是否能提高效率,因为现在周末休息。但我记得把union all改为union后查询时间缩减为25秒了,但分页时Count时间增长了,所以也没这样用。后来我把contract2也改为contract1后(union两张contract1)发现时间缩减至7秒左右,
    所以估计是union all后两张表的索引没能用于排序中造成的。
    请指教解决方案。
      

  5.   

      试试看这个!
    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=...
      

  6.   

    因为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行,使查询效率提高?
      

  7.   

    多天奋战后终于搞定了,把先前的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
    结贴。
      

  8.   

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