Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Urgent: Large volume data query

PouyanNov 15 2010 — edited Nov 25 2010
Dear 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??
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Dec 23 2010
Added on Nov 15 2010
13 comments
3,599 views