Oracle-L: RE: More info on sql query running slow

From: Hallas John <John.Hallas_at_o2.com>
Date: Wed, 20 Mar 2002 06:34:22 -0800
Message-ID: <F001.0042E628.20020320063422@fatcity.com>

The query in itself seems quite well optimised in my opinion, although 30 seconds does seem quite long relative to what is possible. What I do not understand is why the query uses the PCON_CMPY_FK index on the PHY_CONTRACTS
table when that field (company_id) is not the obvious one to be queried. I would have expected to see it using the PK by searching on PHY_CONTRACT_ID.

Does a index on all 3 columns, in the same order as the query help matters?

John

-----Original Message-----
Sent: 20 March 2002 13:58
To: Multiple recipients of list ORACLE-L

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: Rick_Cale_at_teamhealth.com

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: ListGuru_at_fatcity.com (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).

This electronic message contains information from the mmO2 plc Group which may be privileged or confidential. The information is intended to be for the use of the individual(s) or entity named above. If you are not the intended recipient be aware that any disclosure, copying, distribution or use of the contents of this information is prohibited. If you have received this electronic message in error, please notify us by telephone or email (to the numbers or address above) immediately.

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Hallas John
INET: John.Hallas_at_o2.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------