Re: Separating Tables and Indexes

Niall Litchfield wrote:
> fitzjarrell_at_cox.net wrote:
> > I separate them for management reasons; I can create tablespaces with a
> > smaller extent size for the index objects than I would need for the
> > table objects (as usually indexes require less space than tables).
> > Again, this is more a personal preference than a hard-and-fast rule.
>
> I understand, and agree with, the idea that you might separate objects
> by appropriate extent size (though my criteria would be growth patterns
> and not space usage). I don't see how that translates into a
> recommendation to separate indexes from tables because indexes take
> less space than tables. They will take less space than the table with
> which they are associated (nearly always and ignoring lobs), but I'm
> willing to bet that an index on orderid for amazon.com takes more space
> than an index on the ship_country lookup table in the same database :)
>
> Niall Litchfield
> Oracle DBA
> http://www.orawin.info/services/

You misinterpret, I think, my post, as my personal reasons have little bearing on the topic. My contention is for ease of management, and the example I posted prior to that statement is my driving argument: should an index or two or three corrupt the tablespace (however rare that may be) the only loss would be the indexes, not the table data. And indexes are far easier to recreate than the table data they index. That I also choose to segregate according to growth (and that *is* my criteria, not simply the basic size of the object) was an aside from one who does segregate table and index objects.

I hope I have now made that clear.

David Fitzjarell Received on Tue Aug 01 2006 - 10:19:10 CDT