Database Index Optimization for Data Analysts

1. Which of the following is a sign that a query might benefit from an index?

Full table scan shown in execution plan

Query returns results in under 1 second

The table has a clustered index

The WHERE clause contains an OR operator

Submit

4. True or False: Updating table statistics can help the query optimizer choose better execution plans.

True

False

5. Which index strategy is most appropriate for a column frequently used in ORDER BY clauses?

Bitmap index

Hash index

B-tree index

Full-text index

6. When optimizing a slow JOIN query, what should you examine first?

Indexes on the join columns

Table statistics and row counts

The execution plan and join type

The WHERE clause filters

7. What metric in an execution plan indicates the estimated number of rows processed?

Seek predicate

Estimated rows

Index depth

Buffer pool size

8. True or False: A bitmap index is ideal for columns with high cardinality.

True

False

9. In query optimization, what does the term 'selectivity' mean?

The ability to select specific columns

The percentage of rows that match a WHERE clause condition

The number of indexes on a table

The speed at which data is retrieved

10. What is the difference between a clustered and non-clustered index?

Clustered indexes are faster but non-clustered are more flexible

A table can have one clustered index but multiple non-clustered indexes

Non-clustered indexes store data while clustered indexes store pointers only

Clustered indexes are only used for primary keys

11. What is the primary purpose of creating an index on a database table?

To reduce the storage space required

To speed up data retrieval and query performance

To automatically back up table data

To enforce referential integrity constraints

12. When should you consider rebuilding an index rather than reorganizing it?

When fragmentation is below 10%

When fragmentation exceeds 30%

When the table has fewer than 1000 rows

During peak business hours

13. What is the main disadvantage of maintaining too many indexes on a table?

Increased storage requirements and slower INSERT/UPDATE operations

Reduced query performance on SELECT statements

Loss of data integrity

Automatic table replication

14. True or False: Creating an index on every column in a table always improves query performance.

True

False

15. In a database, what does cardinality refer to in the context of indexing?

The number of rows in a table

The number of unique values in a column

The order of columns in a composite index

The physical location of index blocks

16. Which statement best describes a covering index?

An index that includes all columns from the table

An index that includes all columns needed to answer a query

An index that covers multiple tables

An index used to cover missing primary keys

17. What is index fragmentation and how does it affect performance?

Fragmentation increases index size and slows queries

Fragmentation distributes data evenly across pages

Fragmentation has no measurable impact on speed

Fragmentation only affects read-only queries

18. When analyzing a slow query, what tool would you use to examine the execution plan?

EXPLAIN or EXPLAIN PLAN statement

INDEX SCAN utility

Query Profiler only

Data Dictionary view

19. What does a composite index consist of?

Multiple tables indexed together

A single column indexed multiple times

Multiple columns indexed as a single key

Indexes on both primary and foreign keys

20. Which type of index is best suited for range queries such as WHERE age BETWEEN 25 AND 35?

Hash index

B-tree index

Bitmap index

Full-text index

Data Analyst Optimizing a Slow Database Index Quiz

1. Which of the following is a sign that a query might benefit from an index?

2.

What first name or nickname would you like us to use?

2. A data analyst discovers a query scanning 10 million rows but returning only 100. The best optimization approach is to create a(n)____ on the filter columns.

3. What is index______ and why is it important for database performance?

4. True or False: Updating table statistics can help the query optimizer choose better execution plans.

5. Which index strategy is most appropriate for a column frequently used in ORDER BY clauses?

6. When optimizing a slow JOIN query, what should you examine first?

7. What metric in an execution plan indicates the estimated number of rows processed?

8. True or False: A bitmap index is ideal for columns with high cardinality.

9. In query optimization, what does the term 'selectivity' mean?

10. What is the difference between a clustered and non-clustered index?

11. What is the primary purpose of creating an index on a database table?

12. When should you consider rebuilding an index rather than reorganizing it?

13. What is the main disadvantage of maintaining too many indexes on a table?

14. True or False: Creating an index on every column in a table always improves query performance.

15. In a database, what does cardinality refer to in the context of indexing?

16. Which statement best describes a covering index?

17. What is index fragmentation and how does it affect performance?

18. When analyzing a slow query, what tool would you use to examine the execution plan?

19. What does a composite index consist of?

20. Which type of index is best suited for range queries such as WHERE age BETWEEN 25 AND 35?