IKM Database Administration Concepts 5

Question 1

An index on a 'gender' column with only three distinct values across 10 million rows is rarely used by the optimizer. What is the most likely reason?

Accepted Answer

The column has low cardinality (poor selectivity)

Answer

Low-selectivity indexes match a large share of rows, so a full scan is often cheaper.

Question 2

Which type of replication can cause data loss on failover because the primary commits before the replica confirms the change?

Accepted Answer

Asynchronous replication

Answer

Asynchronous replication does not wait for the replica to acknowledge, so recent commits can be lost if the primary fails.

Question 3

A DBA splits a large sales table so that each year's rows are stored separately but can still be queried as one table. What is this technique called?

Accepted Answer

Horizontal partitioning by range

Answer

Range partitioning divides rows by a key such as date, which allows partition pruning and easier archiving.

Question 4

In a database that uses MVCC, such as PostgreSQL, why is routine VACUUM maintenance needed?

Accepted Answer

To reclaim space taken by dead row versions and prevent transaction ID wraparound

Answer

MVCC leaves obsolete tuple versions behind, and VACUUM clears them out and freezes old transaction IDs.

Question 5

Queries suddenly slow down after a large bulk load, even though the indexes are unchanged. What should a DBA check first?

Accepted Answer

Whether optimizer statistics are out of date

Answer

Stale statistics give the optimizer wrong row estimates, which leads to poor execution plans after big data changes.

IKM - International Knowledge Measurement Practice Test