Re: index with very high LIO
Date: Thu, 17 Nov 2011 14:06:44 -0600
Message-ID: <4EC56954.8030102_at_ardentperf.com>
Yeah, you're not the first one to point that out... I'm doing some tests now. FWIW it's 7 million out of over a billion rows that are all stuffed into a 400GB heap table with the worst possible clustering factor on the foreign key we're using to join. Also I haven't been able to get more than 20MB/s total throughput with parallel full scans, although single-block reads go at a somewhat decent rate of 5ms. But all the same, I'm checking out the hash join... likely it will turn out to be more efficient.
I should have checked this out more in-depth awhile ago... I got a little carried away with maths and mistakenly thought that it was totally unrealistic. This is the problem with ruling things out based on "calculations". There's little to verify your correctness. It's much easier to find your mistakes when you rule things out with *testing* instead... I should know this by now, for some reason I'm still making the occasional poor judgements in the heat of the moment. :)
-Jeremy
On 11/17/2011 01:24 PM, Greg Rahn wrote:
> Am I reading this right? 7 million rows joined using NESTED LOOPS
> join? Wouldn't a parallel hash join be better/faster?
>
> On Thu, Nov 17, 2011 at 3:34 AM, Jeremy Schneider
> <jeremy.schneider_at_ardentperf.com> wrote:
>> PS... 8k block size; segment of index in question is 100G. Query is
>> pulling 7 million rows from a join of two billion row tables...
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Nov 17 2011 - 14:06:44 CST