Re: _cost_equality_semi_join, Peoplesoft, performance issues

  • From: K Gopalakrishnan <kaygopal@xxxxxxxxx>
  • To: jclarke@xxxxxxxxxxxxxxx
  • Date: Wed, 12 Jan 2005 11:10:27 -0800 (PST)

John :

Disclaimer: I have absolutely no knowledge on Peoplesoft Upgrade
process. So treat this information with pinch of salt when relating to
Peoplesoft performance.

WHile using EXISTS, IN and NOT IN in the join operation, the sub query
is un nested based on the values of _unnest_subquery parameter and the
results are filtered using FILTER operation. Otherwise it is un nested
and joined using either semi join or anti join based on the IN/NOT IN

If the result set is smaller an inline view and it is progressed based
on that view. The problem comes where there is BIG inlist with
duplicates. There the normal join costing may go inflated and this
parameter treats the semijoin as equality join and controls the cost
(and the plan).


It is a non distructive non corruptive non dangerous parameter and you
can use that. Since it begins with an underscore I would get the
blessings from Oracle Support before using that ;)

Have a nice day !!
Best Regards,
K Gopalakrishnan,
Co-Author: Oracle Wait Interface: Oracle Press 2004.

Other related posts: