Re: Problem with cache usage -> very bad performance?

The optimizer will (apparently according to the group ) always take a hint IFF (note 2 F's - thats if and only if) it's a valid execution path you are trying to hint at. A corollary of this is that you shouldn't hint unless you know what you want. ( I suppose <grin> this applies to a lot of things ! not just databases !)

But yes, the HASH_ANTIJOIN path requires the join column to be not null. Unfortunately this fact used to be in the 73 documentation but was then omitted from 8,8i and 9i docs which leads to a lot of people wondering why filters pop up.

Enable not null and let us know how you get on.

<plm_at_gmx.li> wrote in message news:1046200867.7442.0_at_damia.uk.clara.net...
> "Telemachus" <telemachus_at_ulysseswillreturn.net> writes:
>
> > What about
> > select * from sometab p
> > where p.month='31-jan-2003'
> > and
> > p.id not in (select q.id from sometab q where q.month='31-dec-2002')
> >
> > (will only work if id not null ) - should be able to use HASH_AJ and
not
> >
> > that *!$%"£$"£$%$ FILTER "$£%"$£%"$£%" operator .
>
> Aha, so I have to declare id not null and then it could use HASH_AJ?
>
> I have tried optimizer hints, but until now I have not been able to
> bring Oracle on other thoughts :). Will try to set a not null
> constraint tomorrow.
>
> In general I am quite frustrated with Oracle's optimizer and its
> hints. Often Oracle uses some path that is incomprehensible, and hints
> won't do anything. In many cases I have been forced to break complex
> queries up into simple queries using cursor loops and PL/SQL. I would
> expect this to be much slower, but in fact often it is faster :(
>
> > Sorry. Language extremely foul. Filter not a nice operator. it's
oracle's
> > last resort - can't do it any better operator. This is why HASH_AJ
> > (antijoin) was introduced
>
> Any idea how I can force Oracle to use it (apart from hints)?
>
>
> Thank,
>
> --
> Peter Mutsaers, Dübendorf, Switzerland.
Received on Wed Feb 26 2003 - 04:44:16 CST