求助！！各位大侠，帮忙看看查询速度好慢

 #1使用道具  发表于 2011-7-5 23:18   
求助！！！使用leftjoin 查询 速度奇慢。请大侠赐教！
这个是SQL语句。A表达改50W数据，B表2W数据，C表50W数据。 速度好慢。在服务器上大概需要3个小时。请问大家如和优化。
select /*+parallel(ac,4)*/
count(1)
  from (select /*+parallel(cs,4)*/
         AB,
         ceil(avg(A6) * 100) / 100 Q1,
         ceil(avg(A7) * 100) / 100 Q2,
         ceil(avg(A8) * 100) / 100 Q3,
         ceil(avg(A9) * 100) / 100 Q4,
         ceil(avg(A10) * 100) / 100 Q5,
         ceil(avg(A11) * 100) / 100 Q6
          from (select /*+parallel(t,4) parallel(c,4) parallel(r,4)*/
                 r.time,
                 nvl(a_A1, b_A2) AB,
                 A6,
                 A7,
                 A8,
                 A9,
                 A10,
                 A11
                  from A t
                  left join B c on c.C5 =
                                            t.A1
                                        and c.C1 =
                                            t.A2
                                        and c.C2 =
                                            t.A3
                                        and c.C3 <=
                                            t.A4
                                        and c.C4 >=
                                            t.A5
                  left join C r on c.C5 =
                                            r.R1
                                        and c.C6 =
                                            r.R2
                                        and r.R3 <=
                                            c.C7
                                        and r.R4 >=
                                            c.C8
                 where ID = 1
                   and TIME >= TO_DATE(?, 'YYYY-MM-DD HH24:MI:SS')
                   and TIME <= TO_DATE(?, 'YYYY-MM-DD HH24:MI:SS')) cs
         where G> -100.0
         group by AB) ac请大侠们帮帮忙，如和能够优化这条语句！！！！让查询更速度起来！！！执行计划SELECT STATEMENT, GOAL = ALL_ROWS                耗费=12218        CPU 耗费=1630553048
SORT AGGREGATE                        
  PX COORDINATOR                        
   PX SEND QC (RANDOM)        对象名称=:TQ10004                
    SORT AGGREGATE                        
     VIEW                耗费=12218        CPU 耗费=1630553048
      HASH GROUP BY                耗费=12218        CPU 耗费=1630553048
       PX RECEIVE                耗费=12218        CPU 耗费=1630553048
        PX SEND HASH        对象名称=:TQ10003        耗费=12218        CPU 耗费=1630553048
         HASH GROUP BY                耗费=12218        CPU 耗费=1630553048
          HASH JOIN                耗费=12217        CPU 耗费=1624086615
           PX RECEIVE                耗费=6981        CPU 耗费=785740881
            PX SEND BROADCAST        对象名称=:TQ10002        耗费=6981        CPU 耗费=785740881
             HASH JOIN BUFFERED                耗费=6981        CPU 耗费=785740881
              PX RECEIVE                耗费=2123        CPU 耗费=525090333
               PX SEND HASH        对象名称=:TQ10000        耗费=2123        CPU 耗费=525090333
                PX BLOCK ITERATOR                耗费=2123        CPU 耗费=525090333
                 TABLE ACCESS FULL        对象名称=TB_SRC_MGW_TDM        耗费=2123        CPU 耗费=525090333
              PX RECEIVE                耗费=1613        CPU 耗费=119752000
               PX SEND HASH        对象名称=:TQ10001        耗费=1613        CPU 耗费=119752000
                PX BLOCK ITERATOR                耗费=1613        CPU 耗费=119752000
                 TABLE ACCESS FULL        对象名称=TB_SRC_CTX        耗费=1613        CPU 耗费=119752000
           PX BLOCK ITERATOR                耗费=5229        CPU 耗费=796600235
            TABLE ACCESS FULL        对象名称=TB_SRC_RTT        耗费=5229        CPU 耗费=796600235