How to Limit Number of Rows Returned in a Query

  • by

You can order a specific column in a query by adding ORDER BY clause, but the result always returns and lists all the rows that some of them may not be needed. Actually, the meaningful rows could be the first few rows in a decision making process, the others could be nonsense information. For MySQL, which can use LIMIT clause to limit the number of returned rows, but Oracle can not.

Here is an example to list only meaningful rows which may be top 5 or top 10 rows. In this case, we would like to know the total number of objects by owner and object types.

SQL> set pagesize 50;
SQL> column owner format a15;
SQL> column object_type format a20;
SQL> select owner, object_type, count(*) total from dba_objects group by owner, object_type order by 3 desc;

OWNER           OBJECT_TYPE               TOTAL
--------------- -------------------- ----------
PUBLIC          SYNONYM                   27724
SYS             JAVA CLASS                20444
SYS             VIEW                       3763
ORDSYS          JAVA CLASS                 1898
SYS             TYPE                       1306
SYS             INDEX                       996
SYS             TABLE                       969
SYSMAN          INDEX                       953
APEX_030200     INDEX                       946
SYS             JAVA RESOURCE               760
SYSMAN          TABLE                       729
SYSMAN          TYPE                        678
SYS             PACKAGE                     623
SYS             PACKAGE BODY                597
MDSYS           JAVA CLASS                  531
SYSMAN          VIEW                        471
ORDSYS          TYPE                        446
APEX_030200     TRIGGER                     366
APEX_030200     TABLE                       360
XDB             LOB                         340
SYS             JAVA DATA                   328
OLAPSYS         VIEW                        307
SYSTEM          INDEX                       210
SYSMAN          PACKAGE                     196
SH              INDEX PARTITION             196
SYSMAN          PACKAGE BODY                196
APEX_030200     PACKAGE                     189
APEX_030200     PACKAGE BODY                181
MDSYS           TYPE                        157
SYSTEM          TABLE                       156
APEX_030200     LOB                         155
SYS             LOB                         153
SYS             LIBRARY                     144
OLAPSYS         INDEX                       135
SYS             SEQUENCE                    134
ORDDATA         INDEX                       129
XDB             INDEX                       128
OLAPSYS         TABLE                       126
APEX_030200     VIEW                        125
MDSYS           TABLE                       123
MDSYS           INDEX                       114
SYS             TYPE BODY                   113
WMSYS           VIEW                        110
MDSYS           FUNCTION                    107
SYS             PROCEDURE                   101
SYS             FUNCTION                     98
XDB             TYPE                         97

OWNER           OBJECT_TYPE               TOTAL
--------------- -------------------- ----------
SYSMAN          TRIGGER                      97
MDSYS           LOB                          94
MDSYS           VIEW                         86
XDB             TABLE                        81
SYSMAN          LOB                          75
ORDDATA         TABLE                        73
CTXSYS          PACKAGE                      73
CTXSYS          VIEW                         71
MDSYS           PACKAGE                      70
ORDSYS          JAVA RESOURCE                70
MDSYS           PACKAGE BODY                 66
CTXSYS          PACKAGE BODY                 62
WMSYS           INDEX                        61
EXFSYS          VIEW                         56
SH              TABLE PARTITION              56
CTXSYS          INDEX                        56
MDSYS           TRIGGER                      53
XDB             XML SCHEMA                   52
SYS             INDEX PARTITION              52
SYSTEM          INDEX PARTITION              52
SYSMAN          TYPE BODY                    49
SYS             TABLE PARTITION              49
OLAPSYS         TRIGGER                      48
EXFSYS          TABLE                        47
EXFSYS          JAVA CLASS                   47
CTXSYS          TABLE                        47
WMSYS           TABLE                        45
OLAPSYS         PACKAGE                      45
APEX_030200     SYNONYM                      45
MDSYS           TYPE BODY                    43
OLAPSYS         PACKAGE BODY                 43
SYSTEM          TABLE PARTITION              39
EXFSYS          INDEX                        39
OE              TYPE                         37
XDB             PACKAGE                      34
OE              INDEX                        33
XDB             PACKAGE BODY                 33
ORDSYS          FUNCTION                     32
CTXSYS          TYPE                         31
EXFSYS          TYPE                         30
ORDSYS          PACKAGE                      28
SH              INDEX                        27
XDB             TRIGGER                      27
EXFSYS          FUNCTION                     26
DBSNMP          TABLE                        25
ORDDATA         VIEW                         25
SYS             CONSUMER GROUP               25

