超级超级慢的一个查询语句，待优化，在此谢谢高手指导

Question

SELECT ROWNUM no1, t.*
  FROM (
  SELECT main.f_id, main.f_information, main.f_problemsourcename,
               NVL (main.f_superviserenumber, 0) AS f_superviserenumber,
               main.f_problemtype, main.f_code, main.f_moveend, main.f_moveendname,
               main.f_problemlevelname,
               (CASE
                   WHEN main.f_problemtype = 0
                      THEN '部件'
                   ELSE '事件'
                END
               ) f_problemtypename,
               (   (CASE
                       WHEN main.f_problemtype = 0
                          THEN '部件'
                       ELSE '事件'
                    END)
                || ' '
                || main.f_classbigname
                || ' '
                || main.f_classsmallname
               ) AS problemtypename,
               (main.f_streetname || ' ' || main.f_communityname || ' '
                || main.f_locatedetail
               ) AS loacatedetail,
               main.f_classbigname, main.f_classsmallname, main.f_curoperationdate,
               main.f_locatedetail, main.f_cursignuserid, main.f_oughtlastfeelbacktime,
               main.f_verifyfeelbackcitymngpeople, main.f_problemlevelid,
               main.f_dispatchuserid,
               ROUND (  (  NVL (main.f_timelimit, 0) * 60
                         + NVL (main.f_timeslowminute, 0)
                         + NVL (main.f_timelapseminute, 0)
                        )
                      / 60,
                      2
                     ) timelimit,
                tempTb.f_timelimit,
                tempTb.Lastusetime2 usetime,
               '督办' AS op, main.f_problemdiscription, main.f_dispatchdate, main.f_unitname,
               main.f_unitid, main.f_communityid, main.f_streetid, main.f_classsmallid,
               main.f_classbigid, main.f_problemsourceid
    from oprt_s_main main,
(select oprt.F_ID, oprt.f_dispatchdate,
 gettransacttime (oprt.f_dispatchdate,TO_DATE ('2009-5-5 17:14:02','YYYY-MM-DD HH24:MI:SS')) Beginusetime,
  gettransacttime (oprt.f_dispatchdate,GetNormalFeelBackTime(TO_DATE ('2009-5-5 17:14:02','YYYY-MM-DD HH24:MI:SS'),120)) Lastusetime2,
 ROUND (  (  NVL (oprt.f_timelimit, 0) * 60
                         + NVL (oprt.f_timeslowminute, 0)
                         + NVL (oprt.f_timelapseminute, 0)
                        )
                      / 60,
                      2
                     ) timelimit2,
               (  NVL (oprt.f_timelimit, 0) * 60
                + NVL (oprt.f_timeslowminute, 0)
                + NVL (oprt.f_timelapseminute, 0)
               ) f_timelimit from oprt_s_main oprt where oprt.f_moveend IN (SELECT f_code
                               FROM sys_s_state sta
                              WHERE sta.f_node IN (6))
  ) tempTb
  where tempTb.f_timelimit between tempTb.Beginusetime and  tempTb.Lastusetime2
  and main.F_ID = tempTb.F_ID
  
union all
  
  select * from 
  (SELECT f_id, f_information, f_problemsourcename,
               NVL (f_superviserenumber, 0) AS f_superviserenumber,
               f_problemtype, f_code, f_moveend, f_moveendname,
               f_problemlevelname,
               (CASE
                   WHEN f_problemtype = 0
                      THEN '部件'
                   ELSE '事件'
                END
               ) f_problemtypename,
               (   (CASE
                       WHEN f_problemtype = 0
                          THEN '部件'
                       ELSE '事件'
                    END)
                || ' '
                || f_classbigname
                || ' '
                || f_classsmallname
               ) AS problemtypename,
               (f_streetname || ' ' || f_communityname || ' '
                || f_locatedetail
               ) AS loacatedetail,
               f_classbigname, f_classsmallname, f_curoperationdate,
               f_locatedetail, f_cursignuserid, f_oughtlastfeelbacktime,
               f_verifyfeelbackcitymngpeople, f_problemlevelid,
               f_dispatchuserid,
               ROUND (  (  NVL (f_timelimit, 0) * 60
                         + NVL (f_timeslowminute, 0)
                         + NVL (f_timelapseminute, 0)
                        )
                      / 60,
                      2
                     ) timelimit,
               (  NVL (f_timelimit, 0) * 60
                + NVL (f_timeslowminute, 0)
                + NVL (f_timelapseminute, 0)
               ) f_timelimit,
               gettransacttime (f_dispatchdate,
                                TO_DATE ('2009-5-5 13:53:02',
                                         'YYYY-MM-DD HH24:MI:SS'
                                        )
                               ) usetime,
               '督办' AS op, f_problemdiscription, f_dispatchdate, f_unitname,
               f_unitid, f_communityid, f_streetid, f_classsmallid,
               f_classbigid, f_problemsourceid
          FROM oprt_s_main
         WHERE f_moveend IN (SELECT f_code
                               FROM sys_s_state
                              WHERE f_node IN (6)))
  
  where USETIME > f_timelimit   
  ) t  
  
  
  
  执行大约3分钟左右

Answer 1

表结构，索引，执行计划，数据量
要有这些数据才能分析啊
单纯一个SQL语句没办法优化的，最多看看是否语法上有待优化

Answer 2

1：
 把SELECT f_code FROM sys_s_state WHERE f_node IN (6)改成SELECT f_code FROM sys_s_state WHERE f_node=62：
 把这个函数gettransacttime获取值的项去掉试试看，需要多长时间。3：建议把*换成具体的列。4：把in换成exists试试看(如果可以的话)。

Answer 3

请参考：
http://blog.csdn.net/pathuang68/archive/2009/04/16/4084116.aspx

Answer 4

我已经照着oraclelogan 的方法做了，也是相同的效果，继续等待各位给出更好的建议

Answer 5

你得用explain plan找出慢的地方。可以走索引的就走索引 /*+ INDEX (amp_zb idx_amp_zb)*/

Answer 6

要将表的情况和join的情况什么的说清楚才知道怎么join效率最好