Re: FW: Help....With weird join...

I vaguely recall a bug a little while ago that was along these lines. Oracle failed to notice that the predicate had been repeated, and treated it as two predicates with an argument like:

    selectivity due to pred 1 is 10%
    selectivity due to pred 2 is 10%

Cross-product results in selectivity of 0.1 * 0.1 - 0.01 (1%) and therefore decided that a particular join method/direction was good.

I believe the bug has been fixed.

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Next Seminar - UK, April 3rd - 5th
http://www.jlcomp.demon.co.uk/seminar.html

Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases

-----Original Message-----
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Date: 12 March 2002 22:37

|The remark about the 'two passes' is wrong. My guess is that
repeating
|the condition makes it more attractive, compared to the
VINVOCE.ACCT_NO
|= '12345' condition, normally more attractive (constant) but which
|musn't be very good - stupid question, but is this column indexed? I
|would expect it to be selective. Are tables analyzed and CBO turned
on?
|As usual, running EXPLAIN would help. I find this kind of
optimization
|rather questionable. First, the least you can say is that it is not
|really self-explanatory. You'd better check the execution plans and
use
|hints to reproduce the good one.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jonathan Lewis
  INET: jonathan_at_jlcomp.demon.co.uk

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