Re: nlj batching

From: Andrew Kerber <andrew.kerber_at_gmail.com>
Date: Mon, 11 Oct 2010 07:54:10 -0500
Message-ID: <AANLkTimDD6d7MMM4vVYP0CWMAQuaWt9BXXnJ_TN5CcaE_at_mail.gmail.com>

I would think this is going to be a highly table size/hardware/horsepower dependent operation. All you can really do is put the same query on two different database that are different only in Oracle version and see what happens.

On Sun, Oct 10, 2010 at 10:35 PM, joshuasingham <joshuasingham_at_gmail.com>wrote:

> Hi Rafu,
>
> Thanks for the infomation but i already knew about the i/o prefetching
> part(vector i/o) just was looking if there was any other benefits other
> than that
>
>
>
>
> On Mon, Oct 11, 2010 at 2:12 AM, Timo Raitalaakso <rafu_at_iki.fi> wrote:
>
>>
>> Is there something else to say that is not in documentation?
>>
>> http://download.oracle.com/docs/cd/E11882_01/server.112/e16638/optimops.htm#BABFCIAI
>>
>> --
>> Rafu
>> http://rafudb.blogspot.com/
>>
>>
>> On 10.10.2010 20:39, joshuasingham wrote:
>>
>>> Hi all,
>>> I been going through some notes and wandering if anybody know the
>>> benefits of nlj batching for example in 10g i get this plan (as shown below)
>>> which then in 11g in converts into 2 nested loop join my question would be
>>> why will this be a good compared to 10g
>>>
>>> Oracle 10g
>>> ------------------------------------------------
>>> | Id | Operation | Name |
>>> ------------------------------------------------
>>> | 0 | SELECT STATEMENT | |
>>> | 1 | TABLE ACCESS BY INDEX ROWID | T2 |
>>> | 2 | NESTED LOOPS | |
>>> | 3 | TABLE ACCESS BY INDEX ROWID| T1 |
>>> |* 4 | INDEX RANGE SCAN | T1_N1 |
>>> |* 5 | INDEX RANGE SCAN | T2_N1 |
>>> ------------------------------------------------
>>>
>>> Oracle 11g
>>> ------------------------------------------------
>>> | Id | Operation | Name |
>>> ------------------------------------------------
>>> | 0 | SELECT STATEMENT | |
>>> | 1 | NESTED LOOPS | |
>>> | 2 | NESTED LOOPS | |
>>> | 3 | TABLE ACCESS BY INDEX ROWID| T1 |
>>> |* 4 | INDEX RANGE SCAN | T1_N1 |
>>> |* 5 | INDEX RANGE SCAN | T2_N1 |
>>> | 6 | TABLE ACCESS BY INDEX ROWID | T2 |
>>>
>>> thanks
>>>
>>>
>> --
>> http://www.freelists.org/webpage/oracle-l
>>
>>
>>
>

-- 
Andrew W. Kerber

'If at first you dont succeed, dont take up skydiving.'

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Oct 11 2010 - 07:54:10 CDT