Re: What is a TABLESPACE?

continuing from what you wrote ...

And in Oracle tablespaces almost always are used to break up tables and indexes and place them onto separate physical drives to improve performance. Beyond that my impression is that most DBAs (myself included) use a different tablespace for tables of different extent sizes to remove the problem of tablespace fragmentation. So, for example, I might have one tablespace for small tables where the extent size is 512K and another for tables of 5M and another for 500M.

Daniel A. Morgan

Blair Kenneth Adamache wrote:

> A tablespace can hold one or more tables, and indexes and/or the long fields
> (CLOBs, BLOBs, LONG VARCHARs) for a table. It maps the logical data
> (tabular view) to the physical storage devices (files or containers on a
> file system).
>
> By default, tables you create are created in the tablespace USERSPACE1. If
> you want to have more control of where your data is placed, you have to
> created additional table spaces.
>
> On DB2 for OS/390, some DBAs like to put each table in its own tablespace.
> On DB2 on UNIX, OS/2 and Windows, the common practice is to put all related
> tables (those with referential constraints against each other) in the same
> tablespace.
>
> Combined with the CREATE BUFFERPOOL command, the CREATE TABLESPACE command
> gives you a lot of control of your data, including:
> - whether the maximum row width is 4K, 8K, 16K or 32K
> - the maximum number of columns in a table
> - whether the table data is stored on raw devices or on the file system
> - the ability to restore a tablespace from a backup image rather than a full
> database backup image
> - the ability to give a table a better chance of being cached in memory by
> assigning it to its own bufferpool
>
> Wyvette Banks wrote:
>
> > Hi,
> >
> > I am a novice in the Database world. What is a TABLESPACE, and when do I
> > use it?
> >
> > I was creating a Table in DB2 and it gave me two default choices for a
> > TABLESPACE name:
> > 1. None
> > 2. USERSPACE1
> > What is happening if I choose None or if I choose USERSPACE1? I noticed
> > this in Oracle as well, so image this is a DBMS thing.
> >
> > Gil
Received on Sat Mar 24 2001 - 09:11:01 CST