I was trying to use ROWNUM to retrieve a row but got no luck. What I did was:
1. Find the max ROWNUM by doing
select max(ROWNUM) from callhistory;
2. Use this ROWNUM to retrieve a row
select calldate from callhistory where rownum = 1414980;
no rows selected
Can you tell me why it's not working?
ronwum can be used with '=' sign only when using rownum=1, else you need to use rownum<=value, which will give you all the "value" first lines ...
but the good question to ask is : why do you want to use rownum, it only gives you the order in which the lines are retrieved by your query, what exactly do you want to do ???
My migration of a big CallHistory table from SQL Server to Oracle was failed in the middle(out of memory) -- there are totally 12 million rows need to be migrated while only 10% of them was moved over.
I created a 2nd table in SQL Server which is the same as the original one except that it was grouped by CallDate (which I think that will make the CallDate column to be ordered). I was trying to use ROWNUM to find out what the last CallDate that has been migrated to Oracle, so I can just pick up from there.
Does that make sense to you?
go get a Nth record do this
select * from table1 where rownum=1 and rowid NOT IN
(SELECT rowid FROM table1 WHERE rownum < 12345);
in such a case, I'd try to find a number column (an id for example) in the table, and then migrate the rows block by block using a modulo function.
for example let's say you have an id column which is a number, I'd first migrate all rows which id ends with a 1 (that is to say mod(id,10)=1), then all lines which id ends with 2, 3, and so on ... that way you only manipulate about 10% of the lines each time and you won't saturate your memory ...
note that if it's still too much, you can use a number greater than 10 in your modulo function, that will reduce the number of lines in each "block" ...
I looked at the table's data, and none of the NUMBER columns' data has a nice distribution :-( So I will just 'chop' the big table into a couple of smaller managable pieces, and migrate them one at a time.
Thanks again for your help!
Click Here to Expand Forum to Full Width