没有一种Oracle版本有本地语法以最佳方式返回独特的Top N。此功能必须使用PL/SQL管道函数手动创建。
示例模式
此脚本创建一个带有一列的表,大约有1亿行,并使用大约1GB的空间。
create table mytable(columnname number not null) nologging;
insert into mytable
select level from dual connect by level <= 100000;
commit;
begin
for i in 1 .. 10 loop
insert into mytable select * from mytable;
commit;
end loop;
end;
/
begin
dbms_stats.gather_table_stats(user, 'MYTABLE');
end;
/
select bytes/1024/1024/1024 gb from dba_segments where segment_name = 'MYTABLE';
Oracle 12c行限制子句与distinct一起使用效果不佳。
新的12c语法在返回少量行时始终需要约20秒:
select distinct columnname from mytable fetch first 10 rows only;
该语句读取整个表,对整个表进行哈希操作,然后获取前N行记录。
explain plan for
select distinct columnname from mytable fetch first 10 rows only;
select * from table(dbms_xplan.display(format => 'basic'));
Plan hash value: 239985407
| Id | Operation | Name |
| 0 | SELECT STATEMENT | |
| 1 | VIEW | |
| 2 | WINDOW NOSORT STOPKEY| |
| 3 | VIEW | |
| 4 | HASH UNIQUE | |
| 5 | TABLE ACCESS FULL | MYTABLE |
下面是Ed Heal创建的Oracle 11g版本,令人惊讶的是它运行得更好!大约只需要12秒。
select * from (select distinct columnname from mytable) where rownum < 10;
尽管速度更快,12秒仍然很慢。无论我的CPU或I/O性能如何,如果需要花费秒级而不是毫秒级的时间,则算法肯定有问题。
确实,这个计划看起来好多了。在计划中有一个名为
SORT GROUP BY STOPKEY
的功能。它在查询处理
所有内容之前停止。但它停止的时间还是太晚了。(也许Oracle仍在读取整个表格,只是对其中的一部分进行排序吗?)
explain plan for
select * from (select distinct columnname from mytable) where rownum < 10;
select * from table(dbms_xplan.display(format => 'basic'));
Plan hash value: 3842480186
| Id | Operation | Name |
| 0 | SELECT STATEMENT | |
| 1 | COUNT STOPKEY | |
| 2 | VIEW | |
| 3 | SORT GROUP BY STOPKEY| |
| 4 | TABLE ACCESS FULL | MYTABLE |
流水线函数
这是一个不太理想的解决方案,有几个原因。它需要为不同的结果集编写新代码和对象。它可能无法很好地扩展 - 函数需要使用集合来存储先前的结果,如果该集合变得非常庞大会发生什么?
每种不同的结果类型都需要新的对象:
create or replace type columnname_rec is object
(
columnname number
);
create or replace type columnname_tab is table of columnname_rec;
还有另外一个函数,返回不同类型:
create or replace function fast_distinct_top_n(p_n number, p_cursor in sys_refcursor) return columnname_tab pipelined is
v_columnname number;
v_distinct_count number := 0;
type previous_values_type is table of varchar2(4000) index by varchar2(4000);
v_previous_values previous_values_type;
begin
loop
fetch p_cursor into v_columnname;
if not v_previous_values.exists(v_columnname) then
v_previous_values(v_columnname) := v_columnname;
v_distinct_count := v_distinct_count + 1;
pipe row(columnname_rec(v_columnname));
exit when v_distinct_count >= p_n;
end if;
end loop;
end;
/
但是最终我们创建了一个查询,可以在毫秒内返回不同的前N个结果。
select * from table(fast_distinct_top_n(10, cursor(select * from mytable)));
如果你无法忍受创建太多的对象,那么可能有一种使用Method4来使其通用化的方法。但这个解决方案仍然会很复杂。