Bind Variable Substitution In Exec Plan

Giganews Newsgroups
Subject: Bind Variable Substitution In Exec Plan
Posted by:  Daniel (danielroy10ju…@hotmail.com)
Date: 17 Nov 2005

Guys,
    I'm running Oracle 9.2.0.6 on SUN. I've got this huge query
generated by Siebel (I know, I know, all the queries generated by
Siebel are huge!) which goes like this:

select ...
FROM
      SIEBEL.S_CONTACT_BU T1,
      ...
      SIEBEL.S_PARTY T3,
      ...
      SIEBEL.S_LST_OF_VAL T5,
        ...
      SIEBEL.S_CONTACT T32
  WHERE
      ...
      T1.BU_ID = :3 AND T32.ROW_ID = T1.CONTACT_ID AND
      T1.BU_ID = T3.ROW_ID AND
      ...

Now, the problem is that the optimizer "replaces" T1.BU_ID = T3.ROW_ID
with :3 = T3.ROW_ID , and therefore T3 ends up "driving" the query with
an index on T3.ROW_ID, instead of driving with an index on T1.BU_ID. Is
there a way to stop this substitution? I looked at the hidden
parameters, and couldn't find anything. Is that just an optimizer
feature I have to live with, or is there a way to inactivate this?

Thanx

Daniel

Replies