tuning sql removed CASE Statement

[cappmgr]

I have been given an assignment to make a procedure run faster. It's select statement to fill the cursor is ugly with some correlated sub queries. I set autotrace on for the Execution Plan and Statistics. It has 6 Full Table Scans and some are in a loop. This I believe has to be the problem, but I am not yet sure how to rewrite it to remove the FTS. It also has a CASE statement with a passed in parameter l_option. My first move was to remove the l_option and make two procedures.
Original Select

SELECT   DISTINCT atx.create_userid AS arsuser
			  ,atx.transmission_id AS transmissionid
			  ,atx.transmission_status AS transstatus
			  ,tsc.trans_status_code_desc AS transstatusdesc
			  ,NVL(atx.transmission_comment
				   ,' ') AS transcomment
			  ,atx.create_datetime AS transdate
FROM     transmission atx
	 INNER JOIN ref_transmission_status_code tsc
			 ON tsc.transmission_status_code = atx.transmission_status
	 INNER JOIN transmission_sub_program tsp
			 ON atx.transmission_id = tsp.transmission_id
	 INNER JOIN transmission_rate_effectivity tre
			 ON tre.transmission_sub_program_id = tsp.transmission_sub_program_id
	 INNER JOIN rate_effectivity re
			 ON re.effectivity_id = tre.effectivity_id
WHERE    atx.transmission_id > 1
AND  EXISTS (SELECT tr.transmission_rate_id
		   FROM   transmission_rate tr
		   WHERE  tr.transmission_effectivity_id = tre.transmission_effectivity_id
		   AND tr.rate_status_code = 'TX_DOH')
AND  EXISTS (SELECT treff.effectivity_id
				  ,reff.logical_delete_ind
		   FROM   transmission_rate xr1
				  JOIN transmission_rate_effectivity treff
					ON xr1.transmission_effectivity_id = treff.transmission_effectivity_id
				  JOIN rate_effectivity reff
					ON reff.effectivity_id = treff.effectivity_id
		   WHERE  xr1.rate_id IN (NVL((SELECT MAX(xr.superceeds_rate_id)
								FROM   transmission_rate xr
									   JOIN transmission_rate_effectivity xre
										 ON xr.transmission_effectivity_id = xre.transmission_effectivity_id
								WHERE  xre.effectivity_id = re.effectivity_id
								AND xre.effectivity_status_code = 'TX_DOH')
							   ,(SELECT MAX(xr.rate_id)
								 FROM   transmission_rate xr
										JOIN transmission_rate_effectivity xre
										  ON xr.transmission_effectivity_id = xre.transmission_effectivity_id
								 WHERE  xre.effectivity_id = re.effectivity_id
								 AND xre.effectivity_status_code = 'TX_DOH')))
		   AND 1 = (CASE 
					  WHEN l_option = 1
						   AND reff.logical_delete_ind = 'N'
						   AND re.logical_delete_ind = 'Y'
						   AND reff.effective_date <= re.effective_date
													   THEN 1
					  WHEN l_option = 2
						   AND reff.logical_delete_ind = 'Y'
						   AND re.logical_delete_ind = 'N'
						   AND reff.effective_date <= re.effective_date
													   THEN 1
					  WHEN l_option = 3
						   AND reff.effective_date <= re.effective_date
													   THEN 1
					  ELSE 0
					END))
ORDER BY atx.transmission_id DESC;

I changed it to this

SELECT   DISTINCT atx.create_userid AS arsuser
			  ,atx.transmission_id AS transmissionid
			  ,atx.transmission_status AS transstatus
			  ,tsc.trans_status_code_desc AS transstatusdesc
			  ,NVL(atx.transmission_comment
				   ,' ') AS transcomment
			  ,atx.create_datetime AS transdate
FROM     transmission atx
	 INNER JOIN ref_transmission_status_code tsc
			 ON tsc.transmission_status_code = atx.transmission_status
	 INNER JOIN transmission_sub_program tsp
			 ON atx.transmission_id = tsp.transmission_id
	 INNER JOIN transmission_rate_effectivity tre
			 ON tre.transmission_sub_program_id = tsp.transmission_sub_program_id
	 INNER JOIN rate_effectivity re
			 ON re.effectivity_id = tre.effectivity_id
WHERE    atx.transmission_id > 1
AND  EXISTS (SELECT tre1.transmission_effectivity_id
		   FROM   transmission_rate_effectivity tre1
		   WHERE  tre1.transmission_effectivity_id = tre.transmission_effectivity_id
		   AND tre1.effectivity_status_code = 'TX_DOH')
AND  EXISTS (SELECT tre2.effectivity_id
				  ,reff.logical_delete_ind
		   FROM   transmission_rate xr1
				  JOIN transmission_rate_effectivity tre2
					ON xr1.transmission_effectivity_id = tre2.transmission_effectivity_id
				  JOIN rate_effectivity reff
					ON reff.effectivity_id = tre2.effectivity_id
		   WHERE  xr1.rate_id = (NVL((SELECT MAX(xr.superceeds_rate_id)
							   FROM   transmission_rate xr
									  JOIN transmission_rate_effectivity xre
										ON xr.transmission_effectivity_id = xre.transmission_effectivity_id
							   WHERE  xre.effectivity_id = re.effectivity_id
							   AND xr.rate_status_code = 'TX_DOH')
							  ,(SELECT MAX(xr.rate_id)
								FROM   transmission_rate xr
									   JOIN transmission_rate_effectivity xre
										 ON xr.transmission_effectivity_id = xre.transmission_effectivity_id
								WHERE  xre.effectivity_id = re.effectivity_id
								AND xr.rate_status_code = 'TX_DOH')))
	[b]	   AND reff.logical_delete_ind = 'N'
		   AND re.logical_delete_ind = 'Y')[/b]
ORDER BY atx.transmission_id DESC;

And it went from 3:14 to 0:5 and I was very happy though I did not understand why.
What is really confusing me is when I change the logical delete indcators that are in bold it takes 2:12 to run.

	[b]	   AND reff.logical_delete_ind = 'Y'
		   AND re.logical_delete_ind = 'N')[/b]

Why did removing the CASE statement make such a difference?
Why would the same query that takes 0:5 to run take 2:12 to run with different values? It must be the data but I do not understand.
Any help or how to approach this would be greatly appreciated.
If anyone would like to see Execution Plan and Statictics please let me know and I'll post them.
Thank you very much,
Marty

 

[SantaMufasa]

Marty,

Anytime you make a change to code that results in swapping INDEX access with a FULL-TABLE SCAN, it can drastically affect the performance of the query. It is highly likely that your CASE statements implied FULL-TABLE SCANS...Just my guess from the sidelines.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[cappmgr]

Mufasa,
Would you mind if I post the execution plan of both SQL before and after the change? How do I read the execution plan and determine what line of code in the query it is refering to? If this is asking too much just say so.
Also I still do not understand how changing the R-Values that I had in bold make the query go from 0:5 to 2:12, it's ths same SQL with diferent R-Values.
As always thank you,
Marty