Problem with understanding Optimization methods.

From: Denham Eva <EVAD_at_TFMC.co.za>
Date: Tue, 06 Jan 2004 21:29:27 -0800
Message-ID: <F001.005DBE2E.20040106212927@fatcity.com>

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: EVAD_at_TFMC.co.za

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: 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). Received on Tue Jan 06 2004 - 23:29:27 CST