Urgent: Large volume data query
PouyanNov 15 2010 — edited Nov 25 2010Dear Experts,
I have to create tools to compare values of two large tables record by record. One day volume data is 100,000,000 records which its records contains 40 fields.
Here is my query:
SELECT * FROM table_name where event_date between between TO_DATE(:start,'mm/dd/yyyy') AND TO_DATE(:end,'mm/dd/yyyy')
There is no partition and I cannot ask DBA to create one (I don’t have that power) but there is index on event_date.
I came up with two ideas:
1- Run the query and then retrive every 10,000,000 records and compare them and then read another 10M and go on...
2- Change the query to something like:
SELECT * FROM (select rownum r, * from table_name where event_date between TO_DATE(:start,'mm/dd/yyyy') AND TO_DATE(:end,'mm/dd/yyyy'))
where r between :ss and :nn
and create 10 Threads in java and process every 10M in parallel. In first thread runs query with :ss = 1 and :nn=10M , second one runs :ss=11M :nn=20M and so on...
But my concern is performance! IS THERE ANY OTHER SOLUTIONS REGARDING QUERY??