About Me

Working as Oracle professional over 7 years now. Specialized in - advanced PL/SQL, advanced SQL queries, Performance Tuning & Designing. Have completed Oracle Advanced PLSQL certification (OCP)

Saturday, February 6, 2010

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

3 comments:

  1. This comment has been removed by the author.

    ReplyDelete
  2. Select * from (
    Select ROWNUM RNM, xx.* from (
    SELECT DISTINCT pha.segment1 last_po_no,
    TRUNC (pha.creation_date) creation_date,
    pla.unit_price rate
    FROM po_headers_all pha,
    po_lines_all pla,
    mtl_system_items_b msib,
    po_line_locations_all pll
    WHERE pha.po_header_id = pla.po_header_id
    AND pla.item_id = msib.inventory_item_id
    AND pll.po_line_id = pla.po_line_id
    AND pll.ship_to_organization_id = msib.organization_id
    AND pla.item_id = 167029--3009 --
    order by 1 desc
    )xx
    )
    where RNM = 2

    ReplyDelete
  3. Try This using ROW_NUMBER Antithetical Function:

    Select * from (Select empid, empname, ROW-NUMBER() over (order by empid ) as row from Employee) where row=n;
    This query return nth rows from a table.

    ReplyDelete

Followers