OWNER           OBJECT_TYPE               TOTAL
--------------- -------------------- ----------
MDSYS           OPERATOR                     25
WMSYS           PACKAGE                      22
WMSYS           PACKAGE BODY                 22
SYSTEM          LOB                          22
ORDSYS          PACKAGE BODY                 20
SYSTEM          SEQUENCE                     20
APEX_030200     PROCEDURE                    19
SYS             PROGRAM                      19
HR              INDEX                        19
EXFSYS          PACKAGE                      18
EXFSYS          PACKAGE BODY                 18
MDSYS           LIBRARY                      18
PM              LOB                          17
XDB             LIBRARY                      17
SH              TABLE                        17
IX              TABLE                        17
SYS             QUEUE                        17
OE              LOB                          15
ORDSYS          TYPE BODY                    15
MDSYS           SEQUENCE                     15
IX              INDEX                        14
WMSYS           TYPE                         14
OE              TABLE                        14
SYSMAN          SEQUENCE                     13
OE              VIEW                         13
DBSNMP          INDEX                        13
SYS             JOB CLASS                    13
SYS             RULE SET                     13
ORDDATA         SEQUENCE                     12
SYSMAN          FUNCTION                     12
OWBSYS_AUDIT    SYNONYM                      12
APEX_030200     FUNCTION                     12
SYSMAN          QUEUE                        12
SYSTEM          VIEW                         12
EXFSYS          PROCEDURE                    10
SYS             TRIGGER                      10
SYS             JOB                          10
SYS             CLUSTER                      10
SYS             RESOURCE PLAN                10
WMSYS           OPERATOR                      9
SYS             WINDOW                        9
HR              TABLE                         9
SYS             UNDEFINED                     9
SYS             SYNONYM                       9
WMSYS           SEQUENCE                      9
SYS             EVALUATION CONTEXT            9
ORDDATA         LOB                           9

OWNER           OBJECT_TYPE               TOTAL
--------------- -------------------- ----------
IX              VIEW                          8
SYS             DIRECTORY                     8
WMSYS           LOB                           8
SI_INFORMTN_SCH SYNONYM                       8
EMA

DBSNMP          TYPE                          8
BI              SYNONYM                       8
SYSTEM          SYNONYM                       8
XDB             FUNCTION                      7
XDB             OPERATOR                      7
OLAPSYS         TYPE                          7
ORDSYS          PROCEDURE                     7
SYS             CONTEXT                       7
DBSNMP          VIEW                          7
SYS             OPERATOR                      7
CTXSYS          TYPE BODY                     6
CTXSYS          OPERATOR                      6
OE              SYNONYM                       6
EXFSYS          TRIGGER                       5
ORDSYS          VIEW                          5
FLOWS_FILES     SYNONYM                       5
XDB             VIEW                          5
OLAPSYS         SEQUENCE                      5
SH              DIMENSION                     5
XDB             SEQUENCE                      5
ORDSYS          TABLE                         5
XDB             TYPE BODY                     5
ORDPLUGINS      PACKAGE                       5
ORDPLUGINS      PACKAGE BODY                  5
EXFSYS          TYPE BODY                     5
DBSNMP          PACKAGE                       4
OE              TRIGGER                       4
SYSTEM          QUEUE                         4
SYSMAN          RULE SET                      4
FLOWS_FILES     INDEX                         4
APEX_030200     SEQUENCE                      4
SYS             SCHEDULER GROUP               4
CTXSYS          INDEXTYPE                     4
ORDSYS          INDEX                         4
SCOTT           TABLE                         4
DBSNMP          PACKAGE BODY                  4
WMSYS           PROCEDURE                     4
XDB             PROCEDURE                     4
IX              QUEUE                         4
OUTLN           INDEX                         4
PM              INDEX                         4

