Indexes & Performance · SQL SQL
What is a partial index, and when is it most advantageous to use one?
Practice question from Indexes & Performance (SQL SQL), with the correct answer and a step-by-step solution.
hard · MCQ · Q-848CE1
What is a partial index, and when is it most advantageous to use one?
- A.An index built on only a subset of table rows defined by a WHERE clause, saving storage and maintenance overhead for unindexed rows.Correct answer
- B.An index that only indexes the first 10 characters of a text column to save RAM.
- C.An index that is temporarily disabled during bulk inserts and re-enabled afterward.
- D.An index created automatically on tables with fewer than 1,000 rows.
Solution
A partial index includes a predicate (e.g., CREATE INDEX idx_active ON users(email) WHERE status = 'active'). It is beneficial when queries target a specific subset of data, drastically reducing index size and write overhead.
More from Indexes & Performance
- What is the primary purpose of a B-tree index on a relational database column?
- Consider a table users with columns (id SERIAL PRIMARY KEY, email VARCHAR(255), status VARCHAR(50)). If a query frequently filters by email…
- What does a 'covering index' achieve in relational database performance optimization?
- Which of the following scenarios is most likely to result in an index being ignored by the query optimizer during a SELECT query with a WHE…
Practise more SQL
Sign up free for adaptive practice on this chapter, with instant feedback and hints on every question.
Start practising freeFree to start · No credit card needed