posted 18 years ago
The existing query have 2 sub query
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 ]