Sample Exam Questions

From the objective of OSS-DB Exam Silver
S3.1 SQL commands (Tablespace)

Sample Question

3.03

Choose three correct ways to use tablespaces.

  1. Place the new database created by CREATE DATABASE in a tablespace different from the default one.
  2. Place the new schema created by CREATE SCHEMA in a tablespace different from the default one.
  3. Place the new table created by CREATE TABLE in a tablespace different from the default one.
  4. Place the new view created by CREATE VIEW in a tablespace different from the source table.
  5. Place the new index created by CREATE INDEX in a tablespace different from the source table.

※This sample exam is different from those that appear in the actual OSS-DB Exam.
2024/05/01

Answer and Explanation

PostgreSQL's database cluster is located under the directory pointed to by the PGDATA environment variable. However, by using the tablespace feature, you can store database objects in a different directory that is symbolically linked from there.

This can be used to compensate for a lack of disk space, or to improve performance by distributing database access across multiple physical disks.

Internally, the CREATE DATABASE command copies the template database. By default, the new database is created in the same tablespace as the template database. However, by specifying the TABLESPACE option, you can use a different tablespace.

When creating a table or index with CREATE TABLE or CREATE INDEX, the objects are created in the tablespace specified by the default_tablespace configuration parameter by default. You can also use a different tablespace by specifying the TABLESPACE option. Furthermore, when adding a UNIQUE or PRIMARY KEY constraint with CREATE TABLE or ALTER TABLE, an index is automatically created. In this case, by specifying the USING INDEX TABLESPACE option, you can create the index in a different tablespace from the table.

Schemas only define the namespaces of database objects and do not hold data themselves, so they are not associated with tablespaces.

Views are also not associated with tablespaces because only the SQL statement that defines them is stored in the database, and the data itself remains stored in the table referenced by the view. However, please note that in the case of materialized views, which hold data separately from the original table, you can specify the tablespace to use.

Therefore, the correct answers are A, C, and E.