self-join runs forever
Date: 5 May 2002 19:14:53 -0700
Message-ID: <e7d269ce.0205051814.5db5fed_at_posting.google.com>
I'm trying to create a table using a self-join on a large table
Platform : Oracle 8.1.6 on WIndows NT 4 on IBM Netfinity
dual processor 700Mh , 4G memory
Table size: individual_answers = 70 million rows
Statement:
from individual_answers b, quest_std_answers qsa
where ina_qua_answer_uid = qsa_answer_uid
and b.ina_inr_uid in (select max(a.ina_inr_uid) from
individual_answers a, quest_std_answers c
Explain plan
When I first run the statement it keep bombing with 'out of process
Primary Key : INA_PK
columns: INA_IND_URN,
INA_QUA_ANSWER_UID,INA_INR_UID
quest_std_answers = 5,000 rows
Primary Key: QSA_PK
columns: QSA_QSD_UID
create table temp as
select qsa_qsd_uid,qsa_answer_name,qsa_answer_uid,count(*) as Total
where a.ina_ind_urn = b.ina_ind_urn
and a.ina_qua_answer_uid =
c.qsa_answer_uid
and c.qsa_qsd_uid = qsa.qsa_qsd_uid)
group by qsa_qsd_uid,qsa_answer_name,qsa_answer_uid
CREATE TABLE STATEMENT Cost = 19148
LOAD AS SELECT
SORT GROUP BY
FILTER
NESTED LOOPS
TABLE ACCESS FULL QUEST_STD_ANSWERS
INDEX FAST FULL SCAN INA_PK
SORT AGGREGATE
MERGE JOIN
INDEX RANGE SCAN INA_PK
SORT JOIN
TABLE ACCESS BY INDEX ROWID QUEST_STD_ANSWERS
INDEX RANGE SCAN QSA_PK
I read somewhere that there may be bugs with hash joins, so I disabled
it with
HASH_JOIN_ENABLED=false in init.ora.
Now the statement has been running for the last 5 days. Is there anything wrong with my statement? What's happening?? Received on Mon May 06 2002 - 04:14:53 CEST