Re: _cost_equality_semi_join, Peoplesoft, performance issues

From: K Gopalakrishnan <kaygopal_at_yahoo.com>
Date: Wed, 12 Jan 2005 11:10:27 -0800 (PST)
Message-ID: <20050112191028.97020.qmail@web30902.mail.mud.yahoo.com>

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 conditions.

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

Bottomline:

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. http://www.amazon.com/exec/obidos/tg/detail/-/007222729X/

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Jan 12 2005 - 13:10:17 CST