Re: Re: explain plan conundrum

Could you please post the entire sql and plan and statistics of the tables and indexes so that we can comment on it rather than letting us guess on selective bits of the entire problem.

At 01:29 PM 11/4/2003, you wrote:
>everything is analyzed. For all indexes, for all indexed columns.
>
>I used analyze. its the same as dbms_stats, just not as robust. I use it
>when I dont feel like typing out dbms_stats.
>
>Are there optimizer parameters that help the optimizer determine join
>order? Ive never had to use the 'ordered' hint on the CBO before when
>everything is analyzed. The difference was huge. Ran for 2 hours and still
>going, with the hint ran in 45 seconds.
>
>im assuming there are some init.ora parameters that I should check out?
>Does oracle take into account 'distinctness' of the columns being joined?
>I have 1 table with 366,000 rows and another with 5,000 rows. the columns
>being joined have 4 distinct values each. However, the table with 366,000
>rows joins on its primary key to another table and that filters out enough
>rows that that join should go first. The optimizer made a bad decision.
>
>how do i analyze why it made a bad join order decision? hints like this
>are a stop gap fix.

Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Wolfgang Breitling
  INET: breitliw_at_centrexcc.com

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).