- 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 CINNER 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如何优化？ 如题……
------解决方案--------------------
就一个SQL，没有其它信息，空谈优化？！
------解决方案--------------------
- 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 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
--------------------------------------------
------解决方案--------------------
贴出你的执行计划吧，没有执行计划的话，先按照我写的那个方法试试看。
------解决方案--------------------