讲讲大概情况:有三张表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秒所有。
union all 本身不会耗用什么资源。 关键还是看数据量有多大。若较大,确认Oracle的排序区参数设置是不是太小了。
楼主应该在查询尽量缩小查询范围,你这里是把两表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的一系列表的最后。
试试看这个! 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=...
因为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行,使查询效率提高?
多天奋战后终于搞定了,把先前的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 结贴。
索引: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秒所有。
关键还是看数据量有多大。若较大,确认Oracle的排序区参数设置是不是太小了。
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的一系列表的最后。
所以估计是union all后两张表的索引没能用于排序中造成的。
请指教解决方案。
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=...
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行,使查询效率提高?
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
结贴。
where CC.LAST_UPDATE_DATE = CC0.LAST_TIME