Re: poorly written query?

> I am trying to run this particular query.................
>
> select collector_id assessor, count(collector_id) num_assmnts,
> count(hotlined_yn) num_hotlines, count(pass_move_yn) num_pins,
> count(suspend_yn) num_suspnds, sum(payment_recvd) pay_recvd,
> count(payment_recvd) num_pays, sum(arts_amt) arts, count(arts_amt)
> arts_total from ollectr.del_acct, collectr.acct_contact where
> rep_review_date< sysdate and rep_review_date>= sysdate- 2 group by
> collector_id;
>
> ..............via sql*plus on an Oracle 8.0.5 database from
> a read only table. The sums add up more than just yesterday's date and it
> takes forever to process. Could someone please give me some advice as to
> what I might be doing wrong? Any help would be much appreciated!

My thoughts in no particular order:

Are the fields in your WHERE clause indexed? Are the tables really in two different schemas or is this a misspelling? Your code would be much easier to read if you formatted it? You should be using TRUNC() with your dates as they contain hours, minutes, and seconds

And finally ... depending on the size of these tables and your hardware, they just may take a very long time to do the calculations. Why don't you create a table of metadata with the sums and counts maintained by triggers or by the procedure that controls your inserts, updates, and deletes, or if you can afford some latency ... by one or more snapshots (materialized views).

Daniel A. Morgan Received on Fri Mar 23 2001 - 01:09:02 CST