1, select id, max(sched_time) from schedule where sched_time < sysdate then get the max_sched_time lower than current time for each fid 2, select * from schedule where sched_time > max_sched_time and sched_time < max_sched_time + ? and id = fid
I try to combine it to one query to avoid running hundreads times of query for hundreds id as
select * from schedule s, (select id, max(sched_time) max_sched_time from scheudle where sched_time < sysdate group by id ) ST where s.id = st.id and s.sched_time > max_sched_time and s.sched_time < max_sched_time + ? order by s.id
but it seems the new query create much more db hit and used up the temp space. Imet teh db problem of java.sql.SQLException: ORA-01652: unable to extend temp segment by 128 in tablespace TEMP.
How could I optimize the DB query to decrease the DB load?
Thanks, [ August 02, 2005: Message edited by: Steve Jiang ]
Steve, As you noticed, combining the queries makes one really slow query. Sometimes this works out better an sometimes it doesn't. The first place to start is to make sure you have the proper indexes. Having one on sched_time is critical. You can also use your database's explain feature to see what the execution plan is for optimization ideas.