Check the costs of the two queries (autotrace will be sufficient). In this example, rule based uses the index because it exists and will return the right answer.
Cost based works out that the scan and sort is cheaper. The cost of an index full scan is approximately blevel + leaf_blocks (columns from user_indexes). The cost of the tablescan is blocks / 'adjusted db_file_multiblock_read_count' If your dbfmbrc is 8, use 6.59 If it's 16 use 10 If it's 32 use 16.4 The cost of the sort (which seems to be wrong in 8.1.7.4) is likely to be about the same as the cost of the tablescan. So, as an example, pretend your dbfmbrc is 16, then if the index is larger than 1/5th of the size of the table, the scan and sort will work out cheaper than the index full scan. I am a little surprised, though, that you don't have a path that is "index FAST full scan". This suggests that your index is actually bigger than your table. Maybe it's got lots of holes in it. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html ____UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html ----- Original Message ----- To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Wednesday, January 07, 2004 5:29 AM > 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: Jonathan Lewis 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).