Re: 32K block size tablespace for indexes
- From: Guillermo Alan Bort <cicciuxdba@xxxxxxxxx>
- To: niall.litchfield@xxxxxxxxx
- Date: Thu, 29 Jan 2009 07:05:07 -0200
I can certainly see why someone would do so.
I think it all comes down to the access path.
For instance: if you usually perform a fast full index scan, then
having the index in a large block tablespace does make sense. Tha same
is true for large index range scans. However, there is an
administration overhead to having multiple block size tablespace
(namely the appropriate cache area).
On the other hand, I/O Subsystems are a lot faster than 5 years ago,
and Oracle 10g manages buffer rather well.
Another result of having indexes in a different block size tablespace
is that they would have their own cache area, so it's highly unlikely
that index blocks will be aged out due to normal database operation.
However, defining a keep cache would have the same effect.
In the end, I've only seen truly justifiable situations where a
different block size is required when dealing with transportable
tablespaces. In other situations is just seems like a waste of good
resources... and if index blocks are hot, simply pin the index to the
cache or define a keep cache for it.
Alan Bort
Oracle Certified Associate
On Thu, Jan 29, 2009 at 5:51 AM, Niall Litchfield
<niall.litchfield@xxxxxxxxx> wrote:
> most likely because they read some advice on it. Note I have an opinion on
> this, but this might help with the background
>
> Pros
> ====
> http://searchoracle.techtarget.com/tip/0,289483,sid41_gci1008028_mem1,00.html
> http://www.ixora.com.au/tips/block_size.htm
>
> Against
> =======
> http://richardfoote.wordpress.com/2008/03/31/larger-block-index-tablespace-and-small-index-scans-performance-improvement-let-down/
> (including
> related papers and blogs)
>
> There's been a whole host of, somewhat heated, discussions on this lately on
> otn.
>
> cheers
>
> Niall
>
>
> On Thu, Jan 29, 2009 at 7:03 AM, hrishy <hrishys@xxxxxxxxxxx> wrote:
>>
>> Hi
>>
>> I have inherited a database where the indexes alone are in the 32k block
>> tablespaces.
>>
>> Now without any documentation or anything for a hint i am just wundering
>> why did the original designers design it like that?
>>
>> What are the pro's and cons of such a approach ?
>>
>> regards
>> Hrishy
>>
>>
>>
>> --
>> //www.freelists.org/webpage/oracle-l
>>
>>
>
>
>
> --
> Niall Litchfield
> Oracle DBA
> http://www.orawin.info
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
