469,581 Members | 1,916 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,581 developers. It's quick & easy.

Any suggestions to improve this query performance

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
Jul 19 '05 #1
2 4143
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
Jul 19 '05 #2
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

Jul 19 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by ALex_1998 | last post: by
2 posts views Thread by Jaidev Paruchuri | last post: by
7 posts views Thread by Bing Wu | last post: by
8 posts views Thread by Michael C | last post: by
2 posts views Thread by Pramod Ramachandran | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.