An example of selectivity occurs in database systems when a query condition targets a column where every row holds the exact same value. In such a case, selecting data based on this value would result in retrieving every single row from the table, representing 100% selectivity.
Understanding Selectivity in Database Systems
Selectivity, in the context of databases and query optimization, refers to the proportion of rows returned by a query based on a specific condition. It's a crucial factor that database optimizers use to decide the most efficient way to execute a query, often by determining whether to use an index or perform a full table scan.
The example provided, where a column has the same value across all rows (e.g., an is_active column where all users are true), perfectly illustrates 100% selectivity. If you query WHERE is_active = TRUE, you will retrieve every row because 100% of the data matches your condition. While counter-intuitive to the common understanding of "high selectivity" (which usually implies few results), in this specific sense, it means 100% of the table's rows are selected.
Different Levels of Selectivity
Selectivity is typically expressed as a percentage or a fraction, where:
- 0% Selectivity: No rows match the condition.
- 100% Selectivity: All rows match the condition.
Here’s a breakdown of selectivity with practical examples:
| Selectivity Level | Description | Example Query Condition
