Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> DBA HELP: Performane Tune SELECT, SUM, & CASE

DBA HELP: Performane Tune SELECT, SUM, & CASE

From: gilgantic <gilgantic_at_gmail.com>
Date: 20 Apr 2005 16:06:42 -0700
Message-ID: <1114038402.110702.102330@l41g2000cwc.googlegroups.com>


HELP!!! I am trying to fine tune or rewrite my SELECT statement which has a combination of SUM and CASE statements. The values are accurate, but the query is slow.
BUSINESS RULE


  1. Add up Count1 when FIELD_1 has a value and FIELD_2 is NULL, or both have a value.
  2. Add up Count2 when FIELD_2 has a value and FIELD_1 is NULL.
  3. TotalCount = Count1 + Count2 -- (Below, basically had to reuse the SQL from both Count1 and Count2)
  4. Add a NoneCount when both FIELD_1 and FIELD_2 are NULL.

SQL Code



SELECT
	SUM(CASE
		WHEN ((FIELD_1 IS NOT NULL AND FIELD_2 IS NULL)  OR (FIELD_1 IS NOT
NULL AND FIELD_2 IS NOT NULL))
		THEN 1
		ELSE 0
		END) AS Count1 ,
	SUM(CASE
		WHEN (FIELD_1 IS NULL AND FIELD_2 IS NOT NULL)
		THEN 1
		ELSE 0
		END) AS Count2,
	SUM(CASE
		WHEN (FIELD_1 IS NULL  AND FIELD_2 IS NOT NULL)
		THEN 1
		ELSE (CASE WHEN ((FIELD_1 IS NOT NULL AND FIELD_2 IS NULL) OR FIELD_1
IS NOT NULL AND FIELD_2 IS NOT NULL) THEN 1 ELSE 0 END)
		END) AS Total_Count,
	SUM(CASE
		WHEN ( FIELD_1 IS NULL AND FIELD_2 IS NULL)
		THEN 1
		ELSE 0
		END) AS None_Count, 
FROM    
	TABLE_1
Received on Wed Apr 20 2005 - 18:06:42 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US