pr****@rtimes.com (Pramod Ramachandran) wrote in message news:<66**************************@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