• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

DB QUERY optimization

 
Steve Jiang
Ranch Hand
Posts: 127
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 ]
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34974
379
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic