find unique row number

[himmat.m4]

I am getting data by view but there is too large data. so I want to select data in particular slab (i.e. 25 rec) but in my resulting data there is no any unique record in column for fetching.

so please give me solution.

selection records like

rows 1 to 25
rows 25 to 50
|
|

[debasisdas]

you need to use ROWNUM.

[himmat.m4]

rownum is not useful bcoz rownum not coming sequentially it will b come like for first rownum 4 then 2 then 10 like.

here I want first 25 rows then next sequentially 25 rows

[debasisdas]

rownum is not useful bcoz rownum not coming sequentially it will b come like for first rownum 4 then 2 then 10 like.

have you ever user ROWNUM, or that is just an assumption.

[himmat.m4]

Yes I have used.

if there is too large record then rownum comes like above

[urtrivedi]

SELECT col1,col2,col3 FROM 
   (SELECT col1, col2, col3, ROW_NUMBER() OVER (ORDER BY col1) myrow_srno FROM tablename)
   WHERE myrwo_srno BETWEEN 51 and 100;

Is there a date column where you can fetch records by date?
If there is SO much data in the table,
WHY don't you have a unique column in the table?
Obviously, this purpose shows you that you need one.

[himmat.m4]

that all data comes from multiple tables so there is no any unique colomn like we can use to feting data.

[debasisdas]

have you tried as suggested by urtrivedi ?

[himmat.m4]

yes, can u give solution without rownum bcoz for using that needs to retrieve all data first. but here I want reduce the time of fetching data so give solution on that.

[urtrivedi]

I am not sure it is the best way or not, what you can do is create view

create view  myview as SELECT col1, col2, col3, ROW_NUMBER() OVER (ORDER BY col1) myrow_srno FROM tablename
-- or 
create view  myview as SELECT rownum myrow_srno,col1, col2, col3 FROM tablename

then you may select things in your query as I shown you yesterday

SELECT col1,col2,col3 FROM 
   MYVIEW 
   WHERE myrwo_srno BETWEEN 51 AND 100

I think view will follow best execution plan