Any suggestions to improve this query performance

From: Pramod Ramachandran <pramod_at_rtimes.com>
Date: 25 May 2004 04:36:26 -0700
Message-ID: <6616e304.0405250336.6dc5947a@posting.google.com>

Hi group,

I have a query as follows.

SELECT * FROM ACS$USG_EVENT, ACS$USG_EVENTDETAIL WHERE ACS$USG_EVENTDETAIL.PARENTCANONICALID = ACS$USG_EVENT.CANONICALID; both the tables are analyzed and default optimizer is CBO. I use 8i under winnt. The estimate is as follows

ID STEP_DESCRIPTION

---------- --------------------------------------------------
         0 SELECT STATEMENT   COST = 284431
         1   MERGE JOIN
         2     TABLE ACCESS BY INDEX ROWID ACS$USG_EVENT
         3       INDEX FULL SCAN ACS$CAT_USAGEEVENT_PKEY
         4     SORT JOIN
         5       TABLE ACCESS FULL ACS$USG_EVENTDETAIL

It takes more than six minutes to obtain the result. FYI, ACS$USG_EVENTDETAIL has around 900,000 records and ACS$USG_EVENT has 550,000 records.

Any suggestions on which the query can be improved ?

TIA and regards

Pramod Ramachandran Received on Tue May 25 2004 - 06:36:26 CDT