Oracle SQL 亿级无name索引emp表如何获取至少20条不重复姓名
适配约束的最终实现方案
核心实现思路
先梳理现有可用条件:
emp.id作为主键必然带有序存储的B树索引,无需额外建索引,沿该索引可直接顺序读取数据,完全规避全表扫描- 禁止在
emp.name字段建索引,因此绝对不能走“全表扫描、对全量name做distinct、按name排序再取前20”的原查询逻辑,否则几十亿行的数据量根本跑不出结果 - 需求本质是快速拿到20条不重复的name,最终按name排序返回即可,不需要把全表的所有不重复name都检索出来
实际执行时,沿id主键索引从最小id开始逐行读取name值,每读一行就判断是否重复,未重复的值就记录下来,等攒够20条不重复的name后立刻停止后续扫描——哪怕前面几千行全是重名,凑够数就停,这点量级的数据排序几乎没有耗时。
最终可用SQL
SELECT * FROM ( SELECT /*+ INDEX(e 你的emp表主键索引名) NO_PUSH_PRED */ DISTINCT e.name FROM emp e ORDER BY e.id ) WHERE rownum <= 20 ORDER BY name;
关键细节说明
- 语句中的
你的emp表主键索引名是建表时随主键自动生成的,无需额外创建,替换为实际的索引名称即可 INDEX提示的作用是强制走主键索引,完全杜绝全表扫描,把IO开销压到最低NO_PUSH_PRED提示专门规避优化器的逻辑缺陷:之前修改版的核心问题,就是优化器把外层的rownum<=20截断条件下推到表扫描层,只取前20条原始行就停止扫描,哪怕全是重名数据也不会向后补读。加这个提示后,优化器就不会提前截断原始扫描的行数- 数据库实际执行路径:沿着id索引从小到大逐行读取name,遇到重复值就跳过,一旦去重后的结果凑够20条,立刻终止后续索引扫描,根本不会触碰后面几十亿行的表数据
- 最后排序的对象仅为20条记录,这点数据量的排序是纳秒级开销,完全没有性能压力
性能对比
- 对比最初的慢查询:原查询需要把全表几十亿行的name全部读出来做distinct,再做全量排序,耗时超1小时是必然结果。本方案实际扫描的行数量级仅为几千行,占总数据量的比例不到万分之一,整体耗时基本在几十毫秒级
- 对比之前提前截断的坏查询:不会因为重名率高导致返回的不重复记录数不足20条,完全满足业务输出要求
内容的提问来源于stack exchange,提问作者Vinoth Karthick
相关产品推荐
相关产品推荐

