Introduction
Comment afficher les TOP N éléments de plusieurs groupes d'une table au lieu de tous les éléments de chaque groupe? L'objectif est d'avoir une idée du contenu des groupes d'une table ou d'une vue, nous utiliserons pour cela la fonction analytique ROW_NUMBER.


 

Points d'attention
NA.

 


 

Base de tests
N'importe quelle base Oracle. Testé sur une 12.1.

 



Exemples
Ce que je souhaite c'est par exemple afficher les trois premiers noms de tous les types d'objets gérés par Oracle, pour avoir une idée de ce qui existe dans ma base. 

Je pourrais faire :
          SQL> SELECT object_type, object_name FROM dba_objects ORDER BY object_type, object_name;
Le problème est que cet ordre ramène 72 870 enregistrements et que je ne peux pas me déplacer dans l'écran car les premières lignes ne sont plus visibles. Je pourrais faire aussi un filtre par type d'objet mais ce serait trop long pour chaque type d'objet, ce que je veux c'est juste avoir un aperçu des groupes qui existent.

La solution est de créer une vue intermédiaire, de donner un numéro à chaque enregistrement via la fonction ROW_NUMBER puis de filtrer sur les trois premiers enregistrements de cette vue.  

La partie
          select object_type, object_name, owner, ROW_NUMBER() OVER (PARTITION BY object_type ORDER BY object_type) num_row from dba_objects 
