Message-ID: <F001.0053F9FF.20030130125553@fatcity.com>
Date: Thu, 30 Jan 2003 12:55:53 -0800
To: Multiple recipients of list ORACLE-L <ORACLE-L@fatcity.com>
Sender: root@fatcity.com
Reply-To: ORACLE-L@fatcity.com
Errors-To: ML-ERRORS@fatcity.com
From: Jared.Still@radisys.com
Subject: RE: SQL question
Organization: Fat City Network Services, San Diego, California
Precedence: bulk
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit

Nah, I'm tired of it now.

It was an interesting diversion.

Jared


"Khedr, Waleed" <Waleed.Khedr@FMR.COM>
Sent by: root@fatcity.com
 01/30/2003 11:34 AM
 Please respond to ORACLE-L

 
        To:     Multiple recipients of list ORACLE-L <ORACLE-L@fatcity.com>
        cc: 
        Subject:        RE: SQL question


What about:

select count(count(*))
from emp
group by ename, job


Have fun :)

Waleed

-----Original Message-----
Sent: Thursday, January 30, 2003 1:55 PM
To: Multiple recipients of list ORACLE-L


Jared

Jared Still wrote:
> Though not a dramatic difference, the CONCAT was faster
> and less resource intensive than the inline view with GROUP BY.
> 
> :)

Ok, let it be like that, but your test does not check some
other things, like common sense, logic, and session memory.
Performance can vary as I mentioned sometimes can be
neglected, however let's consider the tricks you made before
your test:

1. create index emp_idx on emp(ename, job, mydate); -- what for do
you need it? It was not in the original problem definition. It's
not used, however you created it, what's that for?

2. Both queries give different results, that's what I mentioned --
you just proved my words :) One must be very careful with that.
Even DISTINCT can lose sometimes like in your example, but it does
not mean that the logic of the application works correctly in
case of || = CONCAT is used. Think also about an artificial limit
your create -- each and every varchar has to be padded to it's
maximum length (become CHAR) -- that optional and case dependent,
however; all date and numeric columns have to be formatted
otherwise you can face the same case like your your example.

SQL> SELECT COUNT(*) FROM emp;

    COUNT(*)
----------
       64000

SQL> select count(distinct(ename||job||mydate)) FROM emp;

COUNT(DISTINCT(ENAME||JOB||MYDATE))
-----------------------------------
                                 2000

SQL> SELECT COUNT(*)
    2   FROM (
    3    SELECT DISTINCT
    4     ename, job, mydate
    5    FROM emp
    6   );

    COUNT(*)
----------
        7000

-- 
Vladimir Begun
The statements and opinions expressed here are my own and
do not necessarily represent those of Oracle Corporation.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Vladimir Begun
  INET: Vladimir.Begun@oracle.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Khedr, Waleed
  INET: Waleed.Khedr@FMR.COM

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: 
  INET: Jared.Still@radisys.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).