Thread: Advantage of more than 1 tablespace on 1 disk?
Hi, I've got a database with massive tables which fall into 2 categories: Tables which don't change often but get read a LOT, and tables which are heavily added to continuously and sometimes read. Would there be any advantage in moving the latter logging tables to a separate tablespace, bearing in mind it would still be on the same disk? Or have I kinda missed how tablespaces work? Thanks Thom
what you want in case of the table that gets read a lot, is partition + cluster.
For table that changes a lot, I don't really know myself either. It is usually trial and error type of scenario for me. (partitioning too, but that depends on what parts are updated and under what conditions, etc).
Hi Thom, table spaces are not in the first place related with addressing usage pattern of individual tables. They are a mechanism for putting up a *logical* layout of persistent storage. As such they are describing segments of persistence storage that will (or "might from the point of view of the schema designer") benefit from being mapped to physical disks independently. (e.g. separating table data and index data, or arrange for indexesused with hot queries to be accessible via different IO channels) For a given machine and disk configuration you may then decide what table space to be mapped to what physical disk. (Hopefully being aware that this will decrease throughput) For addressing your actual question you might have a look at indexing, clustering and/or clustering. Those deal with efficiencyof accessing individual tables. Rainer Thom Brown schrieb: > Hi, > > I've got a database with massive tables which fall into 2 categories: > Tables which don't change often but get read a LOT, and tables which > are heavily added to continuously and sometimes read. > > Would there be any advantage in moving the latter logging tables to a > separate tablespace, bearing in mind it would still be on the same > disk? Or have I kinda missed how tablespaces work? > > Thanks > > Thom >