Oracle SQL - Sélectionner les n premières lignes distinctes

Question

Dans Oracle 12, nous disposons enfin d'une fonctionnalité de limite, ce qui nous permet de simplement

select distinct columnname from mytable fetch first n rows only;

Cependant, je suis actuellement coincé avec une version antérieure et j'aimerais savoir comment obtenir ce résultat.

Idéalement, la requête devrait retourner des lignes immédiatement même pour une grande table. Il devrait retourner les résultats dès que les N distincts ont été trouvés, au lieu de traiter toutes les lignes.

Answer 1

Je pense qu'utiliser

.. where rownum < XXX

devrait fonctionner

Peut-être

select * from (select distinct columnname from mytable) where  rownum < XXX

Answer 2

Aucune version d'Oracle ne possède une syntaxe native qui renvoie un Top N distinct de manière optimale. Cette fonction doit être créée manuellement à l'aide d'une fonction pipelinée PL/SQL.

Exemple de schéma

Ce script crée une table avec une colonne, environ 100 millions de lignes, qui utilise environ 1 Go d'espace.

--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';

La clause de limitation de ligne d'Oracle 12c ne fonctionne pas bien avec distinct.

La nouvelle syntaxe 12c s'exécute systématiquement en 20 secondes environ pour renvoyer un petit nombre de lignes :

select distinct columnname from mytable fetch first 10 rows only;

L'instruction lit l'ensemble de la table, effectue un hachage de l'ensemble de la table, puis saisit les N premières lignes :

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 |
------------------------------------------

La version Oracle 11g créée par Ed Heal ci-dessous fonctionne de manière surprenante. meilleur ! Il fonctionne en 12 secondes environ.

select * from (select distinct columnname from mytable) where  rownum < 10;

Même si c'est beaucoup plus rapide, 12 secondes c'est quand même horrible. Quelles que soient les performances de mon processeur ou de mes E/S, les algorithmes doivent être erronés si cela prend des secondes au lieu de millisecondes.

En effet, ce plan semble un peu meilleur. Il comporte un SORT GROUP BY STOPKEY faible dans le plan. Cela arrête la requête avant qu'elle ne traite tout . Mais c'est encore beaucoup trop tard. (Peut-être qu'Oracle lit toujours la table entière mais n'en trie qu'une partie).

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 |
-------------------------------------------

Fonctions pipelines

C'est une solution peu glorieuse pour plusieurs raisons. Elle nécessite un nouveau code et de nouveaux objets pour les différents ensembles de résultats. Elle n'est pas forcément évolutive - la fonction dispose d'une collection pour stocker les résultats précédents, que se passe-t-il si cette collection devient énorme ?

Chaque type de résultat différent nécessite de nouveaux objets :

--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;

Et une fonction différente qui renvoie un type différent :

--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;
/

Mais finalement, nous avons créé une requête qui renvoie les N premiers résultats distincts en millisecondes.

select * from table(fast_distinct_top_n(10, cursor(select * from mytable)));

Si vous ne supportez pas de créer autant d'objets, il y a probablement un moyen de les rendre génériques en utilisant Méthode4 . Mais cette solution serait encore compliquée.