Re: Myth revisited ...

Noons wrote:
>
> There was a thread on Oracle-L about this a few weeks
> ago. Funny how these things come around.
>

Guess I'll have to search for it. Thanks for the lead.  

> The consensus seemed to be you can get a measure
> of better indexing (less b-tree levels) if you put
> the index in a tablespace with a large block size.
> Therefore reducing I/O even more for the indexes.
>
> I can't remember if there were any caveats on key sizes
> and complexity. In any case, something I'd consider
> only after a lot of experimentation and testing.
>

I agree that this entire case is subject to a lot of skull sweat and may be useless except in extreme cases.

Understading the caveats would be mandatory. I suspect that there would be a mix of tablespace block sizes required. Gut feeling says the optimum mix would likely be where the number of levels & leaves per block are roughly similar regardless of size/complexity.  

> As for tables in tablespaces of larger block sizes,
> I can see an advantage when dealing with LOBs: you get
> (hopefully) more "in-lined" rows. It also may be
> advantageous for IOTs.
>

I also see advantages for inline collections and some complex object types.

IOT makes sense in the same way as plain index.  

> Again: very hard to say it will work everytime.
> Highly dependent on workloads, blah-blah-blah.

Of course. I also expect certain combinations will provide a negative impact.

Also am interested whether this is a 'bag of tricks' thing or whether the myth-buster statement holds for the mixed block size environment. Received on Sun Nov 16 2003 - 09:23:48 CST