Hi All, Oracle 8.1.6 I have 2 tables which have been analyzed. The query takes about 30+ seconds to run. If I run it many times it is faster as data gets loaded into buffer cache. I want to optimize when that is not the case. All the fields in the query have a separate index created EXCEPT for ACCRUED_AND_PAID.hold_payment_flag.
Phy_Contracts has 10,466 rows. Accrued_and_Paid has 820,919 rows. Here is output from explain plan SELECT STATEMENT Cost = 1382 SORT AGGREGATE NESTED LOOPS TABLE ACCESS BY INDEX ROWID PHY_CONTRACTS INDEX RANGE SCAN PCON_CMPY_FK TABLE ACCESS BY INDEX ROWID ACCRUED_AND_PAID INDEX RANGE SCAN ADPD_PCON_FK Any ideas what I can do to speed this query. SELECT COUNT(a.phy_contract_id) FROM accrued_and_paid a, phy_contracts b WHERE a.hold_payment_flag = 'Y' AND b.phy_contract_id = a.phy_contract_id AND b.company_id = 16 AND b.contract_type = 'IC'; List of single column index on PHY_CONTRACTS INDEX_NAME COLUMN_NAME ------------------------------ --------------------- PCON_CONTRACT_TYPE_IDX CONTRACT_TYPE PCON_CMPY_FK COMPANY_ID PCON_PK PHY_CONTRACT_ID List of single column index on ACCRUED_AND_PAID INDEX_NAME COLUMN_NAME ------------------------------ ------------------ ADPD_PCON_FK PHY_CONTRACT_ID Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).