Re: self-join runs forever

This may work better (if you leave hash joins enabled)

create table temp as
select qsa_qsd_uid,qsa_answer_name,qsa_answer_uid,count(*) as Total from individual_answers b, quest_std_answers qsa, (
select a.ina_ind_urn , c.qsa_qsd_uid, max(a.ina_inr_uid) miiu from individual_answers a, quest_std_answers c where a.ina_qua_answer_uid = c.qsa_answer_uid group by a.ina_ind_urn , c.qsa_qsd_uid
) sq
where
sq.ina_ind_urn = b.ina_ind_urn and
sq.qsa_qsd_uid = qsa.qsa_qsd_uid
and b.ina_inr_uid=sq.miiu

"Jim" <jimmy.liew_at_ogilvy.com> wrote in message news: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
> 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
>
> Statement:
> create table temp as
> select qsa_qsd_uid,qsa_answer_name,qsa_answer_uid,count(*) as Total
> 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
> 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
>
> Explain plan
> 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
>
> When I first run the statement it keep bombing with 'out of process
> memory'.
> 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 Fri May 10 2002 - 18:50:35 CEST