optimizer_mode = first_rows and ora-1652

I gave it 36 hours, it never showed so here it is again. My apologies if it's a duplicate.

Can someone give me an explanation for this?

I'm trying to tune a sql statement. optimizer_mode is set to choose in the init.ora. The select pulls from 6 tables. As is it does full table scans on all 6 tables. There are appropriate indexes that can be used. Row counts vary from 40 rows to approx. 3000. This is my first 8 database. My 7.3.4 databases always seemed to work as advertised with CBO, but this one isn't doing what I expect so I'm trying various things. (many just to see what they do and I know there is a lot more to look at.)

One thing I'm trying is 'alter session set optimizer_mode = first_rows'. The first time I ran 'select * from table1,table2,.... where ....;' after altering the session the sql statement ran fine and I got my explain plan
(set autotrace on). The next time, though I got 'ORA-01652: unable to
extend temp segment by 256 in tablespace TEMP'. I know my temp tablespace is small, 10m, so I up it to 50m. Same error. Alter the table, pctincrease = 0, same error. Up temp to 100m. Same error. Now I'm baffled. I'm the only user (this is my play database, no one else even knows it exists!), and it ran fine before the alter session. So for giggles I set 'alter session set optimizer_mode = all_rows'. Works fine, no errors. Change it back to first_rows, ORA-1652. If I add first_rows as a hint I get the same error. There is no order by in the statement. Is there something in first_rows that forces a sort? If so, *why*????

The db is 8.1.6.0.0 running on Solaris 2.7.

I've got other things I want to try. Right now I'm leaning toward a rule hint for this statement. But I thought I'd ask the list gurus for insight.

TIA! Linda

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Seley, Linda
  INET: LSeley_at_IQNavigator.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).