Tamilselvan,

Thanks, I have the problem resolved. It turns out to be the rule base optimizer. Once I dropped the statistics on both databases, they both used rule base and the execution plans are same, and the order on the result seems to be right. However, I don't understand why cost base uses different execution plans on both databases, while all settings are same including same init.ora settings and indexes? Also, how does the cost base and rule base affect the query result, especially the order of the result? Please advise.


Many thanks!