100倍以上的性能提高

kerlion

我是拨号上去执行的，刚才有人回报，本地执行1秒都要不了，哈哈！

其实给定一个SQL和每个表的大小顺序，以及索引的情况，你应该知道怎么样是最优秀的explan.

richto

Hash Join does not necessary better than Nested Loop, all depends on your environment and number of records return.  There are no absoluted decision which is better than the other, you have to understand the operation first.  One thing for sure is that Nested Loop can return records faster(response time).  Hash Jion must full scan the smaller table first and build a Hash table in memory before it can return records.

chao_ping

我的感觉一般在oltp系统里面, 对于绝大多数情况还是使用nested loop比较合适.
除非有些查询例外,需要返回大量的数据, 这样nested loop反而会很慢.

stevenzhaoyi

I mean you look younger than before
But what is the meaning of "use_nl或use_hash" hash join? or hash table?

eager to learn!

kerlion

hash jion,

我选了个头像而也嘛。
哈哈，我只是讨厌别人叫我大叔，哈哈

biti_rainy

要坚信，没有绝对好的方式
都是根据不同情况而采取不同的策略
如果固定那种方式好，相信oracle早就固化了，还用你我在这里折腾？

use_nl或use_hash  ：  nested loop  /  hash
关键在于弄清楚哪种方式到底适合在什么样的情况下  

nested loop  :有索引，结果集小
merge :   ……


BTW:  对于有大量重复值的，如果采用hash是不是有不良影响？
说实在的，偶也没有真正仔细弄清楚过
抛出我的意见（砖头），望各位指正：
hash join 根据列值，按某种算法计算出列在内存中存储的位置，数据就存储在该处
当要查询或者做连接的时候，根据列值就能找着记录的位置，这样就免去了维护查找数据位置的代价，但如果重复值多的情况下，根据hash算法的特征（相同的值相当于是一个指针链表存储）
，是不是会对效率有问题

kerlion

nested loop:very  small row sets
hash jion: small row sets
sort merge: two tables a big

stevenzhaoyi

thx and take care~!

in nowadays, nested loop can be said the worst performance among these three options.
                      hash join can be very complex or very simple. My professor told us to use it with caution.
                      merge sort or other type of sorts should be better in general case

My understanding, byw,does oralce support other typed of sorts?such as quick sort,etc?

starfly

how long can one to attain this level,
i have used oracle for 3years,but still can't do such tuning job.

kerlion

oralce use sort, but didint specify what kind of sort methods they use.