To find the last N and display the row number.
With the usual way to pick last set of records from any table minimum 3 joins are required. Using analytical functions the same can be reduced to single table hit to retrieve the last N rows and display the row numbers for the same. This is particularly useful when something like this needs to be achieved in a big query that has multiple joins and multiple column fetch.
NON ANALYTICAL WAY
select * from scott.emp minus select * from scott.emp where rownum <
(select count(*) – 10 from scott.emp);
SQL> select * from scott.emp minus select * from scott.emp where rownum <
2 (select count(*) - 10 from scott.emp);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- -------
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
11 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2564595189
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 1036 | 9 (67)| 00:00:01 |
| 1 | MINUS | | | | | |
| 2 | SORT UNIQUE | | 14 | 518 | 4 (25)| 00:00:01 |
| 3 | TABLE ACCESS FULL | EMP | 14 | 518 | 3 (0)| 00:00:01 |
| 4 | SORT UNIQUE | | 14 | 518 | 5 (20)| 00:00:01 |
|* 5 | COUNT STOPKEY | | | | | |
| 6 | TABLE ACCESS FULL| EMP | 14 | 518 | 3 (0)| 00:00:01 |
| 7 | SORT AGGREGATE | | 1 | | | |
| 8 | INDEX FULL SCAN | PK_EMP | 14 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter( (SELECT COUNT(*)-10 FROM "SCOTT"."EMP" "EMP")>ROWNUM)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
13 consistent gets
0 physical reads
0 redo size
1250 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
11 rows processed
SQL>
ANALYTICAL WAY
SELECT qry.empno, qry.ename, qry.job, qry.mgr, qry.hiredate,qry.sal,qry.comm,qry.deptno,qry.row_desc
from
(SELECT A.*, COUNT(1) OVER (ORDER BY ROWNUM ASC) ROW_ASC, COUNT(1) OVER (ORDER BY ROWNUM DESC) ROW_DESC FROM SCOTT.EMP A) QRY where qry.row_Asc between 1 and 11
SQL> SELECT qry.empno, qry.ename, qry.job, qry.mgr, qry.hiredate,qry.sal,qry.com
m,qry.deptno,qry.row_desc
2 from (SELECT A.*, COUNT(1) OVER (ORDER BY ROWNUM ASC) ROW_ASC, COUNT(1) OVE
R (ORDER BY ROWNUM DESC) ROW_DESC FROM SCOTT.EMP A) QRY where qry.row_Asc betwee
n 1 and 11;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO ROW_DESC
---------- ----------
7876 ADAMS CLERK 7788 23-MAY-87 1100
20 4
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0
30 5
7839 KING PRESIDENT 17-NOV-81 5000
10 6
7788 SCOTT ANALYST 7566 19-APR-87 3000
20 7
7782 CLARK MANAGER 7839 09-JUN-81 2450
10 8
7698 BLAKE MANAGER 7839 01-MAY-81 2850
30 9
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400
30 10
7566 JONES MANAGER 7839 02-APR-81 2975
20 11
7521 WARD SALESMAN 7698 22-FEB-81 1250 500
30 12
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300
30 13
7369 SMITH CLERK 7902 17-DEC-80 800
20 14
11 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2175649969
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 1582 | 5 (40)| 00:00:01 |
|* 1 | VIEW | | 14 | 1582 | 5 (40)| 00:00:01 |
| 2 | WINDOW SORT | | 14 | 518 | 5 (40)| 00:00:01 |
| 3 | WINDOW SORT | | 14 | 518 | 5 (40)| 00:00:01 |
| 4 | COUNT | | | | | |
| 5 | TABLE ACCESS FULL| EMP | 14 | 518 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("QRY"."ROW_ASC">=1 AND "QRY"."ROW_ASC"<=11)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
1360 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
11 rows processed
Saturday, February 6, 2010
TO FIND RUNNING TOTAL
To find the running total using the non-analytical way minimum the same table needs to be joined twice. Using Analytical functions the same can be achieved with single table hit. In a single query running total can be computed in no time.
NON ANALYTICAL WAY -
Select emp1.empno, emp1.sal, sum(emp2.sal) from scott.emp emp1, scott.emp emp2
where emp2.empno <= emp1.empno group by emp1.empno, emp1.sal
order by emp1.empno
EMPNO SAL SUM(EMP2.SAL)
---------- ---------- -------------
7369 800 800
7499 1600 2400
7521 1250 3650
7566 2975 6625
7654 1250 7875
7698 2850 10725
7782 2450 13175
7788 3000 16175
7839 5000 21175
7844 1500 22675
7876 1100 23775
7900 950 24725
7902 3000 27725
7934 1300 29025
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1016356015
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 160 | 7 (29)| 0
0:00:01 |
| 1 | SORT GROUP BY | | 10 | 160 | 7 (29)| 0
0:00:01 |
| 2 | MERGE JOIN | | 10 | 160 | 6 (17)| 0
0:00:01 |
| 3 | SORT JOIN | | 14 | 112 | 2 (0)| 0
0:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| EMP | 14 | 112 | 2 (0)| 0
0:00:01 |
| 5 | INDEX FULL SCAN | PK_EMP | 14 | | 1 (0)| 0
0:00:01 |
|* 6 | SORT JOIN | | 14 | 112 | 4 (25)| 0
0:00:01 |
| 7 | TABLE ACCESS FULL | EMP | 14 | 112 | 3 (0)| 0
0:00:01 |
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access(INTERNAL_FUNCTION("EMP2"."EMPNO")<=INTERNAL_FUNCTION("EMP1"."EMPNO
" )) filter(INTERNAL_FUNCTION("EMP2"."EMPNO")<=INTERNAL_FUNCTION("EMP1"."EMPNO
" ))
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
9 consistent gets
0 physical reads
0 redo size
790 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
14 rows processed
SQL>
ANALYTICAL WAY
SQL> set autotrace on
SQL> Select e1.empno, e1.sal, sum(e1.sal) OVER (ORDER BY rownum) from scott.emp
e1 order by e1.empno;
EMPNO SAL SUM(E1.SAL)OVER(ORDERBYROWNUM)
---------- ---------- ------------------------------
7369 800 800
7499 1600 2400
7521 1250 3650
7566 2975 6625
7654 1250 7875
7698 2850 10725
7782 2450 13175
7788 3000 16175
7839 5000 21175
7844 1500 22675
7876 1100 23775
7900 950 24725
7902 3000 27725
7934 1300 29025
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3990564813
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 112 | 5 (40)| 00:00:01 |
| 1 | SORT ORDER BY | | 14 | 112 | 5 (40)| 00:00:01 |
| 2 | WINDOW SORT | | 14 | 112 | 5 (40)| 00:00:01 |
| 3 | COUNT | | | | | |
| 4 | TABLE ACCESS FULL| EMP | 14 | 112 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
807 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
14 rows processed
SQL>
NON ANALYTICAL WAY -
Select emp1.empno, emp1.sal, sum(emp2.sal) from scott.emp emp1, scott.emp emp2
where emp2.empno <= emp1.empno group by emp1.empno, emp1.sal
order by emp1.empno
EMPNO SAL SUM(EMP2.SAL)
---------- ---------- -------------
7369 800 800
7499 1600 2400
7521 1250 3650
7566 2975 6625
7654 1250 7875
7698 2850 10725
7782 2450 13175
7788 3000 16175
7839 5000 21175
7844 1500 22675
7876 1100 23775
7900 950 24725
7902 3000 27725
7934 1300 29025
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1016356015
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 160 | 7 (29)| 0
0:00:01 |
| 1 | SORT GROUP BY | | 10 | 160 | 7 (29)| 0
0:00:01 |
| 2 | MERGE JOIN | | 10 | 160 | 6 (17)| 0
0:00:01 |
| 3 | SORT JOIN | | 14 | 112 | 2 (0)| 0
0:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| EMP | 14 | 112 | 2 (0)| 0
0:00:01 |
| 5 | INDEX FULL SCAN | PK_EMP | 14 | | 1 (0)| 0
0:00:01 |
|* 6 | SORT JOIN | | 14 | 112 | 4 (25)| 0
0:00:01 |
| 7 | TABLE ACCESS FULL | EMP | 14 | 112 | 3 (0)| 0
0:00:01 |
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access(INTERNAL_FUNCTION("EMP2"."EMPNO")<=INTERNAL_FUNCTION("EMP1"."EMPNO
" )) filter(INTERNAL_FUNCTION("EMP2"."EMPNO")<=INTERNAL_FUNCTION("EMP1"."EMPNO
" ))
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
9 consistent gets
0 physical reads
0 redo size
790 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
14 rows processed
SQL>
ANALYTICAL WAY
SQL> set autotrace on
SQL> Select e1.empno, e1.sal, sum(e1.sal) OVER (ORDER BY rownum) from scott.emp
e1 order by e1.empno;
EMPNO SAL SUM(E1.SAL)OVER(ORDERBYROWNUM)
---------- ---------- ------------------------------
7369 800 800
7499 1600 2400
7521 1250 3650
7566 2975 6625
7654 1250 7875
7698 2850 10725
7782 2450 13175
7788 3000 16175
7839 5000 21175
7844 1500 22675
7876 1100 23775
7900 950 24725
7902 3000 27725
7934 1300 29025
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3990564813
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 112 | 5 (40)| 00:00:01 |
| 1 | SORT ORDER BY | | 14 | 112 | 5 (40)| 00:00:01 |
| 2 | WINDOW SORT | | 14 | 112 | 5 (40)| 00:00:01 |
| 3 | COUNT | | | | | |
| 4 | TABLE ACCESS FULL| EMP | 14 | 112 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
807 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
14 rows processed
SQL>
TOP N rows from a table
Non Analytical way
SELECT * FROM scott.emp inside WHERE 5 >= (SELECT COUNT (DISTINCT outside.sal) FROM scott.emp outside
WHERE outside.sal >= inside.sal) ORDER BY inside.sal DESC;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- -------
7839 KING PRESIDENT 17-NOV-81 5000 10
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7566 JONES MANAGER 7839 02-APR-81 2975 20
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
6 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1721702769
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 37 | 25 (4)| 00:00:01 |
| 1 | SORT ORDER BY | | 1 | 37 | 25 (4)| 00:00:01 |
|* 2 | FILTER | | | | | |
| 3 | TABLE ACCESS FULL | EMP | 14 | 518 | 3 (0)| 00:00:01 |
| 4 | SORT GROUP BY | | 1 | 4 | | |
|* 5 | TABLE ACCESS FULL| EMP | 1 | 4 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter( (SELECT COUNT(DISTINCT "OUTSIDE"."SAL") FROM
"SCOTT"."EMP" "OUTSIDE" WHERE "OUTSIDE"."SAL">=:B1)<=5)
5 - filter("OUTSIDE"."SAL">=:B1)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
91 consistent gets
0 physical reads
0 redo size
1028 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
13 sorts (memory)
0 sorts (disk)
6 rows processed
SQL>
Analytical way
SQL> SELECT a.* FROM
2 (select e.*,row_number() OVER (order by rownum) rnm FROM scott.emp e) a
3 WHERE rnm BETWEEN 1 AND 6;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO RNM
---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800
20 1
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300
30 2
7521 WARD SALESMAN 7698 22-FEB-81 1250 500
30 3
7566 JONES MANAGER 7839 02-APR-81 2975
20 4
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400
30 5
7698 BLAKE MANAGER 7839 01-MAY-81 2850
30 6
6 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1602237660
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time|
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 1400 | 4 (25)| 00:00:01|
|* 1 | VIEW | | 14 | 1400 | 4 (25)| 00:00:01|
|* 2 | WINDOW SORT PUSHED RANK| | 14 | 518 | 4 (25)| 00:00:01|
| 3 | COUNT | | | | ||
| 4 | TABLE ACCESS FULL | EMP | 14 | 518 | 3 (0)| 00:00:01|
--------------------------------------------------------------------------------
-
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RNM">=1 AND "RNM"<=6)
2 - filter(ROW_NUMBER() OVER ( ORDER BY ROWNUM)<=6)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
1140 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
6 rows processed
SQL>
SELECT * FROM scott.emp inside WHERE 5 >= (SELECT COUNT (DISTINCT outside.sal) FROM scott.emp outside
WHERE outside.sal >= inside.sal) ORDER BY inside.sal DESC;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- -------
7839 KING PRESIDENT 17-NOV-81 5000 10
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7566 JONES MANAGER 7839 02-APR-81 2975 20
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
6 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1721702769
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 37 | 25 (4)| 00:00:01 |
| 1 | SORT ORDER BY | | 1 | 37 | 25 (4)| 00:00:01 |
|* 2 | FILTER | | | | | |
| 3 | TABLE ACCESS FULL | EMP | 14 | 518 | 3 (0)| 00:00:01 |
| 4 | SORT GROUP BY | | 1 | 4 | | |
|* 5 | TABLE ACCESS FULL| EMP | 1 | 4 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter( (SELECT COUNT(DISTINCT "OUTSIDE"."SAL") FROM
"SCOTT"."EMP" "OUTSIDE" WHERE "OUTSIDE"."SAL">=:B1)<=5)
5 - filter("OUTSIDE"."SAL">=:B1)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
91 consistent gets
0 physical reads
0 redo size
1028 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
13 sorts (memory)
0 sorts (disk)
6 rows processed
SQL>
Analytical way
SQL> SELECT a.* FROM
2 (select e.*,row_number() OVER (order by rownum) rnm FROM scott.emp e) a
3 WHERE rnm BETWEEN 1 AND 6;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO RNM
---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800
20 1
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300
30 2
7521 WARD SALESMAN 7698 22-FEB-81 1250 500
30 3
7566 JONES MANAGER 7839 02-APR-81 2975
20 4
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400
30 5
7698 BLAKE MANAGER 7839 01-MAY-81 2850
30 6
6 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1602237660
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time|
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 1400 | 4 (25)| 00:00:01|
|* 1 | VIEW | | 14 | 1400 | 4 (25)| 00:00:01|
|* 2 | WINDOW SORT PUSHED RANK| | 14 | 518 | 4 (25)| 00:00:01|
| 3 | COUNT | | | | ||
| 4 | TABLE ACCESS FULL | EMP | 14 | 518 | 3 (0)| 00:00:01|
--------------------------------------------------------------------------------
-
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RNM">=1 AND "RNM"<=6)
2 - filter(ROW_NUMBER() OVER ( ORDER BY ROWNUM)<=6)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
1140 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
6 rows processed
SQL>
Retrieve say 5 to 8 rows from table
Non-Analytical way
SELECT a.* FROM
(select e.*,ROWNUM rnm FROM scott.emp e) a
WHERE rnm BETWEEN 3 AND 8;
SQL> SELECT a.* FROM
2 (select e.*,ROWNUM rnm FROM scott.emp e) a
3 WHERE rnm BETWEEN 3 AND 8;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO RNM
---------- ----------
7521 WARD SALESMAN 7698 22-FEB-81 1250 500
30 3
7566 JONES MANAGER 7839 02-APR-81 2975
20 4
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400
30 5
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO RNM
---------- ----------
7698 BLAKE MANAGER 7839 01-MAY-81 2850
30 6
7782 CLARK MANAGER 7839 09-JUN-81 2450
10 7
7788 SCOTT ANALYST 7566 19-APR-87 3000
20 8
6 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2077119879
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 1400 | 3 (0)| 00:00:01 |
|* 1 | VIEW | | 14 | 1400 | 3 (0)| 00:00:01 |
| 2 | COUNT | | | | | |
| 3 | TABLE ACCESS FULL| EMP | 14 | 518 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RNM"<=8 AND "RNM">=3)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
1142 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
6 rows processed
SQL>
OR
SELECT * FROM scott.emp WHERE rowid in (SELECT rowid FROM scott.emp WHERE rownum <= 8
MINUS
SELECT rowid FROM scott.emp WHERE rownum < 3);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
6 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 986843382
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time|
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 49 | 8 (38)| 00:00:01|
|* 1 | HASH JOIN | | 1 | 49 | 8 (38)| 00:00:01|
| 2 | VIEW | VW_NSO_1 | 8 | 96 | 4 (50)| 00:00:01|
| 3 | MINUS | | | | ||
| 4 | SORT UNIQUE | | 8 | 96 | ||
|* 5 | COUNT STOPKEY | | | | ||
| 6 | INDEX FULL SCAN| PK_EMP | 14 | 168 | 1 (0)| 00:00:01|
| 7 | SORT UNIQUE | | 2 | 24 | ||
|* 8 | COUNT STOPKEY | | | | ||
| 9 | INDEX FULL SCAN| PK_EMP | 14 | 168 | 1 (0)| 00:00:01|
| 10 | TABLE ACCESS FULL | EMP | 14 | 518 | 3 (0)| 00:00:01|
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access(ROWID="ROWID")
5 - filter(ROWNUM<=8)
8 - filter(ROWNUM<3)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
10 consistent gets
0 physical reads
0 redo size
1069 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
6 rows processed
Using Analytical functions
SQL> SELECT a.* FROM
(select e.*,row_number() OVER (order by rownum) rnm FROM scott.emp e) a
WHERE rnm BETWEEN 3 AND 8;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO RNM
---------- ----------
7521 WARD SALESMAN 7698 22-FEB-81 1250 500
30 3
7566 JONES MANAGER 7839 02-APR-81 2975
20 4
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400
30 5
7698 BLAKE MANAGER 7839 01-MAY-81 2850
30 6
7782 CLARK MANAGER 7839 09-JUN-81 2450
10 7
7788 SCOTT ANALYST 7566 19-APR-87 3000
20 8
6 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1602237660
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time|
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 1400 | 4 (25)| 00:00:01|
|* 1 | VIEW | | 14 | 1400 | 4 (25)| 00:00:01|
|* 2 | WINDOW SORT PUSHED RANK| | 14 | 518 | 4 (25)| 00:00:01|
| 3 | COUNT | | | | ||
| 4 | TABLE ACCESS FULL | EMP | 14 | 518 | 3 (0)| 00:00:01|
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RNM">=3 AND "RNM"<=8)
2 - filter(ROW_NUMBER() OVER ( ORDER BY ROWNUM)<=8)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
1142 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
6 rows processed
SELECT a.* FROM
(select e.*,ROWNUM rnm FROM scott.emp e) a
WHERE rnm BETWEEN 3 AND 8;
SQL> SELECT a.* FROM
2 (select e.*,ROWNUM rnm FROM scott.emp e) a
3 WHERE rnm BETWEEN 3 AND 8;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO RNM
---------- ----------
7521 WARD SALESMAN 7698 22-FEB-81 1250 500
30 3
7566 JONES MANAGER 7839 02-APR-81 2975
20 4
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400
30 5
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO RNM
---------- ----------
7698 BLAKE MANAGER 7839 01-MAY-81 2850
30 6
7782 CLARK MANAGER 7839 09-JUN-81 2450
10 7
7788 SCOTT ANALYST 7566 19-APR-87 3000
20 8
6 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2077119879
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 1400 | 3 (0)| 00:00:01 |
|* 1 | VIEW | | 14 | 1400 | 3 (0)| 00:00:01 |
| 2 | COUNT | | | | | |
| 3 | TABLE ACCESS FULL| EMP | 14 | 518 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RNM"<=8 AND "RNM">=3)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
1142 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
6 rows processed
SQL>
OR
SELECT * FROM scott.emp WHERE rowid in (SELECT rowid FROM scott.emp WHERE rownum <= 8
MINUS
SELECT rowid FROM scott.emp WHERE rownum < 3);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
6 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 986843382
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time|
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 49 | 8 (38)| 00:00:01|
|* 1 | HASH JOIN | | 1 | 49 | 8 (38)| 00:00:01|
| 2 | VIEW | VW_NSO_1 | 8 | 96 | 4 (50)| 00:00:01|
| 3 | MINUS | | | | ||
| 4 | SORT UNIQUE | | 8 | 96 | ||
|* 5 | COUNT STOPKEY | | | | ||
| 6 | INDEX FULL SCAN| PK_EMP | 14 | 168 | 1 (0)| 00:00:01|
| 7 | SORT UNIQUE | | 2 | 24 | ||
|* 8 | COUNT STOPKEY | | | | ||
| 9 | INDEX FULL SCAN| PK_EMP | 14 | 168 | 1 (0)| 00:00:01|
| 10 | TABLE ACCESS FULL | EMP | 14 | 518 | 3 (0)| 00:00:01|
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access(ROWID="ROWID")
5 - filter(ROWNUM<=8)
8 - filter(ROWNUM<3)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
10 consistent gets
0 physical reads
0 redo size
1069 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
6 rows processed
Using Analytical functions
SQL> SELECT a.* FROM
(select e.*,row_number() OVER (order by rownum) rnm FROM scott.emp e) a
WHERE rnm BETWEEN 3 AND 8;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO RNM
---------- ----------
7521 WARD SALESMAN 7698 22-FEB-81 1250 500
30 3
7566 JONES MANAGER 7839 02-APR-81 2975
20 4
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400
30 5
7698 BLAKE MANAGER 7839 01-MAY-81 2850
30 6
7782 CLARK MANAGER 7839 09-JUN-81 2450
10 7
7788 SCOTT ANALYST 7566 19-APR-87 3000
20 8
6 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1602237660
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time|
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 1400 | 4 (25)| 00:00:01|
|* 1 | VIEW | | 14 | 1400 | 4 (25)| 00:00:01|
|* 2 | WINDOW SORT PUSHED RANK| | 14 | 518 | 4 (25)| 00:00:01|
| 3 | COUNT | | | | ||
| 4 | TABLE ACCESS FULL | EMP | 14 | 518 | 3 (0)| 00:00:01|
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RNM">=3 AND "RNM"<=8)
2 - filter(ROW_NUMBER() OVER ( ORDER BY ROWNUM)<=8)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
1142 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
6 rows processed
Retrieve the Nth row from a table
To retrieve Nth row from a table using the usual way it might require to query the same table thrice (can write it in different way also). But, with Analytical function the same can be made to hit only once. Using this kind of analytical functions is particularly useful when the query needs to be part of a huge query - helps to avoid group by - so the need to query the same table multiple times is reduced.
Non Analytical way
One way to do this - Usual - three table hits.
select sal FROM scott.emp WHERE rowid = (SELECT rowid FROM scott.emp WHERE rownum <= 5 MINUS SELECT rowid FROM scott.emp WHERE rownum < 5);
SAL
----------
1250
Execution Plan
----------------------------------------------------------
Plan hash value: 791824717
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time|
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 16 | 5 (40)| 00:00:01 |
| 1 | TABLE ACCESS BY USER ROWID| EMP | 1 | 16 | 1 (0)| 00:00:01 |
| 2 | MINUS | | | | | |
| 3 | SORT UNIQUE | | 5 | 60 | 2 (50)| 00:00:01 |
|* 4 | COUNT STOPKEY | | | | | |
| 5 | INDEX FULL SCAN | PK_EMP | 14 | 168 | 1 (0)| 00:00:01 |
| 6 | SORT UNIQUE | | 4 | 48 | 2 (50)| 00:00:01 |
|* 7 | COUNT STOPKEY | | | | | |
| 8 | INDEX FULL SCAN | PK_EMP | 14 | 168 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter(ROWNUM<=5)
7 - filter(ROWNUM<5)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
414 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1 rows processed
Using Analytical functions
SQL> SELECT a.* FROM
(select e.*,row_number() OVER (order by rownum) rnm FROM scott.emp e) a
WHERE rnm = 5;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO RNM
---------- ----------
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400
30 5
Execution Plan
----------------------------------------------------------
Plan hash value: 1602237660
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time|
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 1400 | 4 (25)| 00:00:01|
|* 1 | VIEW | | 14 | 1400 | 4 (25)| 00:00:01|
|* 2 | WINDOW SORT PUSHED RANK| | 14 | 518 | 4 (25)| 00:00:01|
| 3 | COUNT | | | | ||
| 4 | TABLE ACCESS FULL | EMP | 14 | 518 | 3 (0)| 00:00:01|
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RNM"=5)
2 - filter(ROW_NUMBER() OVER ( ORDER BY ROWNUM)<=5)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
892 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
Non Analytical way
One way to do this - Usual - three table hits.
select sal FROM scott.emp WHERE rowid = (SELECT rowid FROM scott.emp WHERE rownum <= 5 MINUS SELECT rowid FROM scott.emp WHERE rownum < 5);
SAL
----------
1250
Execution Plan
----------------------------------------------------------
Plan hash value: 791824717
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time|
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 16 | 5 (40)| 00:00:01 |
| 1 | TABLE ACCESS BY USER ROWID| EMP | 1 | 16 | 1 (0)| 00:00:01 |
| 2 | MINUS | | | | | |
| 3 | SORT UNIQUE | | 5 | 60 | 2 (50)| 00:00:01 |
|* 4 | COUNT STOPKEY | | | | | |
| 5 | INDEX FULL SCAN | PK_EMP | 14 | 168 | 1 (0)| 00:00:01 |
| 6 | SORT UNIQUE | | 4 | 48 | 2 (50)| 00:00:01 |
|* 7 | COUNT STOPKEY | | | | | |
| 8 | INDEX FULL SCAN | PK_EMP | 14 | 168 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter(ROWNUM<=5)
7 - filter(ROWNUM<5)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
414 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1 rows processed
Using Analytical functions
SQL> SELECT a.* FROM
(select e.*,row_number() OVER (order by rownum) rnm FROM scott.emp e) a
WHERE rnm = 5;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO RNM
---------- ----------
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400
30 5
Execution Plan
----------------------------------------------------------
Plan hash value: 1602237660
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time|
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 1400 | 4 (25)| 00:00:01|
|* 1 | VIEW | | 14 | 1400 | 4 (25)| 00:00:01|
|* 2 | WINDOW SORT PUSHED RANK| | 14 | 518 | 4 (25)| 00:00:01|
| 3 | COUNT | | | | ||
| 4 | TABLE ACCESS FULL | EMP | 14 | 518 | 3 (0)| 00:00:01|
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RNM"=5)
2 - filter(ROW_NUMBER() OVER ( ORDER BY ROWNUM)<=5)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
892 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
To find Nth maximum value from a table
To find NTH maximum value from a table, we may need to query the same table twice to get the desired output. This will lead to table scans / index scans for the same table twice. The same can be achieved via analytical functions by querying the table once. Have enclosed the execution plan and have highlighted the difference.
Non analytical way
SQL> SELECT DISTINCT SAL FROM SCOTT.EMP A WHERE &N=(SELECT COUNT (DISTINCT B.SAL
)
2 FROM SCOTT.EMP B WHERE A.SAL<=B.SAL);
Enter value for n: 5
old 1: SELECT DISTINCT SAL FROM SCOTT.EMP A WHERE &N=(SELECT COUNT (DISTINCT B
.SAL)
new 1: SELECT DISTINCT SAL FROM SCOTT.EMP A WHERE 5=(SELECT COUNT (DISTINCT B.
SAL)
SAL
----------
2450
Execution Plan
----------------------------------------------------------
Plan hash value: 3556451907
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 25 (4)| 00:00:01 |
| 1 | HASH UNIQUE | | 1 | 4 | 25 (4)| 00:00:01 |
|* 2 | FILTER | | | | | |
| 3 | TABLE ACCESS FULL | EMP | 14 | 56 | 3 (0)| 00:00:01 |
| 4 | SORT GROUP BY | | 1 | 4 | | |
|* 5 | TABLE ACCESS FULL| EMP | 1 | 4 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter( (SELECT COUNT(DISTINCT "B"."SAL") FROM "SCOTT"."EMP" "B" WHERE "B"."SAL">=:B1)=5)
5 - filter("B"."SAL">=:B1)
Statistics
----------------------------------------------------------
187 recursive calls
0 db block gets
127 consistent gets
4 physical reads
0 redo size
414 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
17 sorts (memory)
0 sorts (disk)
1 rows processed
USING Analytical functions -
SELECT * FROM
(SELECT e.*, RANK() OVER ( ORDER BY e.SAL DESC) rnk FROM scott.emp e) qry
WHERE qry.rnk = 6;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO RNK
---------- ----------
7782 CLARK MANAGER 7839 09-JUN-81 2450
10 6
Execution Plan
----------------------------------------------------------
Plan hash value: 3291446077
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 1400 | 4 (25)| 00:00:01|
|* 1 | VIEW | | 14 | 1400 | 4 (25)| 00:00:01|
|* 2 | WINDOW SORT PUSHED RANK| | 14 | 518 | 4 (25)| 00:00:01|
| 3 | TABLE ACCESS FULL | EMP | 14 | 518 | 3 (0)| 00:00:01|
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("QRY"."RNK"=6)
2 - filter(RANK() OVER ( ORDER BY INTERNAL_FUNCTION("E"."SAL") DESC)<=6)
Statistics
----------------------------------------------------------
144 recursive calls
0 db block gets
28 consistent gets
0 physical reads
0 redo size
888 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
SQL> SELECT * FROM
2 (SELECT e.*, RANK() OVER ( ORDER BY e.SAL DESC) rnk FROM scott.emp e) qry
3 WHERE qry.rnk = 6;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO RNK
---------- ----------
7782 CLARK MANAGER 7839 09-JUN-81 2450
10 6
Execution Plan
----------------------------------------------------------
Plan hash value: 3291446077
--------------------------------------------------------------------------------
-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 1400 | 4 (25)| 00:00:01|
|* 1 | VIEW | | 14 | 1400 | 4 (25)| 00:00:01|
|* 2 | WINDOW SORT PUSHED RANK| | 14 | 518 | 4 (25)| 00:00:01|
| 3 | TABLE ACCESS FULL | EMP | 14 | 518 | 3 (0)| 00:00:01|
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("QRY"."RNK"=6)
2 - filter(RANK() OVER ( ORDER BY INTERNAL_FUNCTION("E"."SAL") DESC
)<=6)
Statistics
----------------------------------------------------------
144 recursive calls
0 db block gets
28 consistent gets
0 physical reads
0 redo size
888 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
Non analytical way
SQL> SELECT DISTINCT SAL FROM SCOTT.EMP A WHERE &N=(SELECT COUNT (DISTINCT B.SAL
)
2 FROM SCOTT.EMP B WHERE A.SAL<=B.SAL);
Enter value for n: 5
old 1: SELECT DISTINCT SAL FROM SCOTT.EMP A WHERE &N=(SELECT COUNT (DISTINCT B
.SAL)
new 1: SELECT DISTINCT SAL FROM SCOTT.EMP A WHERE 5=(SELECT COUNT (DISTINCT B.
SAL)
SAL
----------
2450
Execution Plan
----------------------------------------------------------
Plan hash value: 3556451907
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 25 (4)| 00:00:01 |
| 1 | HASH UNIQUE | | 1 | 4 | 25 (4)| 00:00:01 |
|* 2 | FILTER | | | | | |
| 3 | TABLE ACCESS FULL | EMP | 14 | 56 | 3 (0)| 00:00:01 |
| 4 | SORT GROUP BY | | 1 | 4 | | |
|* 5 | TABLE ACCESS FULL| EMP | 1 | 4 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter( (SELECT COUNT(DISTINCT "B"."SAL") FROM "SCOTT"."EMP" "B" WHERE "B"."SAL">=:B1)=5)
5 - filter("B"."SAL">=:B1)
Statistics
----------------------------------------------------------
187 recursive calls
0 db block gets
127 consistent gets
4 physical reads
0 redo size
414 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
17 sorts (memory)
0 sorts (disk)
1 rows processed
USING Analytical functions -
SELECT * FROM
(SELECT e.*, RANK() OVER ( ORDER BY e.SAL DESC) rnk FROM scott.emp e) qry
WHERE qry.rnk = 6;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO RNK
---------- ----------
7782 CLARK MANAGER 7839 09-JUN-81 2450
10 6
Execution Plan
----------------------------------------------------------
Plan hash value: 3291446077
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 1400 | 4 (25)| 00:00:01|
|* 1 | VIEW | | 14 | 1400 | 4 (25)| 00:00:01|
|* 2 | WINDOW SORT PUSHED RANK| | 14 | 518 | 4 (25)| 00:00:01|
| 3 | TABLE ACCESS FULL | EMP | 14 | 518 | 3 (0)| 00:00:01|
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("QRY"."RNK"=6)
2 - filter(RANK() OVER ( ORDER BY INTERNAL_FUNCTION("E"."SAL") DESC)<=6)
Statistics
----------------------------------------------------------
144 recursive calls
0 db block gets
28 consistent gets
0 physical reads
0 redo size
888 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
SQL> SELECT * FROM
2 (SELECT e.*, RANK() OVER ( ORDER BY e.SAL DESC) rnk FROM scott.emp e) qry
3 WHERE qry.rnk = 6;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO RNK
---------- ----------
7782 CLARK MANAGER 7839 09-JUN-81 2450
10 6
Execution Plan
----------------------------------------------------------
Plan hash value: 3291446077
--------------------------------------------------------------------------------
-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 1400 | 4 (25)| 00:00:01|
|* 1 | VIEW | | 14 | 1400 | 4 (25)| 00:00:01|
|* 2 | WINDOW SORT PUSHED RANK| | 14 | 518 | 4 (25)| 00:00:01|
| 3 | TABLE ACCESS FULL | EMP | 14 | 518 | 3 (0)| 00:00:01|
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("QRY"."RNK"=6)
2 - filter(RANK() OVER ( ORDER BY INTERNAL_FUNCTION("E"."SAL") DESC
)<=6)
Statistics
----------------------------------------------------------
144 recursive calls
0 db block gets
28 consistent gets
0 physical reads
0 redo size
888 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
Friday, February 5, 2010
Read Hexa-decimal file via External table
External table was introduced in Oracle 9i which can be used to read file from ORACLE DIRECTORY. Multiple files can be read at the same time. It can also read hexa-decimal files. The following example demonstrates the use of external table to read from hexa-decimal file.
TEST : External table to read from hexa-decimal delimited with different hexa-decimal values.
FILE
Emp Name David Deptid Team1
Emp Name Imran Deptid Team1
Emp Name Ikia Deptid Team1
Emp Name Courts Deptid Team2
Emp Name John Deptid Team2
Emp Name Julie Deptid Team3
Emp Name Josaphine Deptid Team3
create or replace directory ext_dir as 'C:\'
drop table tp_emp_ext;
create table tp_emp_ext
(Emp_name_field varchar2(200),
Emp_name_value varchar2(200),
Dept_id_field varchar2(200),
Dept_id_value varchar2(200))
organization external (
type oracle_loader
default directory ext_dir
access parameters ( records delimited by newline
nodiscardfile
nologfile
fields terminated by 0X'11'
missing field values are null
REJECT ROWS WITH ALL NULL
FIELDS
(Emp_name_field char(200) terminated by 0X'02',
Emp_name_value char(200) terminated by 0X'03',
Dept_id_field char(200) terminated by 0X'02',
Dept_id_value char(200) terminated by 0X'03')
)
location ('samp_text.dat')
)
reject limit unlimited
select * from tp_emp_ext
EMP_NAME_FIELD EMP_NAME_VALUE DEPT_ID_FIELD DEPT_ID_VALUE
Emp Name David Deptid Team1
Emp Name Imran Deptid Team1
Emp Name Ikia Deptid Team1
Emp Name Courts Deptid Team2
Emp Name John Deptid Team2
Emp Name Julie Deptid Team3
Emp Name Josaphine Deptid Team3
TEST : External table to read from hexa-decimal delimited with different hexa-decimal values.
FILE
Emp Name David Deptid Team1
Emp Name Imran Deptid Team1
Emp Name Ikia Deptid Team1
Emp Name Courts Deptid Team2
Emp Name John Deptid Team2
Emp Name Julie Deptid Team3
Emp Name Josaphine Deptid Team3
create or replace directory ext_dir as 'C:\'
drop table tp_emp_ext;
create table tp_emp_ext
(Emp_name_field varchar2(200),
Emp_name_value varchar2(200),
Dept_id_field varchar2(200),
Dept_id_value varchar2(200))
organization external (
type oracle_loader
default directory ext_dir
access parameters ( records delimited by newline
nodiscardfile
nologfile
fields terminated by 0X'11'
missing field values are null
REJECT ROWS WITH ALL NULL
FIELDS
(Emp_name_field char(200) terminated by 0X'02',
Emp_name_value char(200) terminated by 0X'03',
Dept_id_field char(200) terminated by 0X'02',
Dept_id_value char(200) terminated by 0X'03')
)
location ('samp_text.dat')
)
reject limit unlimited
select * from tp_emp_ext
EMP_NAME_FIELD EMP_NAME_VALUE DEPT_ID_FIELD DEPT_ID_VALUE
Emp Name David Deptid Team1
Emp Name Imran Deptid Team1
Emp Name Ikia Deptid Team1
Emp Name Courts Deptid Team2
Emp Name John Deptid Team2
Emp Name Julie Deptid Team3
Emp Name Josaphine Deptid Team3
Subscribe to:
Posts (Atom)