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 -> Re: DBA HELP: Performane Tune SELECT, SUM, & CASE

Re: DBA HELP: Performane Tune SELECT, SUM, & CASE

From: Serge Rielau <srielau_at_ca.ibm.com>
Date: Wed, 20 Apr 2005 19:23:38 -0400
Message-ID: <3co6k1F6n65d2U1@individual.net>


gilgantic wrote:
> 1. Add up Count1 when FIELD_1 has a value and FIELD_2 is NULL, or both
> have a value.

== i.e. FIELD_1 has a value
> 2. Add up Count2 when FIELD_2 has a value and FIELD_1 is NULL.
> 4. TotalCount = Count1 + Count2 -- (Below, basically had to reuse the
> SQL from both Count1 and Count2)

== This can be done "later"
> 3. Add a NoneCount when both FIELD_1 and FIELD_2 are NULL.
>

SELECT count1, count2,

        count1 + count2 as total-count,
        none_count FROM
  (SELECT
	SUM(CASE
		WHEN (FIELD_1 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 NULL)
		THEN 1
		ELSE 0
		END) AS None_Count,

FROM TABLE_1) as t

I'm wondering what you mean by slow.
You should end up with a table scan an an aggregation. Not much choice.

Cheers
Serge

-- 
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Received on Wed Apr 20 2005 - 18:23:38 CDT

Original text of this message

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