Re: Need help - optimizer trouble

Tom Lane wrote:

> "ADBAAMD" <adba.amdocs@bell.ca> writes:
>
>> Tom Lane wrote:
>>
>>> This behavior is a bit of a hack, which we'll likely reconsider in
>>> future releases, but it does have some redeeming social value too.
>
>> Perhaps it would be cleaner to have somehing like Oracle's hints?
>
> Possibly.  Want to enlighten those of us who've never used Oracle about
> how that works?

    You can read (almost) all about it at
http://technet.oracle.com/doc/server.804/a58246/toc.htm, more to the
point http://technet.oracle.com/doc/server.804/a58246/optimiz.htm#2250.

    For example:

  SELECT    /*+ STAR */
    doc_id
    FROM
    written_by    AS wb
    JOIN
    author_keyword    AS kw
      ON
    kw.author_id    =  wb.author_id
   WHERE
    kw.keyword    =  'foo'
;


    There is also a /*+ ORDERED */ hint, but I dislike it as it also requires
query rewriting.  Keep in mind I have an opinion against having to
rewrite queries for performance reasons, thinking this should be done at
the optimizer or preferably at the physical level or the
logical-to-physical mapping.


    Also it does not seems to me very nice to have queries changing behaviour
from version to version and then back again.



--
  _
/ \   Leandro Guimarães Faria Corsetti Dutra         +55 (11) 3040 8913
\ /   Amdocs at Bell Canada                          +1 (514) 786 87 47
  X    Support Center, São Paulo, Brazil          mailto:adbaamd@bell.ca
/ \   http://terravista.pt./Enseada/1989/    mailto:leandrod@amdocs.com