Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Problem with cache usage -> very bad performance?
"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 Tue Feb 25 2003 - 13:21:06 CST