| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: SQL question
. never trust Vladimir Begun, check everything what he's saying :)
Trust?
I don't know you well enough to not trust you.
. never use the sql that looks cool but does not work properly . never tune a query that returns wrong result and compare its
performance with that one that works correctly but slowly.
As you will see in another post, both queries return identical results for me on 8.1.7.
Jared
Vladimir Begun <Vladimir.Begun_at_oracle.com>
Sent by: root_at_fatcity.com
01/30/2003 12:16 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
cc:
Subject: Re: SQL question
Jared
Windows 2k 9.2.0.1
534 hsecs
214 hsecs
Query I've used:
SELECT COUNT(
DISTINCT(
RPAD(NVL(ename, ' '), 30 + NVL2(ename, 0, 1))
|| RPAD(NVL( job, ' '), 30 + NVL2( job, 0, 1))
|| NVL(TO_CHAR(mydate, 'DDMMYYYYHH24MISS'), '*NULL*')
)
) AS l
As you can see it's tightly bound to table definition one has to handle nulls for varchars/chars.
L
7000
Check the resources -- I have doubts that this query is a winner :)
So, the moral of this story:
. never trust Vladimir Begun, check everything what he's saying :) . never use the sql that looks cool but does not work properly . never tune a query that returns wrong result and compare its
performance with that one that works correctly but slowly.
Thanks!
Regards,
-- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. Vladimir Begun wrote:Received on Thu Jan 30 2003 - 15:53:58 CST
> 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
>
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun INET: Vladimir.Begun_at_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_at_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_at_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_at_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).
 |
 |