Any suggestions to improve this query performance

[Pramod Ramachandran]

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

[Ed prochak]

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

[Romeo Olympia]

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

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