-
Hi,
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;
MAX(ROWNUM)
-----------
1414980
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?
Thanks!
-
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 ???
-
Thanks pipo!
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!
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|