Query Running out of Temp Tablespace

For reasons which make me nuts, were are still running a production app on 7.3. We started a migration to 8i, but we've put it on hold while we consider a different platform (it's DEC/Compaq/HPaq Alpha).

Anyway, on 7.3, I had a nasty query ala Crystal Reports. I killed it after 1h20m. I rewrote all the inner joins to use subqueries, and then added 3 or 4 indexes. Now, it runs out of temp tablespace. It was 200M with 120 extents:

SQL> /
ERROR:
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

no rows selected

 ELAPSED: 0 00:02:31.60 CPU: 0:00:00.00 BUFIO: 19 DIRIO: 0 FAULTS: 0 I upped it to 400M with 120 extents, then 400M autoextend:

SQL> /
ERROR:
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP ORA-07869: sfsfs: $WRITE failure

no rows selected

 ELAPSED: 0 00:35:38.06 CPU: 0:00:00.00 BUFIO: 20 DIRIO: 0 FAULTS: 0 It still dies. For kicks, I ran the same query on 8i. The first one with all the inner joins and no indexes (that I killed after 1h20m) took 27 minutes to finish. The rewritten query took 2.7 seconds under 8i.

::SIGH:: Anyway, I guess I need to make this work under 7.3, so can someone offer an explanation, and/or possible solution why the optimized query runs out of temp tablespace?

Thanks,
John Received on Tue Oct 16 2001 - 14:48:49 CDT