I agree with /*+ORDERED */ hint suggestion (in combination with inline
view).
I've seen cases, when inline view itself didn't do the job, but along with
/*+ORDERED */ hint proved to be a huge performance benefit.
So, try:
SELECT /*+ORDERED */ 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;
or, may be changing order of "a" and "b".
Igor Neyman, OCP DBA
[EMAIL PROTECTED]
----- Original Message -----
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Wednesday, March 20, 2002 2:50 PM
> [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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Igor Neyman
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).
