Re: does oracle us both block and row level nested loop joins?

From: Tanel Põder <tanel.poder.003_at_mail.ee>
Date: Fri, 30 Jul 2004 19:17:27 +0300
Message-ID: <07ff01c47650$b5542b70$36ae9fd9@porgand>

> You see from consistent gets that all of the blocks in T2 are opened 50
> times (total LIOs = the amount of rows in T1 * blocks in T2 + some
> overhead).

A correction: Each block in in T2 is opened 50*rows in given block times, in other words a consistent get is done for each combination of row matching between both row sources + there is some overhead. This is very ineffective, even with tiny tables like in the example...

(Oracle version 9.2.0.4 W2k)

Tanel.

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Fri Jul 30 2004 - 11:22:46 CDT