这个SQL如何优化？

[guohouchang]

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如何优化？ 如题……

[tangren]

就一个SQL，没有其它信息，空谈优化？！

[秋雨飘落]

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
没啥子优化的了

[BenChiM888]

--粗略的估计你的执行计划，按照下面的改法试试
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

[flalscjf]

------------------------------------------------------------------
给 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
--------------------------------------------------------------------------------

[njlywy]

你要优化到什么程度呢？？

[BenChiM888]

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

[hanks_gao]

[Quote=引用 6 楼 benchim888 的回复:]

贴出你的执行计划吧，没有执行计划的话，先按照我写的那个方法试试看。
[/Quote]
先確保LEG_ID，PDATE，ARR_AIRPORT 上有索引！
然後再看一下執行計畫！