Re: Horrendous Execution Plan from CBO

7.3.4? You're brave...

Try to analyze with the following syntax: ANALYZE TABLE <tname> COMPUTE STATISTICS FOR ALL INDEXED COLUMNS;

There was a funny with CBO that it ignored index stats completely even in some very simple joins. With 7.3.4. Usually this fixed the problem. Can't remember which patch level fixed the problem, try this syntax and see if it resolves this issue.

Hint to try:
FIRST_ROWS (no need to specify table aliases)

Since both tables are of nearly the same number of rows hash joins would be a bad idea, particularly at 7.3.4. Stick to either merge (if you want the lot selected) or nested loops (if you want subset of all joined rows). Make sure those join columns are of the SAME data type, it may be suffering from implicit conversion.

HTH
Cheers
Nuno Souto
nsouto_at_optushome.com.au

• Original Message -----
  > Do any of you SQL tuning gurus have any suggestions? I've listed all
  the
  > gory details below.
  >
  

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Nuno Souto
  INET: nsouto_at_optushome.com.au

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