Re: self-join runs forever

jimmy.liew_at_ogilvy.com (Jim) 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??

Look at the nested loop, make sure the right table is driving. Use the LEADING, USE_NL or other hint to force the proper order.

Mike Received on Mon May 06 2002 - 18:08:10 CEST