From the objective of OSS-DB Exam Gold
- Performance Tuning - Tuning database and queries
Sample Question
3.07
Choose two correct statements regarding reindexing.
-
Reindexing must be done by stopping the service.
-
Since REINDEX does not lock reads on the underlying table, it has no impact on read operations when executed while the service is running.
-
CREATE INDEX CONCURRENTLY allows you to create an index without acquiring locks that conflict with concurrent inserts, updates, and deletes.
-
CREATE INDEX CONCURRENTLY can also be used to create primary keys.
-
Regularly re-creating indexes can help prevent index bloat.
※This sample exam is different from those that appear in the actual OSS-DB Exam.
2025/06/02
Answer and Explanation
A. Reindexing does not necessarily require a service outage. However, using the REINDEX command can impact running services, potentially blocking query processing.
B. REINDEX does not lock reads on the underlying table, but it does acquire an exclusive lock on the index itself, which can block queries from running services.
C. Correct.
D. CREATE INDEX CONCURRENTLY cannot be used to create a primary key. Primary keys must be created using ALTER TABLE.
E. Correct.
Therefore, the correct answers are C and E.