0

My Oracle 11g SQL query looks something like this:

select distinct(p.person_id) 
from   person p, activity a 
where  p.name like '?' 
  and p.active=1 
  and p.create_date<=systimestamp 
  and p.end_date>=systimestamp 
  and p.person_id = a.person_id

Additionally, I am dynamically adding a couple of date time qualifications (on the activity table) to the query above based on certain conditions.

I am using a batching framework written over JDBC but my question is - Is batching really needed in this case or is just an unnecessary overhead? All the examples which I've seen involved an in parameter for the purpose of qualifications.

Also, is there a good resource to read about this stuff? I couldn't find any decent sources.

Thanks

krokodilko
  • 35,300
  • 7
  • 55
  • 79
kollegekid
  • 21
  • 1
  • 5
  • Cant you make it a procedure? Better performance. – Maheswaran Ravisankar Feb 22 '14 at 13:30
  • OracleUser - Not sure about that but is batching useful at all in this context? – kollegekid Feb 22 '14 at 13:34
  • 3
    Batching is usefull for reducing a number of roundtrips to the database when you have many updates (insert+update+delete statements). You can't batch SELECT statements in JDBC. – krokodilko Feb 22 '14 at 13:58
  • kordirko - Good to know, thanks. It seems our batching framework is different from the actual notion of batching.Our framework seems to expand the SQL select statement to include multiple parameters. – kollegekid Feb 22 '14 at 14:14
  • @OracleUser - better performance? [citation needed] – APC Feb 22 '14 at 19:46
  • @APC I meant.. combining multiple select statements into one pl sql transaction by putting the results into user defined types..! i understood this question as ,OP wants to do the select for multiple values over a Column.. so, instead making a big IN clause, may be we can join it with a PL/SQL table or something.. I get the intention of your comment. You can always let me know, if I am wrong. Thanks. – Maheswaran Ravisankar Feb 23 '14 at 01:53

0 Answers0