select distinct columnname from mytable fetch first n rows only;
然而，我目前被困在以前的版本中，想知道如何实现这个结果。
理想情况下，查询应该立即返回行，即使是对于一个巨大的表。它应该在找到N个不同的结果后立即返回结果，而不是处理所有行。
我认为使用
.. where rownum < XXX
应该可以工作
也许
select * from (select distinct columnname from mytable) where rownum < XXX
没有一种Oracle版本有本地语法以最佳方式返回独特的Top N。此功能必须使用PL/SQL管道函数手动创建。
此脚本创建一个带有一列的表，大约有1亿行，并使用大约1GB的空间。
--drop table mytable purge;
create table mytable(columnname number not null) nologging;
insert /*+ append */ into mytable
select level from dual connect by level <= 100000;
commit;
begin
for i in 1 .. 10 loop
insert /*+ append */ into mytable select * from mytable;
commit;
end loop;
end;
/
begin
dbms_stats.gather_table_stats(user, 'MYTABLE');
end;
/
--1.25GB.
select bytes/1024/1024/1024 gb from dba_segments where segment_name = 'MYTABLE';
新的12c语法在返回少量行时始终需要约20秒：
select distinct columnname from mytable fetch first 10 rows only;
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 |
------------------------------------------
select * from (select distinct columnname from mytable) where rownum < 10;
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 an object to hold a record with the result columns.
--(Not necessary for this simple example since there's only one column, but will
-- be necessary if there are multiple columns.)
create or replace type columnname_rec is object
(
columnname number
);
--Create an object to hold a table of the records.
create or replace type columnname_tab is table of columnname_rec;
还有另外一个函数，返回不同类型：
--Function that returns the distinct Top N as soon as they are found.
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
--Get new value.
fetch p_cursor into v_columnname;
--If the new value does not exist...
if not v_previous_values.exists(v_columnname) then
--Save the new value.
v_previous_values(v_columnname) := v_columnname;
--Increment the counter.
v_distinct_count := v_distinct_count + 1;
--Return the value
pipe row(columnname_rec(v_columnname));
--Exit if the counter is more than the top N.
exit when v_distinct_count >= p_n;
end if;
end loop;
end;
/
select * from table(fast_distinct_top_n(10, cursor(select * from mytable)));
如果你无法忍受创建太多的对象，那么可能有一种使用Method4来使其通用化的方法。但这个解决方案仍然会很复杂。