This content has been marked as final. Show 5 replies
Here is my problem, i need to create some files with my own format(let say 5000 recs each) from a huge data table (May contain 5 Million records). And i want this creation to be multi threaded.
so how can i form queries efficiently to fetch records like 1..5000 and 5001..10000 and so on.
I can form some thing like select * from table where rownum<5000 and not exists ( already fetched records) . but it is not the efficient one. . please suggest me the best way of forming the queries or any alternative approach to create files.
See Tom Kyte's article 'On Top-n and Pagination Queries'
He discusses several ways to do what you are asking.
There are different ways of doing this. Some are efficient. Some are very efficient.
It all depends on your requirement.
Do you require exactly 5000 records per thread, or do you just want to divide the work into chunks? 5000 records from a 5 million row table would mean 1000 threads, which would be much slower than one thread! I would say max 2 threads per CPU, assuming you are all alone on the machine.
Do the records have to be sorted in any way?
Does the table have a primary key index or unique index, or indeed any index on a NOT NULL value?
By far the most efficient would be ROWID ranges.
Edited by: Stew Ashton on Jan 4, 2013 12:15 AM