请教11G的新特性嵌套循环，谢谢

earth2mars

Yong Huang 发表于 2013-2-23 01:47
> 如何证明10G和11G一样也是有一个中间结果(含有全部的ROWID)？

I don't have a proof. I basically too ...

1. "If "中间结果做为驱动行源的", then T has to be 遍历 more than once. Isn't that going to have a much higher cost than 遍历 once?"
==================================================================================
怎么会遍历多次呢？不需要遍历啊，比如中间结果是 2个ROWIDs，那么只要 ROWID SCAN T两次就可以了啊
如果 T表 1000万行，遍历一次T表，到中间结果查询1000万次？想想也不合理啊！10G会这么做？我觉得是不可能的事情
11G难道不是中间结果的ROWIDs作为驱动行源的吗？
而且10G执行计划里的 TABLE ACCESS BY INDEX ROWID 也说明是了最后一步 对T表的访问是ROWID 扫描
这里问题的焦点在于，读一遍是怎样读一遍？全表扫描一遍？这个完全不可能的，
既然是 TABLE ACCESS BY INDEX ROWID，怎么理解读一遍呢？


2.  有一个问题是，那本书里提到的旧机制和新机制，并没有涉及到11G,因为出版那本书的时候，11G还没有发布
书上提到的新机制是10G的


3.  所以11G的所谓 VECTOR I/O到底是怎么回事？不理解
   我觉得OTN上我给出的链接的那两个执行计划，是没有差别的，除非11G的TABLE ACCESS BY INDEX ROWID 能实现 单次I/O多块读，并且10G是单次I/O单块读，那就有差别了

Yong Huang

> 不需要遍历啊，比如中间结果是 2个ROWIDs，那么只要 ROWID SCAN T两次就可以了啊

You're absolutely right! I forgot that the interim result is actually a set of ROWID's. (If it was regular row data, then it would scan the table T multiple times.) And you're right: we can think of the interim result as the driver. Note that Oracle probably thinks that because the ROWID's are pre-sorted, table T only needs to be scanned once, therefore Execs=1 on line 1 for the 10g plan. But interestingly, although the nature of the acccess to table T remains the same, the 11g plan accounting assigns 5 to Execs instead of 1.

> 11G的所谓 VECTOR I/O到底是怎么回事？

If the 11g "table access by index rowid" in the new NLJ uses vector I/O, that will be very interesting. I just tested with my small data set. I still see db file sequential read event for table T (using the new 11g plan). Maybe I need to check the underlying OS system call with e.g. strace on Linux and see readv() calls, instead of relying on the Oracle wait event.

earth2mars

Yong Huang 发表于 2013-2-23 13:06
> 不需要遍历啊，比如中间结果是 2个ROWIDs，那么只要 ROWID SCAN T两次就可以了啊

You're absolutely r ...

sql monitor 显示10G 的TABLE ACCESS BY INDEX ROWID 1次
而11G 5次，确实不理解

我想来想去都不明白11G比10G好在哪里，我觉得这两个执行计划是一样的
除非11G的 TABLE ACCES BY INDEX ROWID 是实现的多块读。

另外，11G对于每个块只会读一次，10G在Lewis那么书上所说，貌似每个数据块也只会读一次
我实在不清楚10G 和11g有什么区别了

VECTOR I/O 太玄乎了

Yong Huang

> sql monitor 显示10G 的TABLE ACCESS BY INDEX ROWID 1次
> 而11G 5次，确实不理解

Literally, we can read it like this. In 10g, table T is read once because after the first read, a pointer pointing to the row just read stays there. When the second read occurs, the pointer moves down by some row ID's to point to the second row. In 11g, each read is independent. There's no "memory" or "remembering" of the read position of the previous read. So 10g's Execs is 1 and 11g's is 5.

> VECTOR I/O 太玄乎了

When I have time, I'll test it and report back.

earth2mars

Yong Huang 发表于 2013-2-24 08:57
> sql monitor 显示10G 的TABLE ACCESS BY INDEX ROWID 1次
> 而11G 5次，确实不理解

Literally, we can read it like this. In 10g, table T is read once because after the first read, a pointer pointing to the row just read stays there. When the second read occurs, the pointer moves down by some row ID's to point to the second row. In 11g, each read is independent. There's no "memory" or "remembering" of the read position of the previous read. So 10g's Execs is 1 and 11g's is 5.
===============================================================
这个是你猜的吧，我也曾这么猜想过，但是我找不到任何官方文档说明，也没法用实验证明
如果是那样，10G是不是只有一次 I/O ? 而11G有5次 I/O ？
那11G的优越性如何体现？
我不清楚 Execs指的是否I/O的次数，如果是I/O的次数，那么应该10G快才对啊
如果是执行“TABLE ACCESS BY INDEX TOWID”的次数 也不对，因为11G执行了5次，10G执行了1次