Any suggestions to improve this query performance

[Pramod Ramachandran]

Hi group,

I have a query as follows.

SELECT * FROM ACS$USG_EVENT, ACS$USG_EVENTDE TAIL WHERE
ACS$USG_EVENTDE TAIL.PARENTCANO NICALID = ACS$USG_EVENT.C ANONICALID;

both the tables are analyzed and default optimizer is CBO. I use 8i
under winnt. The estimate is as follows

ID STEP_DESCRIPTIO N
---------- --------------------------------------------------
0 SELECT STATEMENT COST = 284431
1 MERGE JOIN
2 TABLE ACCESS BY INDEX ROWID ACS$USG_EVENT
3 INDEX FULL SCAN ACS$CAT_USAGEEV ENT_PKEY
4 SORT JOIN
5 TABLE ACCESS FULL ACS$USG_EVENTDE TAIL


It takes more than six minutes to obtain the result. FYI,
ACS$USG_EVENTDE TAIL 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]

Re: Any suggestions to improve this query performance

pramod@rtimes.c om (Pramod Ramachandran) wrote in message news:<6616e304. 0405250336.6dc5 947a@posting.go ogle.com>...

Hi group,
>
I have a query as follows.
>
SELECT * FROM ACS$USG_EVENT, ACS$USG_EVENTDE TAIL WHERE
ACS$USG_EVENTDE TAIL.PARENTCANO NICALID = ACS$USG_EVENT.C ANONICALID;
>
both the tables are analyzed and default optimizer is CBO. I use 8i
under winnt. The estimate is as follows
>
ID STEP_DESCRIPTIO N
---------- --------------------------------------------------
0 SELECT STATEMENT COST = 284431
1 MERGE JOIN
2 TABLE ACCESS BY INDEX ROWID ACS$USG_EVENT
3 INDEX FULL SCAN ACS$CAT_USAGEEV ENT_PKEY
4 SORT JOIN
5 TABLE ACCESS FULL ACS$USG_EVENTDE TAIL
>
>
It takes more than six minutes to obtain the result. FYI,
ACS$USG_EVENTDE TAIL 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_EVENTDE TAIL have a
primary key, or index on the ACS$USG_EVENTDE TAIL.PARENTCANO NICALID
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]

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_ENABL ED
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@rtimes.c om (Pramod Ramachandran) wrote in message news:<6616e304. 0405250336.6dc5 947a@posting.go ogle.com>...

Hi group,
>
I have a query as follows.
>
SELECT * FROM ACS$USG_EVENT, ACS$USG_EVENTDE TAIL WHERE
ACS$USG_EVENTDE TAIL.PARENTCANO NICALID = ACS$USG_EVENT.C ANONICALID;
>
both the tables are analyzed and default optimizer is CBO. I use 8i
under winnt. The estimate is as follows
>
ID STEP_DESCRIPTIO N
---------- --------------------------------------------------
0 SELECT STATEMENT COST = 284431
1 MERGE JOIN
2 TABLE ACCESS BY INDEX ROWID ACS$USG_EVENT
3 INDEX FULL SCAN ACS$CAT_USAGEEV ENT_PKEY
4 SORT JOIN
5 TABLE ACCESS FULL ACS$USG_EVENTDE TAIL
>
>
It takes more than six minutes to obtain the result. FYI,
ACS$USG_EVENTDE TAIL 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