Advanced Techniques in Database Indexing & Query Optimization
Explore advanced strategies for optimizing database queries and indexing.
Questions in this quiz
10 questions on Advanced Techniques in Database Indexing & Query Optimization. Work through them here, then start the quiz to answer them and see your score.
1.What is a covering index and how does it improve query performance?
- AAn index that includes all columns of a table.
- BAn index that is only used for foreign key constraints.
- CAn index that covers multiple tables in a join.
- DAn index that contains all the columns required by a query.
2.Which of the following scenarios would benefit most from a composite index?
- AQueries filtering on a single column.
- BQueries that only use aggregate functions.
- CQueries that perform full table scans.
- DQueries filtering on multiple columns with AND conditions.
3.How does a bitmap index differ from a B-tree index?
- ABitmap indexes are only used in NoSQL databases.
- BBitmap indexes are stored in a tree structure.
- CBitmap indexes are efficient for low-cardinality columns.
- DBitmap indexes are faster for high-cardinality columns.
4.What is the primary advantage of using a clustered index?
- AIt is faster to update than a non-clustered index.
- BIt improves the performance of range queries.
- CIt allows multiple indexes on the same column.
- DIt stores data in a non-sequential order.
5.Why might an index not be used by the query optimizer?
- AThe query is too complex.
- BThe index is on a foreign key column.
- CThe index is on a primary key column.
- DThe index statistics are outdated.
6.In what scenario is a hash index most effective?
- AWhen joining tables.
- BWhen sorting data.
- CWhen performing equality searches.
- DWhen performing range queries.
7.How can you reduce the impact of index fragmentation?
- ABy increasing the size of the database.
- BBy creating more indexes.
- CBy rebuilding or reorganizing the index.
- DBy using a hash index.
8.What is the effect of index selectivity on query performance?
- AHigher selectivity leads to better performance.
- BHigher selectivity leads to worse performance.
- CSelectivity does not affect performance.
- DLower selectivity leads to better performance.
9.What is the purpose of an execution plan in query optimization?
- ATo display the query results.
- BTo store query results for future use.
- CTo execute the query faster.
- DTo show how a query will be executed by the database.
10.Why might a database use a full table scan instead of an index?
- AThe index is more costly to use than scanning the table.
- BThe table is very large.
- CThe query is too simple.
- DThe table has no primary key.