SELECT * FROM 
(
SELECT A.PDATE,A.FLIGHT_NO,A.CARRIER,A.ARR_AIRPORT,A.DEPT_AIRPORT,A.Delay_Time,A.APT_Delay_Time,A.REMARK, ROWNUM RN 
FROM (SELECT *
 from T_UNUSUATest C
INNER JOIN THIS_TABLE1  B ON C.LEG_ID=B.LEG_ID
) A 
WHERE ROWNUM <=10  AND PDATE between '2011-07-25' AND '2011-07-28'
AND FLIGHT_NO_DISP like '%U%' AND ARR_AIRPORT='PVG' Order By PDATE
)
WHERE RN >= 1

SELECT *
  FROM (SELECT A.PDATE,
               A.FLIGHT_NO,
               A.CARRIER,
               A.ARR_AIRPORT,
               A.DEPT_AIRPORT,
               A.Delay_Time,
               A.APT_Delay_Time,
               A.REMARK,
               ROWNUM RN
          FROM (SELECT *
                  from T_UNUSUATest C
                 INNER JOIN THIS_TABLE1 B ON C.LEG_ID = B.LEG_ID) A
         WHERE ROWNUM <= 10
           AND PDATE between '2011-07-25' AND '2011-07-28'
           AND FLIGHT_NO_DISP like '%U%'
           AND ARR_AIRPORT = 'PVG'
         Order By PDATE)
 WHERE RN >= 1
没啥子优化的了

------解决方案--------------------

SQL code

--粗略的估计你的执行计划，按照下面的改法试试
SELECT *
  FROM (SELECT A.PDATE,
               A.FLIGHT_NO,
               A.CARRIER,
               A.ARR_AIRPORT,
               A.DEPT_AIRPORT,
               A.DELAY_TIME,
               A.APT_DELAY_TIME,
               A.REMARK,
               ROWNUM RN
          FROM (SELECT *
                  FROM T_UNUSUATEST C, THIS_TABLE1 B
                 WHERE ARR_AIRPORT = 'PVG'
                   AND C.LEG_ID = B.LEG_ID
                   AND PDATE BETWEEN '2011-07-25' AND '2011-07-28') A
         WHERE ROWNUM <= 10 ISP LIKE '%U%'
         ORDER BY PDATE)
 WHERE RN >= 1

------解决方案--------------------

------------------------------
给 PDATE 建立index
SELECT *
 FROM (SELECT A.PDATE,
              A.FLIGHT_NO,
              A.CARRIER,
              A.ARR_AIRPORT,
              A.DEPT_AIRPORT,
              A.DELAY_TIME,
              A.APT_DELAY_TIME,
              A.REMARK,
              ROWNUM RN
         FROM (SELECT *
                 FROM T_UNUSUATEST C, THIS_TABLE1 B
                WHERE ARR_AIRPORT = 'PVG'
                  AND C.LEG_ID = B.LEG_ID
                  AND PDATE BETWEEN '2011-07-25' AND '2011-07-28'
                  AND ISP LIKE '%U%'
                ORDER BY PDATE) A
        WHERE ROWNUM <= 10  
        )
  WHERE RN >= 1
--------------------------------------------



------解决方案--------------------
贴出你的执行计划吧，没有执行计划的话，先按照我写的那个方法试试看。

------解决方案--------------------
探讨


贴出你的执行计划吧，没有执行计划的话，先按照我写的那个方法试试看。