Re: Any suggestions to improve this query performance

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

1. this really should be asked in comp.databases.oracle.misc since this group, comp.databases.oracle is defunct.
2. since you are doing "SELECT *" I suspect there isn't much more to do. if the ACS$USG_EVENT table has matches for all its rows, you return over half a million rows. If you are waiting to display the results, you might add a FIRST_ROWS hint.

Does ACS$USG_EVENT have a PRIMARY KEY? Does ACS$USG_EVENTDETAIL have a primary key, or index on the ACS$USG_EVENTDETAIL.PARENTCANONICALID attribute? I might have thought the full scan and index scan might be reversed, but I really don't see much that can help this. The SELECT * forces a read of the entire row from both tables eventually.

Unless there is something special about your data, you are stuck. If there is something special, you might be able to partition the query into parts and run the parts in parallel.

(Remember the DB adage: know thy data.)
HTH
  ed Received on Tue May 25 2004 - 12:26:15 CDT