Any suggestions to improve this query performance
Date: 25 May 2004 04:36:26 -0700
Message-ID: <6616e304.0405250336.6dc5947a_at_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
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
---------- --------------------------------------------------
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
Pramod Ramachandran Received on Tue May 25 2004 - 13:36:26 CEST