Eva, Any chance this system is CPU-bound? I have seen similar problems when cpu-intensive operations are performed. Another thought is to check your temporary tablespace settings. When in doubt, <okay list, repeat after me...> "Do a 10046, level 8 trace"
Execution plan 1 does a sort which will use the temp tablespace and be cpu intensive. Plan 2 is probably reading the data in sorted order (without seeing the exact table/index info this is a guess), which does not require a sort. IIRC, an index will be considered when the ORDER BY clause is invoked to remove the need for a separate sort operation. Daniel Fink Denham Eva wrote: > Hello Listers, > > A normal sql query from a data warehouse tool called Sagent. > SELECT COL1, COL2, COL3 > FROM TABLE > ORDER BY 3; > > The table has approximately 2 mil records. > table has 22 indexes. > > The database is set up optimizer CHOOSE. > I run DBMS_Stats.Gather_Schema_Stats('SchemaName') regularly. > OS is Win2k > ORACLE 81741 > > OK, when doing a explain plan on the above sql, I get the following... > SELECT STATEMENT Optimizer Mode=CHOOSE > SORT ORDER BY > TABLE ACCESS FULL TABLENAME -- Very slow and takes > hours! > > When adding the hint /*+RULE*/ for example I get > SELECT STATEMENT Optimizer Mode=Hint:RULE > TABLE ACCESS BY INDEX ROWID TABLENAME > INDEX FULL SCAN TABLE_INDEX -- > Much faster!!! > > Have I given enough info that anyone can explain why the CHOOSE mode insists > on doing a TABLE ACCESS FULL? > Is there anything I can do to improve performance? Please remember that this > query comes from a Data Warehouse tool and hence does not appear to accept > hints. > > Any help will be much appreciated! > Denham > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Denham Eva > INET: [EMAIL PROTECTED] > > Fat City Network Services -- 858-538-5051 http://www.fatcity.com > San Diego, California -- Mailing list and web hosting services > --------------------------------------------------------------------- > 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.net -- Author: Daniel Fink INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- 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).