数据库系统如何知道特定列有多少个不同的值？

Question

在下面的链接

http://www.programmerinterview.com/index.php/database-sql/selectivity-in-sql-databases/

作者写道，由于“性别”列只有两个可能的值，因此它对10000条记录的选择性将是；根据给定的公式；0.02 %。

但我的问题是，数据库系统如何知道此特定列具有如此多的唯一值？难道数据库系统不需要扫描整个表至少一次吗？或者以其他方式让数据库系统知道这些唯一的值？

Answer 1

首先，你应用的公式是错误的。性别的选择性(在给定的示例中)将是50%，而不是0.02%。这意味着每个值大约50%的时间都会出现。

数据库跟踪这一点的一般方法是使用一种称为“统计”的东西。这些度量值保留在所有表中，并由优化器使用。有时，信息也可以由列上的索引提供。

Answer 2

回到你的实际问题:是的，数据库频繁地扫描所有表数据并保存一些统计数据(例如最大值、最小值、不同键的数量、表中的行数等)。在内部表中。这些统计信息用于对查询(或其他DML操作)的基本结果进行，以便评估最佳的执行计划。您可以通过运行命令EXEC DBMS_STATS.GATHER_DATABASE_STATS;或一些other ones来手动触发统计信息的生成。您还可以建议Oracle仅读取所有数据的样本(例如，所有行的10% )

通常数据内容不会有很大的变化，所以如果这些数字不是绝对准确的，那也没关系，它们(通常)足以估计一个执行计划。

Answer 3

Oracle有许多与计算不同值的数量(NDV)相关的进程。

1. Manual统计信息收集:可以通过DBMS_STATS.
2. AUTOTASK:中的许多不同过程手动触发统计信息收集，因为10g Oracle有一个默认的自动任务作业，即“自动优化器统计信息收集”。仅当当前统计数据为stale.
3. Bulk负载时，它才会收集统计信息:可以收集12c中的统计信息。NDV可以从100%的数据计算得出，也可以基于样本进行估计。该示例可以基于块或rows.
4. One-pass distinct采样： 11g引入了new AUTO_SAMPLE_SIZE algorithm。它扫描整个表，但只使用一次遍历。扫描整个表比对其中的一小部分进行排序要快得多。对该算法还有几个更深入的描述，例如this one.
5. Incremental统计数据:分区表的可以存储有关NDV的额外信息，称为概要。使用此信息，如果只修改了单个分区，则只需分析该分区即可生成分区和全局statistics.
6. Index NDV:在创建索引时，默认情况下会创建索引统计信息。此外，可以从Oracle或DBMS_STATS.
7. Custom统计信息中的其他过程中的级联选项定期重新收集信息:Oracle可以使用STATISTICS.
8. Dynamic或相关的DBMS_STATS.GATHER_INDEX_STATS采样手动设置NDV：在执行查询之前，DBMS_STATS.SET_*可以自动从表中抽取少量数据块来估计NDV。这通常仅在缺少统计数据时发生。