Breaking Down A Large Number Of Rows Into Smaller Queries? Parallelism

I want to create a external application which will query one table from a large Oracle database. The query will run daily and I am expecting to handle 30,000+ rows. To break down

Solution 1:

Oralce provide a parallel hint for sutuations such as this where you have a full table scan or similar problem and want to make use of multiple cores to divide the workload. Further details here.

The syntax is very simple, you specify the table (or alias) and the number of cores (I usually leave as default) e.g.:

select/*+ parallel(a, default) */ *
  from table_a a

You can also use this with multiple tables e.g.

select /*+ parallel(a, default) parallel(b,default) */ *
  from table_a a, table_b b
 where a.some_id = b.some_id

Solution 2:

A database connection is not thread-safe, so if you are going to query the database from several threads, you would have to have a separate connection for each of them. You can either create a connection or get them from a pool.

Before you implement your approach, take some time to analyze where is the time spent. Oracle overall is pretty good with utilizing multiple cores. And the database interaction is usually is the most time-consuming part. By splitting the query in three you might actually slow things down.

If indeed your application is spending most of the time performing calculations on that data, your best approach might be loading all data in a single thread and then splitting processing into multiple threads.