PL/SQL insert on 230K rows causes TEMP tablespace to grow to over 10GB

From: <paul_swensen_at_splwg.com>
Date: 9 May 2006 16:03:58 -0700
Message-ID: <1147215838.367297.153280@u72g2000cwu.googlegroups.com>

I am trying to run a PL/SQL statement something like the following:

INSERT INTO customer_table(a, b, c, d, e)

  SELECT ( cust_seq.nextval,              /*sequence for this table */
                  details.cust_key,
                  location.loc_key,
                  meter.meter_key,
                  cust_idx.id

  FROM cust_idx, details, meter, location     WHERE cust_idx.id = details.id AND

                 cust_idx.loc_id = location.loc_id AND
                 cust_idx.meter_id = meter.meter_id AND
                 details.most_recent = 'Y' AND
                 meter.most_recent = 'Y' AND
                 location.most_recent = 'Y';

COMMIT; Each table in the statement has roughly 200K rows give or take 10K.

My database is Oracle 9.2.0.4 on Fedora Core 4. When I try and run this in I see the tempfile for my temp tablespace grow to consume all my available disk space. I have tried freeing up disk space to the point where I have more than 10GB but it still takes it all. My entire database is only 3GB so this does not seem right! I did notice that the 3 columns in the WHERE clause at the end (.most_recent = 'Y') were not indexed. I removed them and the PL/SQL ran successfully. However when I created indexes on them in their respective tables and re-ran the PL/SQL I still saw the same problem.

My temp tablespace was created with the following command:

CREATE TEMPORARY TABLESPACE temp TEMPFILE '/home/oracle/oradata/rhodes/temp01.dbf' SIZE 1000m AUTOEXTEND ON EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512k.

I read somewhere on the web that UNIFORM SIZE should be the same as SORT_AREA_SIZE and it is.

Your help would be greatly appreciated. Received on Tue May 09 2006 - 18:03:58 CDT