What is a Segmented Tablespace?

A partitioned tablespace divides the storage space into multiple partitions, each functioning as a standalone segment of data. This structure is designed to optimize storage for large tables, allowing for parallel processing across partitions, which can improve performance for data retrieval and updates.

A partitioned tablespace divides a table into separate partitions based on a specified range of values (such as date ranges, customer regions, or other logical divisions). Each partition can grow independently, providing flexibility in managing large datasets.

Key Characteristics:

• Partitioned Storage: Divides a single table across multiple partitions.
• Efficient for Large Datasets: Optimized for tables with large data volumes that benefit from partitioned access.
• Parallel Data Access: Allows simultaneous access to different partitions, improving performance for large queries.
• Scalable Storage: Each partition can be stored on a different storage volume, enhancing storage flexibility.

Creating a Partitioned Tablespace

To create a partitioned tablespace, use the CREATE TABLESPACE statement with the NUMPARTS parameter to define the number of partitions and optionally DSSIZE for the maximum size of each partition. We may also use BUFFERPOOL and LOCKSIZE to control caching and locking at the tablespace level.

Syntax -

CREATE TABLESPACE tablespace_name
...
DSSIZE data_set_size
NUMPARTS number_of_partitions
...;

• DSSIZE: The maximum size of each data set within the tablespace, usually specified in gigabytes.
• NUMPARTS: Specifies the number of partitions for this tablespace.

Examples - Creating a Partitioned Tablespace

CREATE TABLESPACE EMPLTSP
IN EMPLDB
USING STOGROUP EMPLSG
PRIQTY 1000
SECQTY 500
BUFFERPOOL BP0
DSSIZE 4G
NUMPARTS 12
LOCKSIZE TABLESPACE
CLOSE NO;

• Creates a partitioned tablespace named EMPLTSP in the EMPLDB database.
• STOGROUP: Specifies the storage group EMPLSG for the tablespace.
• PRIQTY and SECQTY: Allocates primary (1000 KB) and secondary (500 KB) space.
• BUFFERPOOL: Uses buffer pool BP0 for optimized data caching.
• DSSIZE: Sets the maximum size of each partition to 4 GB.
• NUMPARTS: Divides the tablespace into 12 partitions.
• LOCKSIZE TABLESPACE: Specifies locking at the tablespace level.
• CLOSE NO: Keeps the tablespace open even when not in use.

Altering a Partitioned Tablespace

To modify an existing partitioned tablespace, use the ALTER TABLESPACE statement. This allows adjustments to space allocations, buffer pools, and other properties.

Examples - Altering a Partitioned Tablespace

ALTER TABLESPACE EMPLTSP
PRIQTY 1200
SECQTY 600
BUFFERPOOL BP1
DSSIZE 6G;

• PRIQTY and SECQTY: Increases primary and secondary space allocations to 1200 KB and 600 KB.
• BUFFERPOOL: Changes buffer pool to BP1.
• DSSIZE: Increases maximum size per partition to 6 GB.

Deleting a Partitioned Tablespace

To delete a partitioned tablespace, use the DROP TABLESPACE statement. This removes the tablespace and all associated data within it.

Examples - Deleting a Partitioned Tablespace

DROP TABLESPACE SALES_TS;

Deletes the SALES_TS tablespace, along with all associated tables and indexes.

---