If I use NOT EXISTS in the 2nd part it doesn't return any rows.I know with the current bind values the 3rd query doesn't return any rows.so i am including only 2 parts here. SELECT CUST_NUM, ABS_TYP_ID, ABS_PGM_ID, ABS_PGM_SHORT_DSCR, ABS_PGM_LNG_DSCR, ABS_PGM_DUR_MODE_CD, ABS_PGM_WFM3G_CD FROM F0324DB.TAMRABS_PGM where cust_num=112805 and ABS_TYP_ID='COFM' union all SELECT CUST_NUM, ABS_TYP_ID, ABS_PGM_ID, ABS_PGM_SHORT_DSCR, ABS_PGM_LNG_DSCR, ABS_PGM_DUR_MODE_CD, ABS_PGM_WFM3G_CD FROM F0324DB.TAMRABS_PGM WHERE CUST_NUM= -1 AND ABS_TYP_ID='COFM' AND ABS_PGM_ID NOT IN(SELECT ABS_PGM_ID FROM F0324DB.TAMRABS_PGM where cust_num=112805 and ABS_TYP_ID='COFM') / 21 rows selected. if i use NOT EXISTS then it's not returning any rows for the 2nd part. AND NOT exists(SELECT ABS_PGM_ID FROM F0324DB.TAMRABS_PGM where cust_num=112805 and ABS_TYP_ID='COFM') 4 rows selected. thanks On Sat, Jan 19, 2013 at 3:00 AM, Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx > wrote: > > Your original query transforms into something of the form: > > select where > cust_num = K > or exists (subquery for cust_num = K) > or (cust_num = -1 and not exists (subquery for cust_num = K) > > The optimizer cannot handle (predicate OR subquery) efficiently (see > http://jonathanlewis.wordpress.com/2007/02/26/subquery-with-or/ ) so it > has had to do a tablescan to get 892 rows that might match the customer or > the subquery, with the consequence that it has ended up running the > subquery 871 times. Either you have found a latch-related bug which makes > this subquery execution highly contentious, or it is the number of calls - > combined with the number of concurrent users - that makes this so > inefficient that you have a problem. If it's a bug you need a patch; if > it's inherent then you can rewrite the query > > select > where cust_num = K > union all > select where cust_num = -1 and not exists (subquery for cust_num = K) > union all > select where exists(subquery for cust_num = K) > > With suitable indexing the first two parts can be more efficient that your > current query (I've omitted the abs_typ_id for clarity, but an index > starting with abs_typ_id and cust_num in some order would be useful). You > don't have to worry about accidental duplicates from the first two pieces > > For the last part of the union all you need to ensure that you include a > predicate to eliminate the rows already returned through the first two > parts - and your optimum plan would be unnesting and converting to a nested > loop join. I am assuming that this table will always be a very small table > and that you would drive off an index on the cust_num when accessing this > table because very few of your customers will appear in the table. > > > > Regards > > Jonathan Lewis > http://jonathanlewis.wordpress.com/all-postings > > Author: Oracle Core (Apress 2011) > http://www.apress.com/9781430239543 > > ----- Original Message ----- > From: "oracledba" <oracledba71@xxxxxxxxx> > To: <jonathan@xxxxxxxxxxxxxxxxxx> > Cc: "Oracle-L Freelists" <Oracle-L@xxxxxxxxxxxxx> > Sent: Saturday, January 19, 2013 12:34 AM > Subject: Re: CBC latch contention on index root block > > > | Here is the query with the plan and predicate section.CUST_NUM value is > | same for all 4 bind variables. > | > | > --------------------------------------------------------------------------------------------------- > > -- > //www.freelists.org/webpage/oracle-l > > > -- //www.freelists.org/webpage/oracle-l