I have tried using ROWNUM but it requires an order (ROW_NUMBER() OVER (ORDER BY col) )by which is not suitable in our case since the number of records is huge.Is there any other way to acheive it?
Say we have a table with 40 million records and I want of generate a series of queries each working on a subset of rows:
What is the best approach to go about it?
You don't say what defines a subset; it even sounds like it would be variable.
There are two easy ways to select subsets. One is via the predicate: select ... where something=something, or where something between x and y. Whatever column(s) you would sort on to generate a row number could be used in a between clause instead.
Another way is to get a random sample: select ... sample 10000000, or sample .25 (25%). See http://info.teradata.com/htmlpubs/DB_TTU_16_00/SQL_Reference/B035-1146-160K/okq1472240804972.html