crée une vue contenant tous les enregistrements de dba_objects, les trie selon leur type puis numérote chaque ligne de 1 à N via la fonction ROW_NUMBER, en réinitialisant la numérotation à chaque nouveau type d'objet; ce numéro est stocké dans la colonne de nom num_row. Il suffit ensuite de filtrer sur cette colonne pour n'afficher que les TOP N enregistrements.        

     SQL> select object_type, object_name, owner from (select object_type, object_name, owner, ROW_NUMBER() OVER (PARTITION BY object_type ORDER BY object_type) num_row from dba_objects ) where num_row BETWEEN 1 and 3 ORDER BY object_type, num_row;

     OBJECT_TYPE             OBJECT_NAME                        OWNER
     ----------------        ------------------------      --------------
     CLUSTER         C_FILE#_BLOCK#                        SYS
     CLUSTER         C_RG#                            SYS
     CLUSTER         C_MLOG#                         SYS
     CONSUMER GROUP        ORA$APPQOS_7                        SYS
     CONSUMER GROUP        BATCH_GROUP                        SYS
     CONSUMER GROUP        ORA$AUTOTASK                        SYS
     CONTAINER        CDB$ROOT                        SYS
     CONTAINER        ORDS                            SYS
     CONTAINER        ORCL                            SYS
     CONTEXT         LSBY_APPLY_CONTEXT                    SYS
     CONTEXT         MAC$FACTOR                        SYS
     CONTEXT         ORA_OLS_SESSION_LABELS                    SYS
     DATABASE LINK        SYS_HUB                         SYS
     DESTINATION        SCHED$_LOCAL_PSEUDO_AGENT                SYS
     DESTINATION        SCHED$_LOCAL_PSEUDO_DB                    SYS
     DIRECTORY        ORACLE_HOME                        SYS
     DIRECTORY        XMLDIR                            SYS
     DIRECTORY        XSDDIR                            SYS
     EDITION         ORA$BASE                        SYS
     EVALUATION CONTEXT    AQ$_SCHEDULER$_EVENT_QTAB_V                SYS
     EVALUATION CONTEXT    AQ$_KUPC$DATAPUMP_QUETAB_1_V                SYS
     EVALUATION CONTEXT    DV$RULE_EVALCTX                     SYS
     FUNCTION        GETLONG                         SYS
     FUNCTION        F$DV$_DBLINK_INFO                    DVF
     FUNCTION        F$DV$_CLIENT_IDENTIFIER                 DVF
     INDEX            I_OBJ2                            SYS
     INDEX            SYS_C008095                        SYS
     INDEX            SYS_C008094                        SYS
     INDEX PARTITION     WRP$_REPORTS_IDX01                    SYS
     INDEX PARTITION     SYS_IL0000017945C00030$$                AUDSYS
     INDEX PARTITION     SYS_IL0000017945C00031$$                AUDSYS
     INDEXTYPE        XDBHI_IDXTYP                        XDB
     INDEXTYPE        SEMCONTEXT                        MDSYS
     INDEXTYPE        SEM_INDEXTYPE                        MDSYS
     JAVA CLASS        sun/util/xml/PlatformXmlPropertiesProvider$Resolver    SYS
     JAVA CLASS        oracle/spatial/rdf/util/XMLInputStream            MDSYS
     JAVA CLASS        oracle/spatial/rdf/util/RDFXMLParseException        MDSYS
     JAVA DATA        LocaleData_coll_ar                    SYS
     JAVA DATA        JAVA$POLICY$SHARED$00000021                OJVMSYS
     JAVA DATA        JAVA$POLICY$SHARED$00000020                OJVMSYS
     JAVA RESOURCE        META-INF/services/javax.script.ScriptEngineFactory    SYS
     JAVA RESOURCE        oracle/spatial/geocoder/head.html            MDSYS
     JAVA RESOURCE        oracle/spatial/network/lod/config/LODConfigs.xsd    MDSYS
     JAVA SOURCE        dbFWTrace                        SYS
     JAVA SOURCE        schedFileWatcherJava                    SYS
     JOB            PURGE_LOG                        SYS
     JOB            MGMT_STATS_CONFIG_JOB                    ORACLE_OCM
     JOB            MGMT_CONFIG_JOB                     ORACLE_OCM
     JOB CLASS        DEFAULT_JOB_CLASS                    SYS
     JOB CLASS        XMLDB_NFS_JOBCLASS                    SYS
     JOB CLASS        AQ$_PROPAGATION_JOB_CLASS                SYS
     LIBRARY         DBMS_PDB_LIB                        SYS
     LIBRARY         KZV$RSRC_LIBT                        DVSYS
     LIBRARY         KZV$ADM_LIBT                        DVSYS
     LOB            SYS_LOB0000000127C00004$$                SYS
     LOB            SYS_LOB0000075741C00038$$                SYS
     LOB            SYS_LOB0000075741C00094$$                SYS
     LOB PARTITION        SYS_LOB0000007379C00009$$                SYS
     LOB PARTITION        SYS_LOB0000017945C00030$$                AUDSYS
     LOB PARTITION        SYS_LOB0000017945C00031$$                AUDSYS
     LOCKDOWN PROFILE    PRIVATE_DBAAS                        PUBLIC
     LOCKDOWN PROFILE    PUBLIC_DBAAS                        PUBLIC
     LOCKDOWN PROFILE    SAAS                            PUBLIC
     OPERATOR        XMLSEQUENCE                        SYS
     OPERATOR        SEM_CONTAINS_COUNT                    MDSYS
     OPERATOR        SEM_CONTAINS_SELECT                    MDSYS
     PACKAGE         STANDARD                        SYS
     PACKAGE         DBMS_CLUSTDB                        SYS
     PACKAGE         CONFIGURE_DV_INTERNAL                    DVSYS
     PACKAGE BODY        STANDARD                        SYS
     PACKAGE BODY        DBMS_CLUSTDB                        SYS
     PACKAGE BODY        DBMS_MACOLS_SESSION                    DVSYS
     PROCEDURE        SUBPTXT2                        SYS
     PROCEDURE        CONFIGURE_DV                        SYS
     PROCEDURE        SET_FACTOR                        DVSYS
     PROGRAM         PURGE_LOG_PROG                        SYS
     PROGRAM         BSLN_MAINTAIN_STATS_PROG                SYS
     PROGRAM         AUTO_SQL_TUNING_PROG                    SYS
     QUEUE            AQ$_SCHEDULER$_EVENT_QTAB_E                SYS
     QUEUE            AQ$_KUPC$DATAPUMP_QUETAB_1_E                SYS
     QUEUE            AQ$_AQ_SRVNTFN_TABLE_1_E                SYS
     RESOURCE PLAN        APPQOS_PLAN                        SYS
     RESOURCE PLAN        MIXED_WORKLOAD_PLAN                    SYS
     RESOURCE PLAN        DEFAULT_MAINTENANCE_PLAN                SYS
     RULE            ALERT_QUE$1                        SYS
     RULE SET        SCHEDULER$_EVENT_QUEUE_R                SYS
     RULE SET        WM$EVENT_QUEUE_N                    WMSYS
     RULE SET        WM$EVENT_QUEUE_R                    WMSYS
     SCHEDULE        DAILY_PURGE_SCHEDULE                    SYS
     SCHEDULE        BSLN_MAINTAIN_STATS_SCHED                SYS
     SCHEDULE        PMO_DEFERRED_GIDX_MAINT_SCHED                SYS
     SCHEDULER GROUP     MAINTENANCE_WINDOW_GROUP                SYS
     SCHEDULER GROUP     ORA$AT_WGRP_SQ                        SYS
     SCHEDULER GROUP     ORA$AT_WGRP_SA                        SYS
     SEQUENCE        UGROUP_SEQUENCE                     SYS
     SEQUENCE        AQ$_KUPC$DATAPUMP_QUETAB_1_N                SYS
     SEQUENCE        UTL_RECOMP_SEQ                        SYS
     SYNONYM         DUAL                            PUBLIC
     SYNONYM         GV$GES_RESOURCE                     PUBLIC
     SYNONYM         GV$GES_TRAFFIC_CONTROLLER                PUBLIC
     TABLE            TS$                            SYS
     TABLE            SYSNTDMM9KDRJR5$S_5UHYU+BV06E                SYS
     TABLE            SYSNT-X6HGAOE+ON4LLRGYU+BV06E                SYS
     TABLE PARTITION     WRP$_REPORTS                        SYS
     TABLE PARTITION     AUD$UNIFIED                        AUDSYS
     TABLE PARTITION     WRP$_REPORTS_TIME_BANDS                 SYS
     TABLE SUBPARTITION    WRI$_OPTSTAT_SYNOPSIS$                    SYS
     TABLE SUBPARTITION    WRI$_OPTSTAT_SYNOPSIS$                    SYS
     TABLE SUBPARTITION    WRI$_OPTSTAT_SYNOPSIS$                    SYS
     TRIGGER         SYSLSBY_EDS_DDL_TRIG                    SYS
     TRIGGER         LBAC$AFTER_CREATE                    LBACSYS
     TRIGGER         LBAC$BEFORE_ALTER                    LBACSYS
     TYPE            BFILE                            SYS
     TYPE            EVENT_STATUS_TABLE_TYPE                 DVSYS
     TYPE            EVENT_STATUS_ROW_TYPE                    DVSYS
     TYPE BODY        JDOM_T                            SYS
     TYPE BODY        LBAC_LABEL_LIST                     LBACSYS
     TYPE BODY        LBAC_PRIVS                        LBACSYS
     UNDEFINED        ORA$QOS_CDB_PLAN                    SYS
     UNDEFINED        CURRENT_OPEN_WINDOW                    SYS
     UNDEFINED        FILE_WATCHER_COUNT                    SYS
     UNIFIED AUDIT POLICY    ORA_RAS_POLICY_MGMT                    SYS
     UNIFIED AUDIT POLICY    ORA_DV_AUDPOL2                        SYS
     UNIFIED AUDIT POLICY    ORA_DV_AUDPOL                        SYS
     VIEW            ALL_XML_SCHEMAS                     SYS
     VIEW            AQ$KUPC$DATAPUMP_QUETAB_1_R                SYS
     VIEW            QT75741_BUFFER                        SYS
     WINDOW            MONDAY_WINDOW                        SYS
     WINDOW            TUESDAY_WINDOW                        SYS
     WINDOW            WEDNESDAY_WINDOW                    SYS
     XML SCHEMA        XDbD/PLZ01TcHgNAgAIIegtw==                XDB
     XML SCHEMA        XDh1jUheYAR5PgNAgAILJCxg==                XDB
     XML SCHEMA        XDRwDSD/9zVX7gU4fl5QqImQ==                XDB
     
     133 rows selected.