_push_join_predicate

[maheshwar]

Hi,

Is there any harm if I change the value of hidden parameter "_PUSH_JOIN_PREDICATE" to false in oracle 9i. Because my query is taking almost 45 min to get results if _PUSH_JOIN_PREDICATE=true and less than a min when _PUSH_JOIN_PREDICATE=false. I am changing the value of _PUSH_JOIN_PREDICATE in my session using alter session.

Please help, Maheshwar

[tamilselvan]

Hi,

Is there any harm if I change the value of hidden parameter "_PUSH_JOIN_PREDICATE" to false in oracle 9i. Because my query is taking almost 45 min to get results if _PUSH_JOIN_PREDICATE=true and less than a min when _PUSH_JOIN_PREDICATE=false. I am changing the value of _PUSH_JOIN_PREDICATE in my session using alter session.

Please help, Maheshwar

Instead of changing the underscore parameter, why don't you tune the SQL statement?

Tamil

[slimdave]

You ought to be able to prevent the predicate push at the query level by one of two other methods
In the in-line view add the clause "and rownum > 1"
Use the NO_PUSH_PRED ptimizer hint documented here: http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96533/hintsref.htm#6195
Either of these would be preferable to modifying the hidden parameter. Don't forget to document in the query why you have done this.