Re: Query Taking a Long Time

The SEQUENTIAL SCAN tells me you could use an index on booked_date. And possibly one on loan_type_code to solve the tmp file requirement for the group by clause. }Hi - } } I have a query that is taking an inordinate amount of time. I have }allowed it to run for 8 hours without getting a result. Yes it involves }a join of two tables . One with 5.6 million records , the other table has }2.8 million records. Working with an HP9000 T5500 box with 2 cpus. } }Below is the query and the results of set explain. Any ideas? Thanks. } }set explain on; }set pdqpriority high; }select loan_type_code, count(*) }from disbursements a,disburse_activity b }where booked_date != '1858/11/17' }and a.disb_id = b.disb_id }group by loan_type_code; } }Below is results of set explain. } }QUERY: }------ }select loan_type_code, count(*) }from disbursements a,disburse_activity b }where booked_date != '1858/11/17' }and a.disb_id = b.disb_id }group by loan_type_code } }Estimated Cost: 110 }Estimated # of Rows Returned: 1 }Maximum Threads: 4 }Temporary Files Required For: Group By } }1) pwages01.b: SEQUENTIAL SCAN } } Filters: pwages01.b.booked_date != 1858/11/17 } }2) pwages01.a: INDEX PATH } } (1) Index Keys: disb_id } (1) Index Keys: disb_id } Lower Index Filter: pwages01.a.disb_id = pwages01.b.disb_id } } } } }ciao }p } }****************************************************************************** }Peter Wages }EDS Arlington, Virginia }pmwages@cais.com }Work: (703) 741-7436 Home: (202) 244-3511 }Anywhere is walking distance, if you have the time }****************************************************************************** **----------------------------------------------** || Darrell S. Thacher dthacher@gte.net || || IntegriSoft, Inc. 817-430-8410 (voice) || || 817-430-4495 (fax) || **----------------------------------------------**