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如何优化? 如题……
(
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如何优化? 如题……
解决方案 »
- sql2005的int在oracle里
- 关于oracle存储过程全表扫描处理百万数据的速度问题
- Oracle ORA-01461 :仅可以为插入LONG列的LONG值赋值
- 往oracle9.2插入数据时发生ora-000600 16622错误,请问怎么解决?
- 如何使用OUT参数查看下面几个题目结果
- oracle递归,只显示up_id=根节点的问题
- 請問如何設置table使之在dba studio 看不到但是通過sql/plus可以看到
- 请问什么是向后滚,!! ,,,,,,,
- 准备学oracle,安装时出现大量的用户名和密码,感觉好乱,100分求救,帮我理清一下我列的问题,杀出一条血路,谢谢!
- 谁能告诉偶oracle里怎么可以修改表里number数据类型的小数精度。
- ORA-03135: connection lost contact
- Oracle分页存储过程的疑问
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, 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
--------------------------------------------------------------------------------
然後再看一下執行計畫!