There is no performance improvement through separating tables and indexes.
A good basic starting point is to arrange objects according to their size. You probably want something like three tablespaces for all your tables and indexes as a starting point ...
i) SMALL, uniform extent size 32kb
ii) MEDIUM, uniform extent size 1Mb
iii) LARGE, uniform extent size 32Mb
You can spread the data files for these over different arrays/controllers, or create one set of tablespaces for each array.
a tablespace for small objects with a uniform extent size somewhere between 16 and 64Kb, then another tablespace for lerger objects with a uniform extent size of 16 or 32-times the size of the small-object TX.
Originally posted by slimdave
I don't believe there to be any performance of maintenance banefit from separating indexes and tables into different tablespaces -- look at asktom.oracle.com or the google forums for many discussions on this.
I've been following advice and putting objects in tablespaces based purely on their size for quite a while now, and it works great. Maybe a dedicated TS for a particular table
If you see no benefit to seperating the index and table, then why are you splitting tables into different tablespaces... managability?
What I can gather there is no performance advantage of seperation, just a managability issue.
These may help..
Last edited by grjohnson; 12-09-2002 at 01:49 AM.
OCP 8i, 9i DBA
Click Here to Expand Forum to Full Width