Home » SQL & PL/SQL » SQL & PL/SQL » Implementing row_number function using SQL (merged)
Implementing row_number function using SQL (merged) [message #419170] Thu, 20 August 2009 04:33 Go to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Hi,

Is thier any way we can implement row_number analytical
function by using SQL only i.e.not using row_number
analytical function.

Please find a test case

create table mytesttable
(
  batchid NUMBER,
  invcd   NUMBER,
  subinvcd NUMBER,-- This is unique
  mtype    NUMBER
);

insert into mytesttable values(200,100,10,1);

insert into mytesttable values(200,100,11,1)

insert into mytesttable values(200,100,14,1);

insert into mytesttable values(200,100,20,1);

insert into mytesttable values(200,100,30,2);

insert into mytesttable values(200,100,33,2);

insert into mytesttable values(200,100,34,2);

select * from mytesttable;

   BATCHID      INVCD   SUBINVCD      MTYPE                                     
---------- ---------- ---------- ----------                                     
       200        100         10          1                                     
       200        100         11          1                                     
       200        100         14          1                                     
       200        100         20          1                                     
       200        100         30          2                                     
       200        100         33          2                                     
       200        100         34          2                                     

7 rows selected.

SQL> select batchid,invcd,subinvcd,mtype,
     row_number() over(partition by batchid,invcd,mtype order by subinvcd) urownum 
     from mytesttable;

   BATCHID      INVCD   SUBINVCD      MTYPE    UROWNUM                          
---------- ---------- ---------- ---------- ----------                          
       200        100         10          1          1                          
       200        100         11          1          2                          
       200        100         14          1          3                          
       200        100         20          1          4                          
       200        100         30          2          1                          
       200        100         33          2          2                          
       200        100         34          2          3                          

Execution Plan
----------------------------------------------------------                      
   0      SELECT STATEMENT Optimizer=CHOOSE                                     
   1    0   WINDOW (SORT)                                                       
   2    1     TABLE ACCESS (FULL) OF 'MYTESTTABLE'                              




Statistics
----------------------------------------------------------                      
          0  recursive calls                                                    
          0  db block gets                                                      
          3  consistent gets                                                    
          0  physical reads                                                     
          0  redo size                                                          
        600  bytes sent via SQL*Net to client                                   
        495  bytes received via SQL*Net from client                             
          2  SQL*Net roundtrips to/from client                                  
          1  sorts (memory)                                                     
          0  sorts (disk)                                                       
          7  rows processed                   


Is thier any other way to get urownum column values without using row_number function i.e without using analytical functions.

Regards,
Rajat
Implementing row_number function using SQL [message #419171 is a reply to message #419170] Thu, 20 August 2009 04:34 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Hi,

Is thier any way we can implement row_number analytical
function by using SQL only i.e.not using row_number
analytical function.

Please find a test case

create table mytesttable
(
  batchid NUMBER,
  invcd   NUMBER,
  subinvcd NUMBER,-- This is unique
  mtype    NUMBER
);

insert into mytesttable values(200,100,10,1);

insert into mytesttable values(200,100,11,1)

insert into mytesttable values(200,100,14,1);

insert into mytesttable values(200,100,20,1);

insert into mytesttable values(200,100,30,2);

insert into mytesttable values(200,100,33,2);

insert into mytesttable values(200,100,34,2);

select * from mytesttable;

   BATCHID      INVCD   SUBINVCD      MTYPE                                     
---------- ---------- ---------- ----------                                     
       200        100         10          1                                     
       200        100         11          1                                     
       200        100         14          1                                     
       200        100         20          1                                     
       200        100         30          2                                     
       200        100         33          2                                     
       200        100         34          2                                     

7 rows selected.

SQL> select batchid,invcd,subinvcd,mtype,
     row_number() over(partition by batchid,invcd,mtype order by subinvcd) urownum 
     from mytesttable;

   BATCHID      INVCD   SUBINVCD      MTYPE    UROWNUM                          
---------- ---------- ---------- ---------- ----------                          
       200        100         10          1          1                          
       200        100         11          1          2                          
       200        100         14          1          3                          
       200        100         20          1          4                          
       200        100         30          2          1                          
       200        100         33          2          2                          
       200        100         34          2          3                          

Execution Plan
----------------------------------------------------------                      
   0      SELECT STATEMENT Optimizer=CHOOSE                                     
   1    0   WINDOW (SORT)                                                       
   2    1     TABLE ACCESS (FULL) OF 'MYTESTTABLE'                              




Statistics
----------------------------------------------------------                      
          0  recursive calls                                                    
          0  db block gets                                                      
          3  consistent gets                                                    
          0  physical reads                                                     
          0  redo size                                                          
        600  bytes sent via SQL*Net to client                                   
        495  bytes received via SQL*Net from client                             
          2  SQL*Net roundtrips to/from client                                  
          1  sorts (memory)                                                     
          0  sorts (disk)                                                       
          7  rows processed                   


Is thier any other way to get urownum column values without using row_number function i.e without using analytical functions.

Regards,
Rajat
Re: Implementing row_number function using SQL [message #419175 is a reply to message #419171] Thu, 20 August 2009 05:04 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Why?
Re: Implementing row_number function using SQL [message #419176 is a reply to message #419171] Thu, 20 August 2009 05:04 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Why?

If you could write an SQL implementation of an analytic function that ran quicker than the analytic function did, then that would be how oracle implemented it in the first place.
Re: Implementing row_number function using SQL [message #419177 is a reply to message #419176] Thu, 20 August 2009 05:12 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I suppose the relative insanity of a request is no reason not to practice my SQL.

Here we go - it's not pretty, but it does the job:
with src as (select batchid,invcd,subinvcd,mtype,rownum rnum
             from (select batchid,invcd,subinvcd,mtype
                   from   mytesttable
                   order by batchid,invcd,subinvcd))
    ,grp as (select batchid,invcd,mtype,min(rnum) min_rnum,max(rnum) max_rnum                   
             from   src
             group by batchid,invcd,mtype)
select src.batchid
      ,src.invcd
      ,src.subinvcd
      ,src.mtype 
      ,(src.rnum - grp.min_rnum + 1) row_number
from   src,grp
where  src.batchid = grp.batchid
and    src.invcd   = grp.invcd
and    src.mtype   = grp.mtype
order by batchid,invcd,subinvcd,mtype;
Re: Implementing row_number function using SQL [message #419184 is a reply to message #419177] Thu, 20 August 2009 05:26 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Hi JRowbottom,

Thanks for the help.

Actually issue with analytical function is the cost and performance.In actual table thier is around 8 Lakh data.I cannot use an index because subinvcd is a unique key.So on analyzing the table thier is no use of index becuase the no of distinct keys in index will be the same as number of rows in table.So full table scan is choosen by oracle cost based optimizer.

Now if we uses order by that will cause WINDOW SORT / FULL TABLE SCAN.This will slow the query execution becuase this data will be sorted out in TEMP tablespace.

That's why i was asking for the different approach.In the approach you posted the with clause will also use Temp tablespace.

Regards,
Rajat Ratewal
Re: Implementing row_number function using SQL [message #419186 is a reply to message #419184] Thu, 20 August 2009 05:31 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
If you can come up with an algorithm to find the sequence number of a row in an ordered set of rows without fetching the complete set, and without using an index, you might be in for the big bucks...
Re: Implementing row_number function using SQL [message #419189 is a reply to message #419184] Thu, 20 August 2009 05:53 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:
.So on analyzing the table thier is no use of index becuase the no of distinct keys in index will be the same as number of rows in table.So full table scan is choosen by oracle cost based optimizer.


I strongly suspect that the reason that the optimiser is chosing a full table scan is because you are selecting all of the rows in the table. An index based access path would only be advantageous when you were selecting a relatively small subset of the rows in the table.

Re: Implementing row_number function using SQL [message #419190 is a reply to message #419189] Thu, 20 August 2009 05:54 Go to previous message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Yes JRowbottom your understading is correct.

Regards,
Rajat
Previous Topic: Error while executing procedure
Next Topic: view to find data growth in table
Goto Forum:
  


Current Time: Fri Dec 09 11:37:04 CST 2016

Total time taken to generate the page: 0.13699 seconds