OWNER           OBJECT_TYPE               TOTAL
--------------- -------------------- ----------
WMSYS           FUNCTION                      4
APEX_030200     TYPE                          4
IX              RULE SET                      4
IX              LOB                           3
CTXSYS          SEQUENCE                      3
ORACLE_OCM      PACKAGE                       3
PM              TABLE                         3
SYSMAN          PROCEDURE                     3
ORACLE_OCM      PACKAGE BODY                  3
MDSYS           JAVA RESOURCE                 3
PM              TYPE                          3
SYS             SCHEDULE                      3
HR              SEQUENCE                      3
OE              TYPE BODY                     3
OUTLN           TABLE                         3
SH              LOB                           2
SYSTEM          TRIGGER                       2
HR              PROCEDURE                     2
OLAPSYS         LOB                           2
EXFSYS          JOB                           2
CTXSYS          LOB                           2
MDSYS           PROCEDURE                     2
WMSYS           RULE SET                      2
ORDSYS          LIBRARY                       2
ORACLE_OCM      JOB                           2
WMSYS           TRIGGER                       2
SH              MATERIALIZED VIEW             2
SCOTT           INDEX                         2
SYSMAN          EVALUATION CONTEXT            2
CTXSYS          FUNCTION                      2
DBSNMP          SEQUENCE                      2
CTXSYS          PROCEDURE                     2
EXFSYS          LOB                           2
SYS             DESTINATION                   2
WMSYS           QUEUE                         2
IX              SEQUENCE                      2
IX              EVALUATION CONTEXT            2
MDSYS           INDEXTYPE                     2
SYS             JAVA SOURCE                   2
APPQOSSYS       TABLE                         2
XDB             INDEXTYPE                     2
HR              TRIGGER                       2
FLOWS_FILES     TABLE                         1
OWBSYS          INDEX                         1
SYSTEM          PACKAGE                       1
WMSYS           EVALUATION CONTEXT            1
EXFSYS          INDEXTYPE                     1

OWNER           OBJECT_TYPE               TOTAL
--------------- -------------------- ----------
APPQOSSYS       SYNONYM                       1
SYSTEM          PROCEDURE                     1
EXFSYS          JAVA RESOURCE                 1
SYS             LOB PARTITION                 1
SYSTEM          TYPE                          1
OUTLN           LOB                           1
SYS             EDITION                       1
EXFSYS          SEQUENCE                      1
EXFSYS          OPERATOR                      1
EXFSYS          LIBRARY                       1
DBSNMP          SYNONYM                       1
IX              TYPE                          1
OE              SEQUENCE                      1
SYSMAN          MATERIALIZED VIEW             1
CTXSYS          LIBRARY                       1
WMSYS           TYPE BODY                     1
DBSNMP          PROCEDURE                     1
SYSTEM          PACKAGE BODY                  1
OLAPSYS         FUNCTION                      1
SH              VIEW                          1
OE              FUNCTION                      1
OWBSYS          TABLE                         1
FLOWS_FILES     LOB                           1
OUTLN           PROCEDURE                     1
HR              VIEW                          1
SYS             RULE                          1
FLOWS_FILES     TRIGGER                       1

260 rows selected.

The result returns all the rows. There could be too many unwanted information to mislead your decision. Let’s trim the result by comprising the original query into a subquery and then limit the rows returned by a pseudo column ROWNUM.

SQL> select * from (select owner, object_type, count(*) total from dba_objects group by owner, object_type order by 3 desc) where ROWNUM <= 10;

OWNER           OBJECT_TYPE               TOTAL
--------------- -------------------- ----------
PUBLIC          SYNONYM                   27724
SYS             JAVA CLASS                20444
SYS             VIEW                       3763
ORDSYS          JAVA CLASS                 1898
SYS             TYPE                       1306
SYS             INDEX                       996
SYS             TABLE                       969
SYSMAN          INDEX                       953
APEX_030200     INDEX                       946
SYS             JAVA RESOURCE               760

10 rows selected.

Leave a Reply

Your email address will not be published. Required fields are marked *