Oracle SQL优化 求指教?????????????????????

 SQL如下:

select * from
(
  select * from   
  (
  select ROW_NUMBER() OVER(PARTITION BY v1.hphm ORDER BY v1.jgsj DESC) RN1,v1.hphm hphm1,v1.qdbh qdbh1,v1.jgsj jgsj1,v1.fxbh fxbh1
  from t_vms_passcar v1 where v1.qdbh=374 
  and v1.jgsj>to_date('2012-6-18 14:35:03','yyyy-mm-dd hh24:mi:ss')
  and v1.jgsj<to_date('2012-6-18 14:59:03','yyyy-mm-dd hh24:mi:ss')  
  ) 
  where RN1<2
)  p1
,
(
  select * from   (
  select ROW_NUMBER() OVER(PARTITION BY v2.hphm ORDER BY v2.jgsj DESC) RN2,v2.hphm hphm2,v2.qdbh qdbh2,v2.jgsj jgsj2,v2.fxbh fxbh2 from t_vms_passcar v2 where v2.qdbh=375 
  and v2.jgsj>to_date('2012-6-18 14:35:03','yyyy-mm-dd hh24:mi:ss')
  and v2.jgsj<to_date('2012-6-18 14:59:03','yyyy-mm-dd hh24:mi:ss')
  ) where RN2<2
)  p2
,
(
  select * from   (
  select ROW_NUMBER() OVER(PARTITION BY v3.hphm ORDER BY v3.jgsj DESC) RN3,v3.hphm hphm3,v3.qdbh qdbh3,v3.jgsj jgsj3,v3.fxbh fxbh3 from t_vms_passcar v3 where v3.qdbh=374 
  and v3.jgsj>to_date('2012-6-18 15:00:00','yyyy-mm-dd hh24:mi:ss')
  and v3.jgsj<to_date('2012-6-18 15:30:00','yyyy-mm-dd hh24:mi:ss')
  ) where RN3<2
)  p3
where p1.hphm1=p2.hphm2(+) and p1.hphm1=p3.hphm3(+)
union
select * from
(
  select * from   
  (
  select ROW_NUMBER() OVER(PARTITION BY v1.hphm ORDER BY v1.jgsj DESC) RN1,v1.hphm hphm1,v1.qdbh qdbh1,v1.jgsj jgsj1,v1.fxbh fxbh1
  from t_vms_passcar v1 where v1.qdbh=374 
  and v1.jgsj>to_date('2012-6-18 14:35:03','yyyy-mm-dd hh24:mi:ss')
  and v1.jgsj<to_date('2012-6-18 14:59:03','yyyy-mm-dd hh24:mi:ss')  
  ) 
  where RN1<2
)  p1
,
(
  select * from   (
  select ROW_NUMBER() OVER(PARTITION BY v2.hphm ORDER BY v2.jgsj DESC) RN2,v2.hphm hphm2,v2.qdbh qdbh2,v2.jgsj jgsj2,v2.fxbh fxbh2 from t_vms_passcar v2 where v2.qdbh=375 
  and v2.jgsj>to_date('2012-6-18 14:35:03','yyyy-mm-dd hh24:mi:ss')
  and v2.jgsj<to_date('2012-6-18 14:59:03','yyyy-mm-dd hh24:mi:ss')
  ) where RN2<2
)  p2
,
(
  select * from   (
  select ROW_NUMBER() OVER(PARTITION BY v3.hphm ORDER BY v3.jgsj DESC) RN3,v3.hphm hphm3,v3.qdbh qdbh3,v3.jgsj jgsj3,v3.fxbh fxbh3 from t_vms_passcar v3 where v3.qdbh=374 
  and v3.jgsj>to_date('2012-6-18 15:00:00','yyyy-mm-dd hh24:mi:ss')
  and v3.jgsj<to_date('2012-6-18 15:30:00','yyyy-mm-dd hh24:mi:ss')
  ) where RN3<2
)  p3
where p1.hphm1(+)=p2.hphm2 and p2.hphm2=p3.hphm3(+)
union
select * from
(
  select * from   
  (
  select ROW_NUMBER() OVER(PARTITION BY v1.hphm ORDER BY v1.jgsj DESC) RN1,v1.hphm hphm1,v1.qdbh qdbh1,v1.jgsj jgsj1,v1.fxbh fxbh1
  from t_vms_passcar v1 where v1.qdbh=374 
  and v1.jgsj>to_date('2012-6-18 14:35:03','yyyy-mm-dd hh24:mi:ss')
  and v1.jgsj<to_date('2012-6-18 14:59:03','yyyy-mm-dd hh24:mi:ss')  
  ) 
  where RN1<2
)  p1
,
(
  select * from   (
  select ROW_NUMBER() OVER(PARTITION BY v2.hphm ORDER BY v2.jgsj DESC) RN2,v2.hphm hphm2,v2.qdbh qdbh2,v2.jgsj jgsj2,v2.fxbh fxbh2 from t_vms_passcar v2 where v2.qdbh=375 
  and v2.jgsj>to_date('2012-6-18 14:35:03','yyyy-mm-dd hh24:mi:ss')
  and v2.jgsj<to_date('2012-6-18 14:59:03','yyyy-mm-dd hh24:mi:ss')
  ) where RN2<2
)  p2
,
(
  select * from   (
  select ROW_NUMBER() OVER(PARTITION BY v3.hphm ORDER BY v3.jgsj DESC) RN3,v3.hphm hphm3,v3.qdbh qdbh3,v3.jgsj jgsj3,v3.fxbh fxbh3 from t_vms_passcar v3 where v3.qdbh=374 
  and v3.jgsj>to_date('2012-6-18 15:00:00','yyyy-mm-dd hh24:mi:ss')
  and v3.jgsj<to_date('2012-6-18 15:30:00','yyyy-mm-dd hh24:mi:ss')
  ) where RN3<2
)  p3
where p1.hphm1(+)=p3.hphm3 and p2.hphm2(+)=p3.hphm3





例如:

现有3个表 字段一样  数据不一样
比如表结构是这样:
表一:
col1 clo2 clo3 clo4
1     a    b    c
2     d    e    f
4     g    h    i
表二:
col1 clo2 clo3 clo4
1     a    b    c
2     d    e    f
3     g    h    i
表三:  类似 表二
我想查询结果显示为:
col1 clo2 clo3 clo4    clo2_1 clo3_1 clo4_1  clo2_2 clo3_2 clo4_2  
1     a    b    c      a       b      c      .....类似
2     d    e    f      d       e      f      .....类似
3                      g    h    i           .....类似
4     g    h    i                            .....类似


请问Oracle    SQL   怎么解决?