FIRST and NEXT means the same ....

In the past , I have had 2 posts (  http://mfzahirdba.blogspot.com/2013/08/fetch-x-rows-in-oracle-12c-new-feature.html and http://mfzahirdba.blogspot.com/2013/07/oracle-12c-installed-new-fetch-feature.html)  on Pagination feature in oracle 12c ( Fetch ... ) . 

Semantically , FIRST  and NEXT means the same in this SQL construct . Sounds bit odd , a'int it ?

Here is an example.

SQL> select empno,ename,hiredate from scott.emp  order by hiredate;

     EMPNO ENAME      HIREDATE
---------- ---------- ---------
      7369 SMITH      17-DEC-80
      7499 ALLEN      20-FEB-81
      7521 WARD       22-FEB-81
      7566 JONES      02-APR-81
      7698 BLAKE      01-MAY-81
      7782 CLARK      09-JUN-81
      7844 TURNER     08-SEP-81
      7654 MARTIN     28-SEP-81
      7839 KING       17-NOV-81
      7900 JAMES      03-DEC-81
      7902 FORD       03-DEC-81
      7934 MILLER     23-JAN-82
      7788 SCOTT      19-APR-87
      7876 ADAMS      23-MAY-87

14 rows selected.

SQL> select empno , ename , hiredate from scott.emp order by hiredate fetch first  5 rows only;

     EMPNO ENAME      HIREDATE
---------- ---------- ---------
      7369 SMITH      17-DEC-80
      7499 ALLEN      20-FEB-81
      7521 WARD       22-FEB-81
      7566 JONES      02-APR-81
      7698 BLAKE      01-MAY-81

SQL> select empno , ename , hiredate from scott.emp order by hiredate fetch next  5 rows only;

     EMPNO ENAME      HIREDATE
---------- ---------- ---------
      7369 SMITH      17-DEC-80
      7499 ALLEN      20-FEB-81
      7521 WARD       22-FEB-81
      7566 JONES      02-APR-81
      7698 BLAKE      01-MAY-81


If you want to display the next n records , you will need to use OFFSET clause . 

SQL> select empno , ename , hiredate from scott.emp
  2  order by hiredate
  3  offset 5 rows
  4  fetch first  5 rows only  ;

     EMPNO ENAME      HIREDATE
---------- ---------- ---------
      7782 CLARK      09-JUN-81
      7844 TURNER     08-SEP-81
      7654 MARTIN     28-SEP-81
      7839 KING       17-NOV-81

      7900 JAMES      03-DEC-81

SQL> select empno , ename , hiredate from scott.emp
  2  order by hiredate
  3  offset 5 rows
  4  fetch next  5 rows only  ;

     EMPNO ENAME      HIREDATE
---------- ---------- ---------
      7782 CLARK      09-JUN-81
      7844 TURNER     08-SEP-81
      7654 MARTIN     28-SEP-81
      7839 KING       17-NOV-81

      7900 JAMES      03-DEC-81


Update:

The same semantic issue is with SQL Server as well. 
Examples below . 

The only ( major) difference is that FETCH clause has to be used in conjunction with OFFSET clause , as can be seen from the last example below. 

1> Select table_name  from information_Schema.tables
2> order by table_type ;
3> GO

table_name
----------
spt_fallback_db
spt_fallback_dev
spt_fallback_usg
SpServerDiagnosticsResult
MSreplication_options
spt_monitor
spt_values

(7 rows affected)
1> Select table_name from information_Schema.tables
2> order by table_type
3> offset 2 rows
4> fetch first  3 rows only;
5> GO
table_name
----------
spt_fallback_usg
spt_fallback_dev
spt_fallback_db

(3 rows affected)
1> Select table_name from information_Schema.tables
2> order by table_type
3> offset 2 rows
4> fetch next   3 rows only;
5> GO
table_name
----------
spt_fallback_usg
spt_fallback_dev
spt_fallback_db

(3 rows affected)
1>
2>
3>
4>
5> Select table_name from information_Schema.tables
6> order by table_type
7> fetch first  3 rows only;
8> GO
Msg 153, Level 15, State 2, Server DDCVSQL02, Line 7
Invalid usage of the option first in the FETCH statement.
 

Comments Welcome.

APPROX_COUNT_DISTINCT - New Oracle 12c Function

Oracle 12c has introduced a aggregate function 'APPROX_COUNT_DISTINCT'  that produces approximate count of discount records. 

If you can live with the approximate count , then this will be better choice if the underlying dataset is large. 

In my test database , I have table called 'whlog' that has around 4 million records .
When I used this funcion , Oracle was able to use the index and comeup with the result a lot quickly . 

The difference between this function and the traditional ( count of discount  ) was pretty significant . 

As always , your mileage will vary. 

Here are the examples:
----------------------

SQL> select count(*) from whlog;

  COUNT(*)
----------
   4244970


SQL> Select count(distinct member_id) from  whlog ;

COUNT(DISTINCTMEMBER_ID)
------------------------
                   48090

Elapsed: 00:00:04.62


SQL>  SELECT  APPROX_COUNT_DISTINCT(member_id) from whlog ;

APPROX_COUNT_DISTINCT(MEMBER_ID)
--------------------------------
                           48057

Elapsed: 00:00:00.23

How to retrieve the underlying SQL for a view.

Some time , you run into performance tuning tasks , where non-performant query uses views . Those views in turn uses views one after the other . 
Though , this is not a recommended practice , it is widely (ab)used . You need to reverse engineer the view to get the actual base tables and its filters and so on. 
Most of the time , the query performs better when directly accessed with the base table ( it depends on the query , and other factors) . 

In any case , Oracle 12c has introduced a new API ( dbms_utility.EXPAND_SQL_TEXT ) to get the underlying base table structures for the views .
This is in one of the most underutilized package , in my opinion ( dbms_utility ). 

Let us get to the example . 



SQL> CREATE OR REPLACE VIEW dept_sales_v
  2  AS
  3    SELECT
  4      deptno AS DepartmentNumber ,
  5      dname  AS DepartmentName ,
  6      loc    AS Location
  7    FROM
  8      scott.dept
  9    WHERE
 10      deptno = 30 ;

View created.


Now , Let us use this new API to get the underlying DDL for the view. 
You may notice that the generated DDL is conceptually the same , syntactically Oracle has created sub query and reference that sub query in the outer query.

SQL> SET serveroutput ON
SQL> SET LONG 10000
SQL> DECLARE
  2    expsql CLOB ;
  3    inpsql CLOB ;
  4  BEGIN
  5    inpsql := 'Select * From dept_sales_v' ;
  6    dbms_utility.EXPAND_SQL_TEXT ( input_sql_text => inpsql, output_sql_text =>
  7    expsql) ;
  8    dbms_output.put_line( expsql) ;
  9  END ;
 10  /
SELECT "A1"."DEPARTMENTNUMBER" "DEPARTMENTNUMBER","A1"."DEPARTMENTNAME"
"DEPARTMENTNAME","A1"."LOCATION" "LOCATION" FROM  (SELECT "A2"."DEPTNO"
"DEPARTMENTNUMBER","A2"."DNAME" "DEPARTMENTNAME","A2"."LOC" "LOCATION" FROM
"SCOTT"."DEPT" "A2" WHERE "A2"."DEPTNO"=30) "A1"

PL/SQL procedure successfully completed.


Now , let us create a view based on this view. 


SQL> CREATE OR REPLACE VIEW EmpSales_v
  2  AS
  3    SELECT
  4      v.DEPARTMENTNAME ,
  5      v.location ,
  6      e.EMPNO AS EmployeeCode,
  7      e.ENAME AS EmployeeName
  8    FROM
  9      DEPT_SALES_V v ,
 10      emp e
 11    WHERE
 12      v.DEPARTMENTNUMBER = e.deptno ;

View created.

Here is the SQL generated by Oracle for this view. 

SQL>     set serveroutput on
SQL> set long 10000
SQL> declare
  2  expsql clob ;
  3  inpsql clob ;
  4  begin
  5  inpsql := 'Select * From EmpSales_v'  ;
  6  dbms_utility.EXPAND_SQL_TEXT  ( input_sql_text => inpsql, output_sql_text => expsql) ;
  7  dbms_output.put_line( expsql) ;
  8  end ;
  9  /
SELECT "A1"."DEPARTMENTNAME" "DEPARTMENTNAME","A1"."LOCATION"
"LOCATION","A1"."EMPLOYEECODE" "EMPLOYEECODE","A1"."EMPLOYEENAME" "EMPLOYEENAME"
FROM  (SELECT "A3"."DEPARTMENTNAME" "DEPARTMENTNAME","A3"."LOCATION"
"LOCATION","A2"."EMPNO" "EMPLOYEECODE","A2"."ENAME" "EMPLOYEENAME" FROM  (SELECT
"A4"."DEPTNO" "DEPARTMENTNUMBER","A4"."DNAME" "DEPARTMENTNAME","A4"."LOC"
"LOCATION" FROM "SCOTT"."DEPT" "A4" WHERE "A4"."DEPTNO"=30) "A3",SCOTT."EMP"
"A2" WHERE "A3"."DEPARTMENTNUMBER"="A2"."DEPTNO") "A1"

PL/SQL procedure successfully completed.

Here is the SQL that has been formatted for Clarity.

Comments Welcome.