If Oracle finds that the query will select more than 2 or 3% of the rows it
will choose a full table scan.
Ruth
----- Original Message -----
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Friday, February 09, 2001 12:01 PM
> 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: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Ruth Gramolini
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
