2

I'm fairly new to working with databases and am trying to fully understand the purpose of a tablespace.

Lets say I have a system where a user will input a schedule of tasks that need to be performed. This schedule is stored in my database and sent to a process which will distribute the tasks. As tasks are completed, the results are sent to a separate process which will store the raw result data, correlate the results, store a correlation summary, and then send it out.

Would I keep all of this data in a single tablespace or would it make more sense to separate it into two tablespaces - one for the schedules and one that will store the raw results and correlated result summary?

In general, how related should tables/indexes be to be stored in the same tablespace?

3

Despite being answered 10 years ago, I don't think I could improve on Justin Cave's very applicable answer from this similar question on SO: Should an Oracle database have more than one tablespace for data storage?

My bias (and this is largely a matter of personal preference) is that if there is no compelling benefit to creating additional tablespaces, life is easier with a single tablespace.

  • There is no performance benefit to putting objects in different tablespaces. There is an old myth that separating tables and indexes would have some performance benefits. There is a potential benefit to spreading I/O over all available spindles, but that's better done with multiple data files in a single tablespace then with multiple tablespaces since Oracle does a round-robin allocation of extents in different data files assuming that your SAN isn't already doing something to even out I/O.
  • If you have large, static lookup/ history tables such that you could bring a new copy of the database to the client site by just bringing the smaller transactional tablespaces, that would be a reason to consider multiple tablespaces. But there are very few applications that have this sort of setup. If you'll have to bring all 200 GB, it doesn't matter how many tablespaces you have.
  • Along the same lines, if you have large read-only objects, putting them in a read-only tablespace can vastly decrease the time and space required for backups. Again, though, this isn't particularly common in practice outside of data warehouses.
  • If your application could run without some subset of objects, there may be a benefit to creating separate tablespaces so that you could take one offline and do a tablespace-level restore. Again though, few applications could run without a set of objects-- if you lose the index tablespace, for example, the application is likely just as dead as had you lost everything.
  • If you have a large number of empty or mostly empty tables and a number of very large tables, separate tablespaces with different extent allocation policies may be preferrable from a space utilization standpoint. This happens occasionally with packaged apps where any given installation is using a relatively small percentage of the available tables and you don't want each of the empty tables to have a relatively large extent assigned to it. With automatic extent management in a locally managed tablespace, this tends not to be a major concern, it may be more concerning if you want to use uniform extents.
  • If different objects have different priorities for disk performance, and you have different types of disk available, separate tablespaces can allow you to put different objects on different sets of disks. In a data warehouse, for example, you may want to put older data on slower, cheaper disk and newer data on more costly disk. This doesn't happen much with OLTP applications.

Unless your application falls into one of these special cases, the only benefit to having separate tablespaces is to appeal to a DBA's sense of organization. Personally, I'm more than happy to be able to avoid specifying a tablespace name every time I create an object or to spend cycles moving objects from the "wrong" tablespace when they inevitably get created in the default tablespace mistakenly. Personally, I'm not overly concerned if a few tens of MB of space are "wasted" when using locally managed tablespaces with automatic extent management over a hand-optimized set of tablespaces with different uniform extent sizes. On the other hand, good DBA's tend to be very concerned about things being organized "just so" so I'm not militantly opposed if a DBA wants to have separate index and data tablespaces just because that appeals to someone's sense of aesthetics.

  • That link contains strong arguments against Cave's preference. There are many benefits you would miss, but not being able to effectively use the transport tablespace feature would be a huge negative. I suppose Cave doesn't like partitioning as well. The world has moved on in the last ten years. Crazy that anyone can consider one tablespace for anything but a Mickey Mouse database. – sandman Jun 30 '17 at 6:57
  • @sandman The OP is specifically asking about 1) storing correlated results in a different tablespace & 2) storing the indexes in a different tablespace. The reasons are presumably for performance considerations. Therefore, Cave's answer is the most applicable since you certainly wouldn't be using transportable tablespaces in that case anyways. Although Cave doesn't mention transportable tablespaces, nearly all of the other exceptions with why one would consider multiple tablespaces are listed in Cave's answer, so I think it is still the best answer on the SO question as well as this question. – Kris Johnston Jun 30 '17 at 13:16
  • Yeah and in the future when his data grows he does want to reorg or split database and use transportable tablespace etc? I think this advice is irresponsible. More work for consultants I guess. – sandman Jul 3 '17 at 8:52
  • @sandman It doesn't really have anything to do with the amount of data. If you have lots of small segregated apps, then storing most of them in the same database probably makes sense, in which case, yes, you would probably find transportable tablespaces useful. On the other hand, if everything is interconnected; or if a segregated app is large enough, it probably belongs in its own database anyways (unless you're running on Exadata or similar hardware and aren't licensed for Multi-tenant), then transportable tablespaces will be of very limited use. I've never had a use case for them myself. – Kris Johnston Jul 3 '17 at 13:39
  • About 10 to 15 years ago, way before multi-tenancy, oracle advised that it was better to have one database for your organization. They proved that by running oracle.com off one database. We didn't quite agree, even at that time, but did have one large corporate database handling four major areas. Ten years later we had to use goldengate and transportable to split off one major area into its own database (due to performance issues related to legacy apps). Now we have the problem of splitting another and the reasons are more complex than I have the time or space to explain here. – sandman Jul 3 '17 at 14:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.