Automatic storage, SMS, and DMS table spaces offer different capabilities that can be advantageous in different circumstances.
Automatic storage table spaces | SMS table spaces | DMS table spaces | |
---|---|---|---|
How they are created | Created using the MANAGED BY AUTOMATIC STORAGE clause of the CREATE TABLESPACE statement, or by omitting the MANAGED BY clause entirely. If the automatic storage was enabled when the database was created, the default for any table space you create is to create it as an automatic storage table space unless you specify otherwise. | Created using the MANAGED BY SYSTEM clause of the CREATE TABLESPACE statement | Created using the MANAGED BY DATABASE clause of the CREATE TABLESPACE statement |
Initial container definition and location | You do not provide a list of containers when creating an automatic storage table space. Instead, the database manager automatically creates containers on all of the storage paths associated with the database. Data is striped evenly across all containers so that the storage paths are used equally. | Requires that containers be defined as a directory name. |
|
Initial allocation of space |
|
Done as needed. Because the file system controls the allocation of storage, there is less likelihood that pages will be contiguous, which could have an impact on the performance of some types of queries. | Done when table space created.
|
Changes to table space containers |
|
No changes once created, other than to add containers for new data partitions as they are added. |
|
Handling of demands for increased storage |
|
Containers will grow until they reach the capacity imposed by the file system. The table space is considered to be full when any one container reaches its maximum capacity. | Containers can be extended beyond the initially-allocated size manually or automatically (if auto-resize is enabled) up to constraints imposed by file system. |
Ability to place different types of objects in different table spaces | Tables, storage for related large objects (LOBs) and indexes can each reside in separate table spaces. | For partitioned tables only, indexes and index partitions can reside in a table space separate from the one containing table data. | Tables, storage for related large objects (LOBs) and indexes can each reside in separate table spaces. |
Ongoing maintenance requirements |
|
None |
|
Use of restore to redefine containers | You cannot use a redirected restore operation to redefine the containers associated with the table space because the database manager manages space. During the restore, the data and containers might be redefined. For redefinition conditions, see Rebalancing during RESTORE of automatic storage database. | You can use a redirected restore operation to redefine the containers associated with the table space | You can use a redirected restore operation to redefine the containers associated with the table space |
Performance | Similar to DMS | Generally slower than DMS and automatic storage, especially for larger tables. | Generally superior to SMS |