Re: Hinting the CBO

On Thu, 22 Nov 2001 18:21:44 -0000, Jonathan Lewis, wrote:

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

I just checked the docs for 8.1.7, and, sure enough, it does specify PUSH_PRED and NO_PUSH_PRED instead of PUSH_JOIN_PRED and NO_PUSH_JOIN_PRED. I'm a bit surprised to see Oracle change the hint syntax that quickly.

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

Actually, it's also an 8.1.7 thing and is referenced in the section on "Global Hints". Unfortunately, since my database is 8.1.5, it doesn't currently help. (I have requested to have the database upgraded to 8.1.7, but this is still several months off.) I did try the index hint INDEX(child idx_c) inside the subquery, but the CBO ignored it.

} Thanks for the comment on the book.

You're welcome.

-- 
Robert

Tchaikowsky.  Was he the tortured soul who poured out his immortal
longings into dignified passages of stately music, or was he just an old
poof who wrote tunes?  Tonight on 'Farming Club' we're going to take an
intimate look at Tchaikowsky and an intimate look at his friends.
Incidentally, BBC Publications have prepared a special pamphlet to go
with this programme called 'Hello Pianist', and it contains material
that some people might find offensive but which is really smashing.
        -- MP