Tag Archives: new features

Select Clause in 12c

Prior to Oracle12c you needed to use an inner query to get the 10 first rows of a table. Example:

Get the 10 first rows ordered by user_id in ascending order:

drop table DEMO1;

create table DEMO1 as select substr(username,1,20) username, user_id, from all_users;

select * from 
(select username, user_id from DEMO1 order by user_id asc)
where rownum <= 10;

USERNAME             USER_ID
-------------------- ----------
XS$NULL              2147483638
SYSKM                2147483619
SYSDG                2147483618
SYSBACKUP            2147483617
DVSYS                   1279990
DBFS_USER                   111
SCOTT                       109
BI                          108
SH                          107
IX                          106

 

Now with 12c, this is extremely simple!!!
Oracle 12c provides enhanced support for top-n analysis.

  • OFFSET provides a way to skip the N first rows in a result set before starting to return any rows
  • The FETCH clause limits the number of rows returned in the result set
  • For the result offset clause, the value of the integer literal must be equal to 0 (default if the clause is not given), or positive. If it is larger than the number of rows in the underlying result set, no rows are returned.
  • For the fetch first clause, the value of the literal must be 1 or higher. The literal can be omitted, in which case it defaults to 1. If the clause is omitted entirely, all rows (or those rows remaining if a result offset clause is also given) will be returned.

Here are several examples:

Select the First 3 rows:
select USER_ID, USERNAME from DEMO1 order by USER_ID FETCH FIRST 3 ROWS ONLY;
USER_ID    USERNAME
---------- ------------
 0         SYS
 7         AUDSYS
 8         SYSTEM
Select 3 rows after the first 4:
select USER_ID, USERNAME from DEMO1 order by USER_ID OFFSET 4 ROWS FETCH NEXT 3 ROWS ONLY;
USER_ID    USERNAME
---------- ------------
21         GSMADMIN_INTERNAL
22         GSMUSER
23         DIP
Select all the rows after the 5th one
select USER_ID, USERNAME from DEMO1 order by USER_ID OFFSET 5 rows ;
 USER_ID   USERNAME
---------- --------------------
 22        GSMUSER
 23        DIP
 36        ORACLE_OCM
 47        DBSNMP
 48        APPQOSSYS
 49        XDB
 50        ANONYMOUS
 60        GSMCATUSER
......
......
Select 31% of the rows:
select USER_ID, USERNAME from DEMO1 order by USER_ID FETCH FIRST 31 PERCENT ROWS ONLY;
USER_ID    USERNAME
---------- --------------------
 0         SYS
 7         AUDSYS
 8         SYSTEM
 13        OUTLN
 21        GSMADMIN_INTERNAL
 22        GSMUSER
 23        DIP
 36        ORACLE_OCM
 47        DBSNMP
 48        APPQOSSYS
 49        XDB
 50        ANONYMOUS
 60        GSMCATUSER
 61        WMSYS