一条SQL困扰我很久了，大大们帮我分析下，怎么优化！

显示全部楼层 · 2007-4-25 04:02:08

select *from (

select a.start_id startStation,

a.start_latstartLat,

a.start_lonstartLon,

c.end_latendLat,

c.end_lonendLon,

a.end_idoneStationEnd,

a.line_idoneline,

b.start_idtwoStationStart ,

b.end_idtwoStationEnd,

b.line_id twoline ,

c.start_id threeStationStart,

c.end_id threeEndStation,

c.line_idthreeline,

2 transferType,

distance(a.end_lat,a.end_lon,b.start_lat,b.start_lon) + distance(b.end_lat,b.end_lon,c.start_lat,c.start_lon) walkDistance,

ROWNUM RN

from ((select * from bustransferplan where start_id = #start.stationId#) a inner join

bustransferplan b on

a.end_heap = b.start_heap and a.line_id != b.line_id

inner join

(select * from bustransferplan where end_id = #end.stationId#) c on

b.end_heap = c.start_heap and b.line_id != c.line_id)

order by walkDistance
) whererownum <= 5
bustransferplan 数据大约在25W,这条SQL就是自关联,现在我建立索引INDEX（end_heap ,start_heap）， INDEX(start_id)，INDEX(end_id) ，现在结果出来基本要5秒钟左右哦，我一直觉得order by walkDistance有问题，数据一超过1000以上就很慢，这种动态排序不可避免，索引也没法建立（也可能是我无知，HOHO,非DBA）,大家帮我看下有无什么方式去优化下这条SQL，这条SQL对我们现在系统十分重要，THINKS!

千问 · 2007-4-25 04:02:08

order by和rownum结合的有问题，语句不对

千问 · 2007-4-25 04:02:08

whzhaha,
Version of Oracle?
Can you run this?
alter session set "_rowsource_execution_statistics" = true;
--your SQL here
select * from table(dbms_xplan.display_cursor('', '', 'last allstats'));
Post the result of the above query with fixed-width font.
If performance degrades above 1000 rows in the final result, it may help to increase sqlplus arraysize(called by other names in other environments): set arraysize 2000.
dingjun123,
Can you specify where "order by和rownum结合的有问题"? I'm not that familiar with ANSI joins, but I don't see anything wrong.
Yong Huang
[ 本帖最后由 Yong Huang 于 2010-10-18 09:39 编辑 ]

千问 · 2007-4-25 04:02:08

谢谢，各位。
数据库 oracle linux32版本 10.2.0.1g10201_database_linux32.zip
我的PL/sql执行计划是：
SELECT STATEMENT, GOAL = ALL_ROWS
792
5
990
COUNT STOPKEY

VIEW
ROOT
792
3094
612612
SORT ORDER BY STOPKEY
792
3094
439348
COUNT

HASH JOIN
690
3094
439348
TABLE ACCESS BY INDEX ROWID
ROOT
BUSTRANSFERPLAN
8
67
3082
INDEX RANGE SCAN
ROOT
BUSTRANSFERPLAN_INDEX_ENDID
3
67

HASH JOIN
681
27949
2683104
TABLE ACCESS BY INDEX ROWID
ROOT
BUSTRANSFERPLAN
14
65
2990
INDEX RANGE SCAN
ROOT
BUSTRANSFERPLAN_INDEX_STARTID
3
65

TABLE ACCESS FULL
ROOT
BUSTRANSFERPLAN
663
260435
13021750
因为小公司，现在没DBA数据库安装，配置，系统架构都是我自己弄，数据一些配置可能也不是很合理，我感觉order by walkDistance
比较慢，不过SQL本身计算也比较大，也不知道如何优化。
按照Yong Huang 的执行计划结果我也贴下：

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID9m7787camwh4m, child number 0
begin :id := sys.dbms_transaction.local_transaction_id; end;
NOTE: cannot fetch plan for SQL_ID: 9m7787camwh4m, CHILD_NUMBER: 0
Please verify value of SQL_ID and CHILD_NUMBER;
It could also be that the plan is no longer in cursor cache (check v$sql_p

8 rows selected
ms看不到计划
我之所以怀疑order by walkDistance有问题是因为改成下面的SQL就很快没一点性能问题，以排序取前几条就挂了，很慢很慢，难道是硬盘排序
select a.start_id startStation,
a.start_latstartLat,
a.start_lonstartLon,
c.end_latendLat,
c.end_lonendLon,
a.end_idoneStationEnd,
a.line_idoneline,
b.start_idtwoStationStart ,
b.end_idtwoStationEnd,
b.line_id twoline ,
c.start_id threeStationStart,
c.end_id threeEndStation,
c.line_idthreeline,
2 transferType,
distance(a.end_lat,a.end_lon,b.start_lat,b.start_lon) + distance(b.end_lat,b.end_lon,c.start_lat,c.start_lon) walkDistance,
ROWNUM RN
from ((select * from bustransferplan where start_id = 1) a inner join

bustransferplan b on

a.end_heap = b.start_heap and a.line_id != b.line_id

inner join

(select * from bustransferplan where end_id = 16) c on

b.end_heap = c.start_heap and b.line_id != c.line_id)
whererownum <= 5order by walkDistance
[ 本帖最后由 whzhaha 于 2010-10-18 09:34 编辑 ]

千问 · 2007-4-25 04:02:08

Yong Huang ，i'm wrong
his sql in subquery is ' ROWNUM RN',but outer iswhererownum <= 5,right
but last sql: whererownum <= 5order by walkDistance
是有问题的，如果walkDistance有索引，并且走了这个索引，那么是可以的，否则会乱序
[ 本帖最后由 dingjun123 于 2010-10-18 10:12 编辑 ]

千问 · 2007-4-25 04:02:08

原帖由 dingjun123 于 2010-10-18 10:05 发表
Yong Huang ，i'm wrong
his sql in subquery is ' ROWNUM RN',but outer iswhererownum
这个错的我是知道的order by walkDistance 就是没意义的，我的意思要取前几条，第一个SQL为什么那么慢，而随机取5条那么快，排序上怎么提升下性能，像这种walkDistance复杂计算也能建立索引？我想在想是不是ORACLE参数需要修改下，争取在内存排序是不是快些，但不知道怎么弄

千问 · 2007-4-25 04:02:08

rownum这种查询，order by要走索引消除排序效率才好，否则会很慢
排序还是要在那个join里排序的，你尽量让join走索引消除这种排序，如果是高版本还可以用分析函数实现，已经有优化了，性能也不错
看那个计划先sqlplus里set serveroutput off

千问 · 2007-4-25 04:02:08

distance??这是你定义的函数？？？

千问 · 2007-4-25 04:02:08

谢谢。rownum这种查询，order by要走索引消除排序效率才好，否则会很慢这个我知道，现在的问题是walkDistance是自关联复杂计算，我不知道怎么建索引啊,MS不能吧

千问 · 2007-4-25 04:02:08

原帖由 dingjun123 于 2010-10-18 10:19 发表
distance??这是你定义的函数？？？
是的，回复很及时啊，呵呵，要是简单计算那就没的说早就建索引了，我现在想肯定是硬盘排序，不然那么慢