在oracal中这个包含子查询的语句耗时180秒，请问如何优化这个查询语句？

Question

select distinct a.id,a.building_name,f.record_person ,d.address,d.begin_date,
       (select count(*) from t_supervise_job_assign where  building_id=a.id) status, 
       a.create_user  
 from t_building a , t_building_info d,t_monitor_plan e,t_monitor_record f 
 where  a.id=d.building_id  AND e.f1_id=a.id AND e.stream_id=f.f1_stream_id 
        AND a.modify_flag<>'D'  AND a.building_name like '%广东%' 
        order by status,d.begin_date  desc 
关键是这句（select count(*) from t_supervise_job_assign where  building_id=a.id），
其中a.id是前面已经查出来的distinct a.id 
请问怎样优化得到高效的查询？

Answer 1

这样会快一点：
select distinct a.id,a.building_name,f.record_person ,d.address,d.begin_date,
       status, 
       a.create_user  
 from t_building a , t_building_info d,t_monitor_plan e,t_monitor_record f,
      (select id,count(*) status from t_supervise_job_assign group by id) g
 where  a.id=d.building_id  AND e.f1_id=a.id AND e.stream_id=f.f1_stream_id 
        AND a.modify_flag<>'D'  AND a.building_name like '%广东%' and
        a.id = g.id        
 order by status,d.begin_date  desc 

Answer 2

也可以创建一个临时表，优化查询。
create global temporary t_name as
select id,count(*) status from t_supervise_job_assign group by id
on commit preserve rows;
select distinct a.id,a.building_name,f.record_person ,d.address,d.begin_date,
       t_name.status, 
       a.create_user  
 from t_building a , t_building_info d,t_monitor_plan e,t_monitor_record f,
 where  a.id=d.building_id  AND e.f1_id=a.id AND e.stream_id=f.f1_stream_id 
        AND a.modify_flag<>'D'  AND a.building_name like '%广东%' and
        a.id = g.id        
 order by status,d.begin_date  desc

Answer 3

select count(*) from t_supervise_job_assign where  building_id=a.id 这句话的意思是从外层查询出来的包含以下列的distinct a.id,a.building_name,f.record_person ,d.address,d.begin_date, a.create_user的查询结果表根据building_id=a.id条件从表t_supervise_job_assign查询出count(*),所以a.id相当于一个数值字符串'a.id',
上面的兄弟的查询结果就查不出这样的数据了，希望大家能明白我的意思。

Answer 4

是select count(*) from t_supervise_job_assign where  building_id=a.id这句慢吗？
t_supervise_job_assign表有没有building_id字段的索引？