Cartesian product in the query. 8i seemed smart enough not to let it
blow up I guess. Thanks for your answers.
johns_spam_address_at_yahoo.com (=?ISO-8859-1?Q?=BFfooguy=3F?=) wrote in message news:<a50fcf68.0110161148.6f1db855_at_posting.google.com>...
> 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 Sun Oct 21 2001 - 00:53:11 CDT
