Steve,
That query took about 52 seconds to run. Thanks Rick "Stephane Faroult" To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> <sfaroult@oriol cc: ecorp.com> Subject: RE: More info on sql query running slow Sent by: [EMAIL PROTECTED] m 03/20/2002 10:53 AM Please respond to ORACLE-L Rick, Curious to know how this would perform : SELECT COUNT(a.phy_contract_id) FROM accrued_and_paid a, (select phy_contract_id from phy_contracts where company_id = 16 and contract_type = 'IC') b WHERE a.hold_payment_flag = 'Y' AND b.phy_contract_id = a.phy_contract_id; >----- Original Message ----- >From: [EMAIL PROTECTED] >To: Multiple recipients of list ORACLE-L ><[EMAIL PROTECTED]> >Sent: Wed, 20 Mar 2002 05:58:25 > >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: Stephane Faroul 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). -- 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).