Re: PL/SQL Performance

From: Pendragon <pendragony_at_geocities.com>
Date: 1998/05/18
Message-ID: <01bd8267$654b06a0$96b9d5d0@yesford.btg.com>#1/1

Lynn Ranen <ranen_at_ix.netcom.com> wrote in article <355DF4CF.BF7AAE3B_at_ix.netcom.com>...
> Hi All --
 

> MODIFCATION # 2 USE EXISTS INSTEAD OF SUB-QUERY (I’m not sure if I used
 this
> feature properly -- but I didn’t get the correct results and it took
> forever)
> TIME = 11.45 seconds ROWS returned = 222
>

 You are correct, that is not the way to use exist.
> <snip>
> WHERE B.ffn = A.ffn
> AND A.plan_type IN ('HMO','PPO','HMO','HMO','HMO')
> AND A.deductible IN (0,100,250,500,750,2000)
> AND LOWER(A.benefit_level) IN
> ('basic','standard','street','plusplan')
> AND EXISTS (select count(*)
> from chpa_county F
> where A.county_id = F.id
> and A.ffn = F.ffn
> and lower(F.county) IN ('broward', 'dade'));

TRY:
WHERE B.ffn = A.ffn
AND

	EXISTS (Select 'X' from chpa_county where
		where lower(county) IN ('broward', 'dade') 
		AND id = A.county_id )

 AND A.plan_type IN ('HMO','PPO')
 AND A.deductible IN (0,100,250,500,750,2000)  AND LOWER(A.benefit_level) IN

                ('basic','standard','street','plusplan')
AND 
	EXISTS (select 'X'
	   from chpa_county D
	   where lower(D.county) IN ('broward','dade') 
	   AND D.ffn = A.ffn);

Now, I also removed duplicates from the list of PPO and HMO. Not being sure of the exact relation ship between the county and county_id fields I placed them in separate exists queires. It is possible that those two can be combined into 1. Received on Mon May 18 1998 - 00:00:00 CDT