Re: Any suggestions to improve this query performance

In most cases a hash join might be more efficient than a merge join. The question is why is the CBO not using that instead?

In 8i, let's see the values for these parameters:

HASH_JOIN_ENABLED
HASH_AREA_SIZE
SORT_AREA_SIZE

Also test if you could hint a hash join and yield better performance. We only wanna test; it's always better to let the CBO decide without using hints.

Cheers,

Romeo

pramod_at_rtimes.com (Pramod Ramachandran) wrote in message news:<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
> ---------- --------------------------------------------------
> 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 - 22:18:10 CEST