CTE Database Administration 2 — Questions and Answers
Question 1: A telecom OSS database experiences slow query performance on a table with 50 million CDR records. Which indexing strategy is MOST appropriate?
- Add a composite index on frequently filtered columns like call_date and subscriber_id (Correct answer)
- Add an index on every column in the table
- Remove all existing indexes to free up storage
- Convert the table to a flat file for faster reads
Correct answer: Add a composite index on frequently filtered columns like call_date and subscriber_id
Composite indexes on high-cardinality filter columns dramatically reduce scan rows for CDR queries.
Question 2: In a telecom billing database, which isolation level prevents dirty reads while allowing non-repeatable reads?
- READ UNCOMMITTED
- READ COMMITTED (Correct answer)
- REPEATABLE READ
- SERIALIZABLE
Correct answer: READ COMMITTED
READ COMMITTED prevents dirty reads by only reading committed data, but allows non-repeatable reads if another transaction commits between reads.
Question 3: A DBA at a telecom carrier needs to partition a large subscriber table. Which partitioning method is BEST for range-based monthly billing cycles?
- Hash partitioning on subscriber_id
- Range partitioning on bill_date (Correct answer)
- List partitioning on service_type
- Round-robin partitioning
Correct answer: Range partitioning on bill_date
Range partitioning on bill_date aligns naturally with monthly billing cycles, enabling efficient partition pruning for period-based queries.
Question 4: Which database object is used in telecom OSS/BSS systems to enforce referential integrity between subscriber accounts and their associated service orders?
- Stored procedure
- Trigger
- Foreign key constraint (Correct answer)
- Check constraint
Correct answer: Foreign key constraint
Foreign key constraints enforce referential integrity by ensuring service order records cannot reference non-existent subscriber accounts.
Question 5: A telecom DBA must ensure the billing database can recover to any point in the last 24 hours. Which backup strategy supports this requirement?
- Weekly full backups only
- Daily full backups with hourly differential backups
- Daily full backups with continuous transaction log backups (Correct answer)
- Monthly full backups with weekly incremental backups
Correct answer: Daily full backups with continuous transaction log backups
Continuous transaction log backups combined with full backups enable point-in-time recovery to any moment within the retention window.
Question 6: In telecom database replication, what is the PRIMARY difference between synchronous and asynchronous replication?
- Synchronous replication uses more disk space than asynchronous
- Synchronous replication waits for replica acknowledgment before committing, while asynchronous does not (Correct answer)
- Asynchronous replication has zero data loss risk
- Synchronous replication can only replicate to one replica
Correct answer: Synchronous replication waits for replica acknowledgment before committing, while asynchronous does not
Synchronous replication guarantees no data loss by requiring replica confirmation before commit, at the cost of higher write latency.
Question 7: A telecom company's network inventory database contains NULL values in the 'equipment_vendor' column. Which SQL function returns a default value when NULL is encountered?
- ISNULL() or COALESCE() (Correct answer)
- NULLIF()
- CAST()
- CONVERT()
Correct answer: ISNULL() or COALESCE()
COALESCE() returns the first non-NULL argument, while ISNULL() (SQL Server) substitutes a specified replacement for NULL values.
CTE Database Administration 2
A telecom OSS database experiences slow query performance on a table with 50 million CDR records.
Which indexing strategy is MOST appropriate?