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-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: 20 March 2002 13:58
To: Multiple recipients of list ORACLE-L
Subject: More info on sql query running slow
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).
=========================================================
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.
=========================================================
