这个sql怎么优化!

myfriend2010

由于这几天地震,一直影响到现在才出结果!

sorry!

wangzhonnew

did you try to create the index i mentioned earlier?

myfriend2010

原帖由 wangzhonnew 于 2008-5-23 19:57 发表
did you try to create the index i mentioned earlier?

没明白你说了什么！！？？？

myfriend2010

可否说的详细点？？？

wangzhonnew

原帖由 wangzhonnew 于 2008-5-14 07:36 发表
i have another thoery that if creating an index with joint column and add all other required columns into INCLUDE clause, that may be able to help to avoid SORT operation
(the reason why creating index only on join column doesn't work, i beleive it may because optimizer thinks IXSCAN+FETCH is slower than SORT because of random access)

wangzhonnew

btw, you cannot use INCLUDE clause if the index is not unique index, then you'll need to add all those columns into indexes

myfriend2010

噢，这个阿！会不会太复杂了？
那么多列！几乎和表的列差不多了！为了这么一个查询这么搞，合适不？

wangzhonnew

that's the only way i can imagine to possibly avoid sort, maybe you can try it out during non-peak hour, and see if it's going to improve the performance.
if it doesn't take effect then simply drop it

askgyliu

原帖由 myfriend2010 于 2008-5-21 13:19 发表
to askgyliu:结果出来了
对于DMS:执行哪个测试
4561588 rows inserted in 1909.23 secs.
对于SMS:执行哪个测试
-- 4561588 rows inserted in 1997.53 secs.
中间测试的时候有停掉数据库的过程,所以数据缓冲区和sql解析的因素可以忽略!

可以详细地给些测试时的设定吗？

DMS TEMP Vs SMS TEMP?

DMS Permanent Vs SMS Permanent?

注意测试时每次换，并且只换一个条件。

askgyliu

原帖由 wangzhonnew 于 2008-5-25 13:35 发表
that's the only way i can imagine to possibly avoid sort, maybe you can try it out during non-peak hour, and see if it's going to improve the performance.
if it doesn't take effect then simply drop it

这个的做法好像难度蛮大的。我相信这个的原意是想用INDEX ONLY ACCESS来避免接下来的TABLE SCAN，并且纯粹INDEX的话是可能避免SORTING。只是实际操作难度不小。

But no harm to try.