[EMAIL PROTECTED] wrote: > > 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 > >
Yek. You should check the plan, there must be a full scan somewhere. I was hoping that the in-line view would be run first (assuming indices are efficient) and the the result used to pull the results from the other table. Two more things to try : 1) /*+ ORDERED */ and the in-line view first in the FROM clause 2) Not using an in-line view but a a.phy_contract_id in (select ..). In theory it shouldn't make any difference but the CBO has its mysterious ways sometimes. -- Regards, Stephane Faroult Oriole Ltd -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult 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).
