Performance issue - selecting data from a large table

[Ritika Saxena]

Hi,

I have two tables in my Oracle database as: ONHOLD and VALID.

In my application, the entire data from ONHOLD table is selected, then some conditions are checked on all records and the rows/records which meet those conditions are saved in VALID table. The other records which do meet the condition, remain in ONHOLD table.

My problem is that the ONHOLD table has more than 10 lakh records. So the entire process takes a lot of memory. How can I make sure that data from ONHOLD table is selected in small chunks?

I cannot select the data on the basis of ROWCOUNT or ROWNUM as it results in checking some records more than once. Like, if I select first 100 records from ONHOLD table out of which 40 are validated, then these 40 records move to VALID table and the 60 invalid records remain in ONHOLD table. Now if I again select 100 records and check them, the 60 records which had already been checked in first step are checked again.

Can anyone suggest me some way to solve this problem?
Thanks in advance!!

[Srinivasa Raghavan]

Hi
Dont you have a primary key which is numeric in the source table ? If you have it then you can group the rows using that column & validate the rows.

Get first 1000 records process it and get next 1000 & keep continuing till you process all the records.

Like Select * From Source where pkyColumn Between 1 and 10000