这个sql怎么优化!

askgyliu

原帖由 anlinew 于 2008-5-12 09:16 发表

举个特例 select * from a,b where a.col=b.col
而a，b的col    的值全为1，这样的sql最好的join 方式应该就是 Cartesian join 了，而Cartesian应该算作merge的一种特例
个人意见

Cartesian product, but no "Cartesian Join". Cartesian product can be processed by all the three join methods.

honggazi

帮顶

askgyliu

Still not tested with different options? Theory is just theory, test is the only way to find out the right way for performance.

wangzhonnew

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)

myfriend2010

原帖由 askgyliu 于 2008-5-13 22:13 发表
Still not tested with different options? Theory is just theory, test is the only way to find out the right way for performance.

老大,这2天西安闹地震,这边都放假了!!

myfriend2010

原帖由 wangzhonnew 于 2008-5-14 06: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)

INCLUDE 那么多列,会不会巨占空间

wangzhonnew

possible, but given there's seems no other good way to go, maybe it's worth to try this appraoch~~~

anlinew

原帖由 wangzhonnew 于 2008-5-14 09:58 发表
possible, but given there's seems no other good way to go, maybe it's worth to try this appraoch~~~

有没有简单办法让它走hash join？

myfriend2010

原帖由 anlinew 于 2008-5-14 15:43 发表

有没有简单办法让它走hash join？

有啊!
http://www.itpub.net/thread-917931-1-1.html

就是费事!

myfriend2010

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