oracle rownum usage for splitting a Table into two equal parts.
878823Jul 28 2011 — edited Jul 29 2011Hi All,
I have a table which has like 1.2 billion records and i would have to split the table in two parts for my Archiving needs.Unfortunately that table does not have any unique key or primary key or data stamp which i can rely for.
I would have to use the rownum concept to divide the table.
I am using the below
SELECT * FROM (SELECT ENAME, SAL FROM EMP ORDER BY SAL DESC) WHERE ROWNUM < 5000000;
But the problem is that the table is taking forever to retrieve as it has to do a order by and then retrieve the data as per the where clause.
The question i have is that instead of using a orderby clause to get the same rownum for the row every time, can i directly rely on the fact that the database is read only and the Rownum would remain same even without oder by clause....
Thanks....