Re: Hinting the CBO

I may be confusing my versions here -

    _push_join_predicate = true/false
    _push_join_union_view = true/false

are undocumented init.ora parameters (and I'm pretty sure they exist in 8.1.5, though maybe they arrived in 8.1.6).

Maybe push_pred(c) and no_push_pred(c) are only version 9 hints. (On the other hand, I've just scanned the 8.1.7 executable and it contains the text PUSH_PRED and NO_PUSH_PRED, and
surprisingly doesn't contain the text for PUSH_JOIN_PRED and NO_PUSH_JOIN_PRED)

How about hints on the inner table, so you get

    /* ordered use_nl(c) index(c.child idx_c) */

(
again this may be a v9 thing, but the syntax

    inline_view.table_in_view
can be used to apply hints from outside a view to tables known to exist inside the view. )

Thanks for the comment on the book.

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

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

Screen saver or Life saver: http://www.ud.com
Use spare CPU to assist in cancer research.

Robert Massey wrote in message ...


>On Thu, 22 Nov 2001 15:37:46 -0000, Jonathan Lewis, wrote:

>

>} Have you tried the PUSH_PRED hint.

>} Its action is to push join predicates into

>} views, so may do what you want when

>} combined with the ORDERED USE_NL(c)

>

>Do you mean PUSH_JOIN_PRED?  I did try adding PUSH_JOIN_PRED(c) with

>(and without) the other hints, but it didn't help.  I couldn't find

>PUSH_PRED in the v8.1.5 docs, but I have just tried it anyway.  Alas, it

>didn't work either.

>

>I may just be expecting too much from this version of the CBO, though it

>looks like the sort of thing that, with proper hinting, _should_ work.

>

>I find your book to be excellent, by the way.

>--