Advanced Indexes Concepts Quiz Quiz

Test your expertise with this challenging quiz on indexes, covering key principles, types, maintenance, and performance optimization. Perfect for those seeking a deep understanding of index structures and their practical implications in database systems.

1. Clustered vs Nonclustered Indexes

   Which of the following best describes a clustered index in a relational database table?

   1. It requires a unique constraint on every column.
   2. It is used only for views and not base tables.
   3. It physically sorts the data rows according to the index key.
   4. It stores only index entries, separate from the table's data.

   Explanation: A clustered index determines the physical order of data rows in a table, making retrieval efficient for range queries on the indexed columns. Nonclustered indexes store index entries separately from the table data, acting as pointers. Clustered indexes can be created on base tables as well as certain types of views, but not exclusively on views. While a clustered index often requires a unique key (either enforced or with an internal identifier), it does not require every column to be unique.

2. Index Maintenance Overhead

   Why can frequent data modifications (INSERT, UPDATE, DELETE) negatively impact index performance?

   1. They cause additional updates to index structures with each modification.
   2. They require rebuilding the entire database each time.
   3. They lock the table for hours after each change.
   4. They prevent the use of any further indexes.

   Explanation: Whenever data is added, updated, or removed, index structures must be updated to reflect those changes, potentially leading to fragmentation or increased overhead. Indexes do not require full database rebuilds after every modification, only localized updates. Table locks are not necessarily held for extended durations due to indexes unless misconfigured. Additional indexes can still be created, unless explicitly restricted.

3. Index Selectivity

   Which statement about index selectivity is correct in optimizing query performance?

   1. Only indexes on foreign keys are considered selective.
   2. An index on a column with highly unique values improves selectivity and performance.
   3. Low selectivity indexes always outperform high selectivity ones.
   4. Selectivity is irrelevant to index effectiveness.

   Explanation: High selectivity means many distinct values, leading to more efficient lookups and filtering, thus improving index usefulness. Low selectivity indexes, on columns with many duplicate values, tend to provide less performance benefit. Selectivity is an important metric for index effectiveness, not irrelevant. Indexes on foreign keys may or may not be selective, depending on the data.

4. Composite Indexes

   Given a composite index on (last_name, first_name), which query filter benefits most from this index?

   1. WHERE last_name = 'Smith' AND first_name = 'Jane'
   2. WHERE first_name = 'Jane'
   3. WHERE age = 30
   4. WHERE city = 'Berlin'

   Explanation: A composite index on (last_name, first_name) is most effective when the query includes both columns, or at least the leading one. Filtering only on the second column (first_name) won't efficiently use the index. Queries on unrelated columns, such as age or city, derive no benefit from the composite index. Using both columns matches the index's order, providing the greatest performance improvement.

5. Unique Indexes Enforcement

   What is the main purpose of a unique index on a column?

   1. To speed up backups.
   2. To compress the table data automatically.
   3. To prevent duplicate values from being stored in the indexed column.
   4. To physically store data in reverse order.

   Explanation: A unique index enforces the rule that each value in the indexed column must be distinct, supporting data integrity. Reverse order storage is not the primary purpose, although indexing can specify sort direction. Indexes have no inherent impact on backup speed or table-level compression; those functions are handled separately.

6. Index Types and Use Cases

   Which type of index is most commonly used to improve performance on columns with many range queries?

   1. Bitmap index
   2. Inverted index
   3. B-tree index
   4. Hash index

   Explanation: B-tree indexes enable fast traversal for range-based queries by maintaining a balanced tree structure, making them ideal for such cases. Hash indexes are optimized for exact-match lookups but not ranges. Bitmap indexes are more suitable for columns with low cardinality rather than range queries. Inverted indexes are used primarily for full-text searching rather than numeric or date ranges.

7. Covering Indexes

   What is a key advantage of a covering index in query optimization?

   1. It restricts access to sensitive table data.
   2. It contains all columns needed by the query, eliminating lookups to the table.
   3. It enforces referential integrity between tables.
   4. It prevents the creation of nonclustered indexes.

   Explanation: A covering index includes all the fields required by a query, so the database can satisfy the request using only the index, avoiding additional reads from the table. It does not inherently restrict access to data or enforce referential integrity. Covering indexes are a subset of nonclustered indexes and do not prevent their use or creation.

8. Index Fragmentation Effects

   How does significant index fragmentation affect database read performance?

   1. It automatically compresses the index pages for better performance.
   2. It prevents any queries from being executed until rebuilt.
   3. It increases I/O due to scattered index pages, lowering performance.
   4. It causes data to be permanently lost.

   Explanation: Fragmented indexes have data stored non-sequentially, resulting in more disk or memory reads and harming read speed. Fragmentation does not block queries entirely; the index still functions, just less efficiently. Data loss is not a consequence of fragmentation alone. Fragmentation is not the same as compression; it actually reduces, not improves, storage efficiency.

9. Hidden Costs of Indexes

   Which hidden cost should be considered before adding multiple indexes to a large table?

   1. Guaranteed acceleration of all query types.
   2. Increased storage usage and slower DML operations.
   3. Automatic query rewriting to use the best index.
   4. Instantaneous index update propagation.

   Explanation: Additional indexes consume more disk space and introduce additional overhead during data modifications, potentially slowing down INSERT, UPDATE, or DELETE operations. Indexes do not automatically rewrite queries for better performance; query plans are chosen by the optimizer. Not all queries benefit from indexes, and some may even perform worse if the wrong index is used. Indexing updates are not instantaneous and add extra processing steps.

10. Partial Indexes

    What is a partial index in the context of optimizing queries?

    1. An index that skips every other row by default.
    2. An incomplete or failed index creation attempt.
    3. An index on columns with only partial data types.
    4. An index built only on a subset of table rows that satisfy a specified condition.

    Explanation: Partial indexes are created by specifying a condition so only rows meeting that condition are indexed, improving space and performance for targeted queries. It does not refer to failed index creation or skipping rows arbitrarily. Index definitions do not depend on whether a column contains partial data types; the term refers to conditional row inclusion.